Database Schema Metadata Management


For details of your database schema metadata, the ANSI-standard set of  information_schema views provides helpful schema details as a read-only, “point in time” reference. However, what if you would like to (a) have a historical view of this data, and/or (b) would like to supplement this metadata with your own data extensions? If this problem strikes a chord, please read on…


Anyone who has previously struggled with database/application change management can attest to the importance of being able to track database schema changes. But the need to extend schema metadata might be less clear. A while ago I was looking for a way to auto-generate SQL MERGE statements, specifically for Data Warehouse population of “Kimball style” dimension tables and attributes. To enable this, I needed to first know the dimension table’s natural (business) key, and whether each column should be treated as a ‘Type-1’, or ‘Type-2’ (historical) attribute.


This requirement eventually evolved into a mini-application, called ‘snap_mart’, consisting of a back-end SQL Server database, and a front-end user interface (download here). This solution creates a historical collection of information_schema views, placed in a standard dimensional model, providing metadata extensions typically needed for a data warehouse.


This Metadata solution consists of:

  • a database DDL script (bimlsnap_v2)

  • a metadata manager front-end application

  • a ‘snap_mart Refresh’ SSIS project (used to extract database metadata from either SQL Server or the cloud database platform: snowflake.)

To find out more, please ping us at info@bitracks.com


Automating MERGE statements

As mentioned earlier, one driving factor for this Database Metadata solution was the need to generate numerous MERGE statements to support population of dimension tables in a Data Warehouse. For this magic to happen, we need to add a code generator to leverage our extended database metadata. Although not currently available as a stand alone script, we do provide this capability in BimlSnap, our game changing Biml centric ELT/ETL product. Using BimlSnap, MERGE statements are rendered as a parameter driven SQL Expression, which can then be generated as an SSIS project using BimlExpress, the free Visual Studio add-in from Varigence. For more information on this accelerated ELT approach, please visit www.bimpsnap.com

16 views

Data Integration Tools  |  Data Warehousing  |  Microsoft Power BI Consultants  |  Business Intelligence Tools  |  Cloud-Based Technology Company

Cincinnati, OH

2020 BI Tracks, LLC

  • Facebook - White Circle
  • Twitter - White Circle
  • LinkedIn - White Circle