Deriving a Metadata Model From SQL Server With BimlStudio

Why Generate the Metadata Model From SQL

The Biml Metadata feature in BimlStudio supports custom MetadataModel and MetadataInstance definitions that can be browsed through a dynamic object model. Maintaining the actual values inside BimlStudio is rarely the right answer; data stewards usually want to edit metadata in a database or spreadsheet. The script below reads an arbitrary 'meta' schema from SQL Server with ImportDB, projects the table list into a MetadataModel, and projects the row contents into a matching MetadataInstance.

Prerequisite

A 'meta' schema with primary keys and foreign keys defined. Tables become entities, columns become properties, and foreign keys become many to one relationships. The pattern adapts to any model as long as relational integrity is declared.

The Script

<#@ template language="VB" optionexplicit="False" #>
<#
  connMetadata = "Data Source=.;Initial Catalog=DerivedMetadataDb;Provider=SQLNCLI11;Integrated Security=SSPI;"
  Dim MetaConnection = SchemaManager.CreateConnectionNode("Meta", connMetadata)
  Dim MetaSchema = MetaConnection.ImportDB("meta", nothing, nothing)
#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Metadata>
    <MetadataModel Name="MetaData">
      <Entities>
        <# for each tbl in MetaSchema.TableNodes #>
        <Entity Name="<#= tbl.Name #>">
          <Properties>
            <# for each col in tbl.Columns.Where(Function(c)
                 c.Name.ToUpper <> "NAME" and
                 c.IsUsedInPrimaryKey = false and
                 c.GetType() <> GetType(Varigence.Languages.Biml.Table.AstTableColumnTableReferenceNode)) #>
            <Property Name="<#= col.Name #>" DataType="String" />
            <# next #>
          </Properties>
          <Relationships>
            <# for each fk in tbl.Columns.OfType(Of AstTableColumnTableReferenceNode) #>
            <Relationship Name="<#= fk.ForeignTable #>"
                          Cardinality="ManyToOne"
                          EntityName="<#= fk.ForeignTable #>" />
            <# next #>
          </Relationships>
        </Entity>
        <# next #>
      </Entities>
    </MetadataModel>

    <MetadataInstance Name="MetadataInstance" MetadataModelName="MetaData">
      <Entities>
        <# for each tbl in MetaSchema.TableNodes #>
        <Entity Name="<#= tbl.Name #>" MetadataModelEntityName="MetaData.<#= tbl.Name #>">
          <DataItems>
            <# for each datRow in ExternalDataAccess.GetDataTable(connMetadata,
                 "select * from [meta].[" & tbl.Name & "]").Rows #>
              <# if tbl.Columns.Where(Function(c) c.IsUsedInPrimaryKey = true).Any then #>
              <DataItem Name="<#= datRow(tbl.Columns.Where(Function(c) c.IsUsedInPrimaryKey = true).First().Name) #>">
              <# else #>
              <DataItem Name="<#= datRow(0) #>">
              <# end if #>
                <Properties>
                  <# for each col in tbl.Columns.Where(Function(c)
                       c.Name.ToUpper <> "NAME" and
                       c.IsUsedInPrimaryKey = false and
                       c.GetType() <> GetType(Varigence.Languages.Biml.Table.AstTableColumnTableReferenceNode)) #>
                  <Property PropertyName="MetaData.<#= tbl.Name #>.<#= col.Name #>">
                    <Value><#= datRow(col.Name) #></Value>
                  </Property>
                  <# next #>
                </Properties>
                <Relationships>
                  <# for each fk in tbl.Columns.OfType(Of AstTableColumnTableReferenceNode) #>
                  <Relationship RelationshipName="MetaData.<#= tbl.Name #>.<#= fk.ForeignTable #>"
                                RelatedItemName="<#= fk.ForeignTable #>.<#= datRow(fk.Name) #>" />
                  <# next #>
                </Relationships>
              </DataItem>
            <# next #>
          </DataItems>
        </Entity>
        <# next #>
      </Entities>
    </MetadataInstance>
  </Metadata>
</Biml>

How It Works

The first pass over MetaSchema.TableNodes builds a MetadataModel that mirrors the database. Each non key, non foreign key column becomes a string Property; each foreign key column becomes a ManyToOne Relationship pointing at the foreign table.

The second pass walks the same tables again to build the MetadataInstance. For every row, the script picks a key for the DataItem (the first primary key column when one exists, otherwise the first column), emits Property entries for each non key column with the row's values, and emits Relationship entries pointing at the related DataItem by its key.

The resulting Metadata object is queryable inside BimlStudio with the same dynamic object syntax used for hand authored metadata.

Without BimlStudio

The Metadata feature is not available in BimlExpress. A separate walkthrough covers a DataSet driven approach that gives BimlExpress projects similar flexibility.