Introduction

In this lesson, we will take a brief tour of the key relational modeling features available in Biml. You'll also learn a bit about how to use tables and other relational assets from within your Biml project - and even how to deploy them to the database server when ready.

Connections

When describing relational database structures, the most basic thing we need to track are server and database connections. While Biml offers a wide variety of connection types that can be used for relational, SSIS, and SSAS, in this lesson, we'll focus just on those that you would use for relational.

To create connections, you will need to add a Connections collection element under the root of your Biml document and then add whichever connections you desire. Let's start with an OLEDB connection for starters:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="Connection1" ConnectionString="Server=localhost;Initial Catalog=AdventureWorks2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
    </Connections>
</Biml>

You'll immediately notice that Biml provides a fairly low-level interface for connection specification. As a baseline, it's just a name and a connection string. If you are uncomfortable writing your own connection strings, the Mist IDE has a visual designer for creating connection strings, or you can use one of many other tools that provide connection string authoring functionality. If you just need a refresher on the syntax for your connection string, I recommend consulting the excellent ConnectionStrings.com.

Of course, we're not limited to OLEDB connections. Below is a code sample that show a selection of the most commonly used database connections along with the minimum configuration required to get them working:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="OleDbConnection1" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
        <AdoNetConnection Name="AdoNetConnection1" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;" Provider="System.Data.SqlClient" />
        <OdbcConnection Name="OdbcConnection1" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
        <OracleConnection Name="OracleConnection1" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
        <TeradataConnection Name="TeradataConnection1" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
        <SqlServerPdwConnection Name="SqlServerPdwConnection1" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" StagingDatabase="Staging" />
    </Connections>
</Biml>

Of course, OLEDB, ADO.NET, and ODBC connections do exactly what you would expect. Furthermore, you could use those connection types to connect to almost any kind of commercially available database server. So why do we have special connection type for Oracle, Teradata, and Microsoft SQL Server APS/PDW? Firstly, they tend to be a bit more convenient to use than having to configure the underlying connection type yourself, since they automatically locate the correct provider and/or driver on your system. Secondly, while their functionality is largely the same as the underlying connection OLEDB, ADO.NET and ODBC connection types when performing relational tasks, they are translated differently into SSIS. In SSIS, Oracle and Teradata connections correspond with the Attunity connection managers, while the SqlServerPdwConnection corresponds to Microsoft's custom APS/PDW connection manager that ships with the PDW Destination component.

Databases

Once we have setup our connections, we can model our databases.

First a very important note. In many cases, Biml requires you to model your database as part of the schema and table modeling process. This does not mean that you must use Biml to deploy or manage your actual databases. Of course, you have that option. But if you prefer to use an existing database or use a custom database creation process, you can do that too. Relational objects created by Biml will ONLY be deployed to a server when you take explicit action to do so. So you never have to worry about databases being dropped, created, or modified without your explicit knowledge and consent.

For this and future samples we will iteratively build an increasingly complex working Biml sample. So we'll include both the database and its associated connection.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="Connection1" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
    </Connections>
    <Databases>
        <Database Name="AdventureWorksDW2012" ConnectionName="Connection1" />
    </Databases>
</Biml>

Like connections, you don't need to specify very much to arrive at a working database. If you choose, you can add configuration for partition schemes, file groups, and other database modeling features. To learn more about that, please take a look at the Biml reference documentation for databases.

One more thing to notice about databases. They must reference a connection. Remember from the Biml Basics lesson that you can reference named items (such as "Connection1") by their scoped name when needed. That's all we're doing above.

Schemas

Biml also enables you to model your own schemas. Remember that schemas are simply groups of database objects that provide their own security context. Defining a schema works just like defining a database:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="Connection1" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
    </Connections>
    <Databases>
        <Database Name="AdventureWorksDW2012" ConnectionName="Connection1" />
    </Databases>
    <Schemas>
        <Schema Name="dbo" DatabaseName="AdventureWorksDW2012" />
    </Schemas>
</Biml>

While you can model either new schemas or schemas that already exist in your database, you actually don't need to model any schemas in most cases. Every database automatically creates a Biml schema corresponding to its default schema. As we'll see in examples in the next section, to reference it, just use the name: Database.[default].

Tables

Now it's time to get into the core of relational modeling: tables.

General

To start with, we will add an empty table to our Biml file:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="Connection1" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
    </Connections>
    <Databases>
        <Database Name="AdventureWorksDW2012" ConnectionName="Connection1" />
    </Databases>
    <Schemas>
        <Schema Name="dbo" DatabaseName="AdventureWorksDW2012" />
    </Schemas>
    <Tables>
        <Table Name="DimAccount" SchemaName="AdventureWorksDW2012.dbo" />
    </Tables>
</Biml>

This will create a single empty table that lives in the dbo schema of the AdventureWorks2012 database. Notice that for SchemaName, we had to fully specify the schema name, including its database name. This is because we might have many schemas defined in our solution, each with the same name of dbo but each in a different database. Using the full scoped name enables the Biml compiler to understand which one you mean to use.

Since dbo is the default schema for AdventureWorks2012 and to reduce the amount of code we use in each example, I'm going to remove the schema definition and reference the default schema in the table definition:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="Connection1" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
    </Connections>
    <Databases>
        <Database Name="AdventureWorksDW2012" ConnectionName="Connection1" />
    </Databases>
    <Tables>
        <Table Name="DimAccount" SchemaName="AdventureWorksDW2012.[default]" />
    </Tables>
</Biml>

At this point, we could add many additional configuration properties to our table. Since this is an overview of basics, I'll refer you to the Biml reference documentation for tables for more information on that.

Columns

What's a table without columns? It's certainly not very interesting, so let's add a few:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="Connection1" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
    </Connections>
    <Databases>
        <Database Name="AdventureWorksDW2012" ConnectionName="Connection1" />
    </Databases>
    <Tables>
        <Table Name="DimAccount" SchemaName="AdventureWorksDW2012.[default]">
            <Columns>
                <Column Name="AccountID" DataType="Int32" IsNullable="false" IdentityIncrement="1" />
                <Column Name="AccountKey" DataType="Int32" IsNullable="false" />
                <Column Name="AccountDescription" DataType="String" Length="50" />
                <Column Name="AccountAge" DataType="Decimal" Precision="12" Scale="12" />
            </Columns>
        </Table>
    </Tables>
</Biml>

Columns have a tremendous amount of configurability exposed through their attributes. The example above attempts to demonstrate some of the more commonly used configuration. Let's take a look at those attributes one-by-one:

• Name: As with all other instances of the Name attribute in Biml, this enables you to supply the name. In this case, the name is used both for Biml references and as part of the relational model.

• DataType: Using the System.Data.DbType type system, this attribute allows you to specify the type of the column. If you need to specify a user defined type (UDT) or something else that is custom, you can override this attribute with the CustomType attribute. While it is almost always overridden, DataType is not actually a required property and defaults to Int32.

• IsNullable: This attribute is a simple boolean that indicates whether or not the column should support null values. The default is True.
• IdentityIncrement: If you need a column to behave as an identity, set this attribute to something other than the default value of 0. Usually, you would set it to 1. Note that you can also change the seed value using the IdentitySeed attribute.

And of course, we also have our data type modifier attributes:

• Length: When you have chosen a data type that supports a length specification (e.g. String, AnsiString, StringFixedLength, AnsiStringFixedLength, or Binary), this is the attribute that you use to set that length. If your target relational platform supports the notion of MAX as a value for length, you can use -1 as your Length value to specify that.
• Precision: For data types such as decimals that support precision, you can set the precision with this attribute. Based on your connection string, default values that match the default data type precision of the target platform will be used if it is left unspecified.
• Scale: For data types such as decimals that support scale, you can set the scale with this attribute. Based on your connection string, default values that match the default data type scale of the target platform will be used if it is left unspecified.

Columns also support a few metadata attributes. These attributes have no direct effect on code generation, but since they are so commonly used in column metadata, they have been added as attributes to make them easier to use. Of course, if you wish to track this metadata information in your own Biml annotations, as we saw in the Biml Basics lesson, you can certainly do so.

• ScdType: This attribute allows you to select from a user friendly list of SCD types to apply to your column. For example, Type 1 corresponds to "Update" while Type 2 corresponds to "Historical". Please note that, as stated above, this has no effect on code generation. This attribute is supplied strictly for your convenience in tracking this metadata for later use.

You may have noticed from the Biml documentation or Mist completion lists that there are other types of columns. These are convenience types that allow you to represent sometimes very complex modeling tasks with a very simple syntax. Let's take a look at each of these additional column types in turn.

TableReference

Suppose you are building a dimension model using the common design pattern where each table will include a surrogate ID key that is both an identity and the primary key for the table. All foreign key references are then constructed to specifically reference those ID columns. TableReferences are essentially optimized to support this design pattern. When you create a table reference, you don't actually supply the ID column but rather its parent table. The TableReference will automatically find the appropriate identity primary key column and construct the foreign key accordingly. Let's take a look at how this syntax works:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="Connection1" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
    </Connections>
    <Databases>
        <Database Name="AdventureWorksDW2012" ConnectionName="Connection1" />
    </Databases>
    <Tables>
        <Table Name="DimAccount" SchemaName="AdventureWorksDW2012.[default]">
            <Columns>
                <Column Name="AccountID" DataType="Int32" IsNullable="false" IdentityIncrement="1" />
                <Column Name="AccountKey" DataType="Int32" IsNullable="false" />
                <Column Name="AccountDescription" DataType="String" Length="50" />
                <Column Name="AccountAge" DataType="Decimal" Precision="12" Scale="12" />
            </Columns>
        </Table>
        <Table Name="FactSales" SchemaName="AdventureWorksDW2012.[default]">
            <Columns>
                <Column Name="SaleID" DataType="Int32" IsNullable="false" IdentityIncrement="1" />
                <TableReference Name="AccountID" TableName="AdventureWorksDW2012.[default].DimAccount" />
                <Column Name="Amount" DataType="Currency" />
            </Columns>
        </Table>
    </Tables>
</Biml>

There is another benefit to using TableReferences. Suppose you'd like to track the reference for later use in your BimlScripts but you don't actually want to create the foreign key constraint (or alternatively, you'd like to create the constraint but not enforce it in the database). This can be useful in some situations such as staging or partial data movement scenarios when either you know the constraint would fail or where the performance penalty of enforcing the constraint is unnecessary. To support these scenarios, the TableReference column supports the ForeignKeyConstraintMode attribute which allows you to configure the constraint settings in the relational model for the relationship. Here are two example:

<TableReference Name="AccountID" TableName="AdventureWorksDW2012.[default].DimAccount" ForeignKeyConstraintMode="CreateAndNoCheck" />
<TableReference Name="AccountID2" TableName="AdventureWorksDW2012.[default].DimAccount" ForeignKeyConstraintMode="DoNotCreate" />

MultiColumnTableReference

The TableReference element is pretty neat, but what if I have multiple columns in my relationship? Does Biml also have a column type for that? In short, yes. To reference multiple columns in a foreign table, you create multiple MultiColumnTableReference columns in your table. Each of those columns must provide 3 attributes:

• Name: The name of the column in the referencing table.
• ForeignColumnName: A reference to the foreign table column that you wish to create the relationship with. Note that since you can use a full scoped name, we don't need to separately specify the foreign table - it's already implicitly provided by the column reference.
• MultipleColumnTableReferenceGroupName: This one is a bit more interesting. Suppose you want to create multiple foreign key constraints to the same foreign table/columns. This would be common, for example, in a role playing dimension scenario. The Biml compiler needs some way to know which column references belong to which foreign key. The group enables us to specify that with a simple string label. It doesn't matter what value you use, as long as all columns participating in the same foreign key use the same value for Group.

Let's take a look at an example that uses multiple foreign keys:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="Connection1" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
    </Connections>
    <Databases>
        <Database Name="AdventureWorksDW2012" ConnectionName="Connection1" />
    </Databases>
    <Tables>
        <Table Name="DimAccount" SchemaName="AdventureWorksDW2012.[default]">
            <Columns>
                <Column Name="AccountID" DataType="Int32" IsNullable="false" IdentityIncrement="1" />
                <Column Name="AccountKey" DataType="Int32" IsNullable="false" />
                <Column Name="AccountDescription" DataType="String" Length="50" />
                <Column Name="AccountAge" DataType="Decimal" Precision="12" Scale="12" />
            </Columns>
        </Table>
        <Table Name="FactSales" SchemaName="AdventureWorksDW2012.[default]">
            <Columns>
                <Column Name="SaleID" DataType="Int32" IsNullable="false" IdentityIncrement="1" />
                <Column Name="Amount" DataType="Currency" />
                <MultipleColumnTableReference Name="ForeignAccountID" ForeignColumnName="DimAccount.AccountID" MultipleColumnTableReferenceGroupName="AccountGroup1" />
                <MultipleColumnTableReference Name="ForeignAccountKey" ForeignColumnName="DimAccount.AccountKey" MultipleColumnTableReferenceGroupName="AccountGroup1" />
                <MultipleColumnTableReference Name="ForeignAccountID2" ForeignColumnName="DimAccount.AccountID" MultipleColumnTableReferenceGroupName="AccountGroup2" />
                <MultipleColumnTableReference Name="ForeignAccountKey2" ForeignColumnName="DimAccount.AccountKey" MultipleColumnTableReferenceGroupName="AccountGroup2" />
            </Columns>
        </Table>
    </Tables>
</Biml>

HashKeyColumn

HashKeyColumn is the final convenience column type. In some design patterns, such as Data Vault, it is common to create a single column that is a hash of the individual business key column values to use as a surrogate key an perhaps the primary key. The HashedKeyColumn gives us a simple and convenient way to do that. Just supply it with a UniqueKey (which we'll learn more about in the next section) that lists the columns to hash, and the Biml compiler will do the rest, including creating a column with a computed value expression that uses an appropriate hashing function on your target platform:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="Connection1" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
    </Connections>
    <Databases>
        <Database Name="AdventureWorksDW2012" ConnectionName="Connection1" />
    </Databases>
    <Tables>
        <Table Name="DimAccount" SchemaName="AdventureWorksDW2012.[default]">
            <Columns>
                <Column Name="AccountID" DataType="Int32" IsNullable="false" IdentityIncrement="1" />
                <Column Name="AccountKey" DataType="Int32" IsNullable="false" />
                <Column Name="AccountDescription" DataType="String" Length="50" />
                <Column Name="AccountAge" DataType="Decimal" Precision="12" Scale="12" />
                <HashedKey Name="TableHash" ConstraintName="UK_AccountKey" />
            </Columns>
            <Keys>
                <UniqueKey Name="UK_AccountKey">
                    <Columns>
                        <Column ColumnName="AccountKey" />
                        <Column ColumnName="AccountAge" />
                    </Columns>
                </UniqueKey>
            </Keys>
        </Table>
    </Tables>
</Biml>

Keys

Once we've defined the table columns, we likely want to define the primary key and perhaps some unique constraints (perhaps to encode our business key(s)). In Biml, this functionality is exposed through table "keys". Before looking at keys in detail, note that if you prefer to use T-SQL to define these structures, you can do so through table custom extensions, which you'll learn more about later.

There are three basic types of keys: PrimaryKey, UniqueKey, and Identity. In the following sections, we'll learn more about each.

Primary Key

Defining a primary key is exceedingly simple. Just give it a name and supply the list of columns that comprise the primary key:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="Connection1" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
    </Connections>
    <Databases>
        <Database Name="AdventureWorksDW2012" ConnectionName="Connection1" />
    </Databases>
    <Tables>
        <Table Name="DimAccount" SchemaName="AdventureWorksDW2012.[default]">
            <Columns>
                <Column Name="AccountID" DataType="Int32" IsNullable="false" IdentityIncrement="1" />
                <Column Name="AccountKey" DataType="Int32" IsNullable="false" />
                <Column Name="AccountDescription" DataType="String" Length="50" />
                <Column Name="AccountType" DataType="String" Length="50" />
            </Columns>
            <Keys>
                <PrimaryKey Name="PK_DimAccount">
                    <Columns>
                        <Column ColumnName="AccountID" />
                    </Columns>
                </PrimaryKey>
            </Keys>
        </Table>
    </Tables>
</Biml>

If you'd like to customize the primary key with attributes such as FillFactor, PadIndex, column SortOrder, or anything else your relational system supports, you can do that also. Check out the Primary Key reference documentation for more information: https://www.varigence.com/Documentation/Language/Element/AstTablePrimaryKeyNode

Unique Key

Another common task is to create a unique key constraint. This is often done when you would like the database engine to automatically enforce that business key columns are unique across rows - even when you have a separate surrogate key that is used as the primary key for the table.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="Connection1" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
    </Connections>
    <Databases>
        <Database Name="AdventureWorksDW2012" ConnectionName="Connection1" />
    </Databases>
    <Tables>
        <Table Name="DimAccount" SchemaName="AdventureWorksDW2012.[default]">
            <Columns>
                <Column Name="AccountID" DataType="Int32" IsNullable="false" IdentityIncrement="1" />
                <Column Name="AccountKey" DataType="Int32" IsNullable="false" />
                <Column Name="AccountDescription" DataType="String" Length="50" />
                <Column Name="AccountType" DataType="String" Length="50" />
            </Columns>
            <Keys>
                <PrimaryKey Name="PK_DimAccount">
                    <Columns>
                        <Column ColumnName="AccountID" />
                    </Columns>
                </PrimaryKey>
                <UniqueKey Name="UK_DimAccount_AccountKey">
                    <Columns>
                        <Column ColumnName="AccountKey" />
                    </Columns>
                </UniqueKey>
            </Keys>
        </Table>
    </Tables>
</Biml>

As with PrimaryKeys, you can further customize UniqueKeys using the attributes described in the Unique Key reference documentation: https://www.varigence.com/Documentation/Language/Element/AstTableUniqueKeyNode

Identity

The final type of key is a convenience feature and probably shouldn't be used unless your relational design exclusively uses single identity column surrogate keys for all tables. When you create an identity key, the Biml compiler will automatically set the identity seed and increment for the column and also create a primary key constraint using that column. Let's take a look at the identity key in action:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="Connection1" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
    </Connections>
    <Databases>
        <Database Name="AdventureWorksDW2012" ConnectionName="Connection1" />
    </Databases>
    <Tables>
        <Table Name="DimAccount" SchemaName="AdventureWorksDW2012.[default]">
            <Columns>
                <Column Name="AccountID" DataType="Int32" IsNullable="false" />
                <Column Name="AccountKey" DataType="Int32" IsNullable="false" />
                <Column Name="AccountDescription" DataType="String" Length="50" />
                <Column Name="AccountType" DataType="String" Length="50" />
            </Columns>
            <Keys>
                <Identity Name="PK_DimAccount">
                    <Columns>
                        <Column ColumnName="AccountID" />
                    </Columns>
                </Identity>
            </Keys>
        </Table>
    </Tables>
</Biml>

As with the other key types, you can further customize Identity keys using the attributes described in the Identity reference documentation: https://www.varigence.com/Documentation/Language/Element/AstTableIdentityNode

Indexes

Next, we'd like to add indexes to our table. For this sample, we're going to revert back to a previous sample that includes a primary and unique key for DimAccount. The syntax for indexes is very similar to keys. At a minimum, you simply name the index and list its constituent columns in the order you want them to appear in the index:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="Connection1" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
    </Connections>
    <Databases>
        <Database Name="AdventureWorksDW2012" ConnectionName="Connection1" />
    </Databases>
    <Tables>
        <Table Name="DimAccount" SchemaName="AdventureWorksDW2012.[default]">
            <Columns>
                <Column Name="AccountID" DataType="Int32" IsNullable="false" IdentityIncrement="1" />
                <Column Name="AccountKey" DataType="Int32" IsNullable="false" />
                <Column Name="AccountDescription" DataType="String" Length="50" />
                <Column Name="AccountType" DataType="String" Length="50" />
            </Columns>
            <Keys>
                <PrimaryKey Name="PK_DimAccount">
                    <Columns>
                        <Column ColumnName="AccountID" />
                    </Columns>
                </PrimaryKey>
                <UniqueKey Name="UK_DimAccount_AccountKey">
                    <Columns>
                        <Column ColumnName="AccountKey" />
                    </Columns>
                </UniqueKey>
            </Keys>
            <Indexes>
                <Index Name="IX_DimAccount">
                    <Columns>
                        <Column ColumnName="AccountType" />
                    </Columns>
                </Index>
            </Indexes>
        </Table>
    </Tables>
</Biml>

As with keys, you can further customize indexes using the attributes described in the Index reference documentation: https://www.varigence.com/Documentation/Language/Element/AstTableIndexNode

Static Sources

In many database designs, some tables are used to store a static collection of values that are maintained by the database developers. For example, you might have a table that tracked the sources from which data was loaded or a static list of date values for DimDate.

Alternatively, you might want to prepopulate your tables with a few static values that will then be supplemented with the data that your packages load. For example, you might want to ensure that every dimension has a value for Unknown.

To do this you can use the StaticSource syntax within Biml, as follows:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="Connection1" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
    </Connections>
    <Databases>
        <Database Name="AdventureWorksDW2012" ConnectionName="Connection1" />
    </Databases>
    <Tables>
        <Table Name="DimAccount" SchemaName="AdventureWorksDW2012.[default]">
            <Columns>
                <Column Name="AccountID" DataType="Int32" IsNullable="false" IdentityIncrement="1" />
                <Column Name="AccountKey" DataType="Int32" IsNullable="false" />
                <Column Name="AccountDescription" DataType="String" Length="50" />
                <Column Name="AccountAge" DataType="Decimal" Precision="12" Scale="12" />
                <HashedKey Name="TableHash" ConstraintName="UK_AccountKey" />
            </Columns>
            <Keys>
                <UniqueKey Name="UK_AccountKey">
                    <Columns>
                        <Column ColumnName="AccountKey" />
                        <Column ColumnName="AccountAge" />
                    </Columns>
                </UniqueKey>
            </Keys>
            <Sources>
                <StaticSource Name="StaticSource">
                    <Rows>
                        <Row>
                            <ColumnValues>
                                <ColumnValue ColumnName="AccountID" Value="-1" />
                                <ColumnValue ColumnName="AccountKey" Value="-1" />
                                <ColumnValue ColumnName="AccountDescription" Value="N'Unknown'" />
                                <ColumnValue ColumnName="AccountAge" Value="0" />
                            </ColumnValues>
                        </Row>
                    </Rows>
                </StaticSource>
            </Sources>
        </Table>
    </Tables>
</Biml>

When you build this table, the static source will be automatically translated into the corresponding SQL INSERT statements to insert those values.

Note that the syntax for static sources is a bit verbose - that is, you have to write a lot of XML for each row. Consequently, it can be a very good choice for a small number of values. If you have a large amount of static source data, you might find that writing INSERT SQL is a better way to persist those values.

Custom Extensions

When using Mist, building Biml tables not only creates the SQL DDL to create those tables, but it also creates SSIS packages that execute the DDL. This can be quite useful for orchestration and deployment. Biml gives you the ability to inject additional code into these packages that can be used to customize initial values, setup, or other important configuration. To do so, you create a custom extension, which is essentially an SSIS package fragment within the table definition. Biml Basics for SSIS are covered in another lesson, so we'll keep the package fragment very simple in the example below. To call a stored procedure after creating the table, we would do the following:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="Connection1" ConnectionString="Server=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Provider=SQLNCLI11;" />
    </Connections>
    <Databases>
        <Database Name="AdventureWorksDW2012" ConnectionName="Connection1" />
    </Databases>
    <Tables>
        <Table Name="DimAccount" SchemaName="AdventureWorksDW2012.[default]">
            <Columns>
                <Column Name="AccountID" DataType="Int32" IsNullable="false" IdentityIncrement="1" />
                <Column Name="AccountKey" DataType="Int32" IsNullable="false" />
                <Column Name="AccountDescription" DataType="String" Length="50" />
                <Column Name="AccountAge" DataType="Decimal" Precision="12" Scale="12" />
                <HashedKey Name="TableHash" ConstraintName="UK_AccountKey" />
            </Columns>
            <Keys>
                <UniqueKey Name="UK_AccountKey">
                    <Columns>
                        <Column ColumnName="AccountKey" />
                        <Column ColumnName="AccountAge" />
                    </Columns>
                </UniqueKey>
            </Keys>
            <Sources>
                <StaticSource Name="StaticSource">
                    <Rows>
                        <Row>
                            <ColumnValues>
                                <ColumnValue ColumnName="AccountID" Value="-1" />
                                <ColumnValue ColumnName="AccountKey" Value="-1" />
                                <ColumnValue ColumnName="AccountDescription" Value="N'Unknown'" />
                                <ColumnValue ColumnName="AccountAge" Value="0" />
                            </ColumnValues>
                        </Row>
                    </Rows>
                </StaticSource>
            </Sources>
            <CustomExtensions>
                <CustomExtension Name="CustomExtension">
                    <Tasks>
                        <ExecuteSQL Name="Execute Stored Procedure" ConnectionName="Connection1">
                            <DirectInput>EXEC usp_StoredProcedure</DirectInput>
                        </ExecuteSQL>
                    </Tasks>
                </CustomExtension>
            </CustomExtensions>
        </Table>
    </Tables>
</Biml>

Building and Deploying Tables

As noted in the previous section, Mist automatically creates SSIS packages for each of your tables that can be used to create those tables in your database. You can also easily create a master package that will execute each of those table creation packages.

Alternatively, you can use the GetDropAndCreateDdl utility method in BimlScript (which you'll learn more about later) to create the necessary SQL scripts to deploy the tables directly.

In most cases, the best practice for incremental deployment, where you want to update tables that may have already been deployed to the database, is to do the following:

1. Create a new environment called the "Build" environment. This is a lightweight environment that contains a clean version of the desired database schema. Note that this database will not contain any actual data, aside from perhaps small amounts of static data defined in StaticSources.
2. Build and deploy the auto-generated schema to the build environment. Since the build environment contains no data, this can be destructive. In other words, it's fine to drop and recreate everything.
3. Use your favorite schema comparison tool (from Microsoft, third parties, or your own schema comparison scripts written with Biml) to create an incremental deployment script for your actual Dev/Test/Prod environment. This can be done by comparing the clean build environment against the target environment that you want to match the build environment schema.
4. Run the incremental deployment script from step 3.

Conclusion

Biml has a variety of rich features for relational modeling and managing database creation. This lesson gave you a brief walk through of the most important features. You'll find in future lessons that these features become considerably more powerful as they extended with BimlScript automation.