Lesson 4: Dimension samples and foreign keys

  • Article

Dimensions and fact tables are the basis of the data warehouses you’re going to create.

In this lesson, we’ll see how 2 demo dimensions are created, and how a foreign key is created.

It isn’t uncommon that dimensions and fact tables are referencing foreign keys from other dimensions and tables. Using Vulcan, this step will be convenient and easy to maintain. The experience you learn in this lesson can surely be applied to much more complicated scenarios for your own dimensions and fact tables.

Let’s take a look at the code first.

<?xml version="1.0" encoding="utf-8" ?>

<Vulcan xmlns="https://tempuri.org/vulcan2.xsd">

     

      <Connection Name="DataWarehouse" Type="OLEDB" ConnectionString="Data Source=localhost;Initial Catalog=DataWarehouse;Provider=SQLNCLI10.1;Integrated Security=SSPI;" />

     

      <Dimensions>

            <Dimension ConnectionName="DataWarehouse" Name="TestDimA">

                  <Columns>

                        <Column Name="ID" Type="INT64"/>

                        <Column Name="Text" Type="WSTR" Length="255"/>

                  </Columns>

                  <Keys>

                        <Identity Name="IK_TestDimA_ID" Clustered="true">

                              <Column ColumnName="ID"/>

                        </Identity>

                  </Keys>

                  <Sources>

                        <StaticSource>

                              <Row>-1,'Unknown'</Row>

                        </StaticSource>

                  </Sources>

            </Dimension>

            <Dimension ConnectionName="DataWarehouse" Name="TestDimB">

                  <Columns>

                        <Column Name="ID" Type="INT64"/>

                        <Column Name="Text" Type="WSTR" Length="255"/>

                        <Dimension DimensionName="TestDimA" OutputName="TestDimAID"/>

                  </Columns>

                  <Keys>

                        <Identity Name="IK_TestDimB_ID" Clustered="true">

                              <Column ColumnName="ID"/>

                        </Identity>

                        <UniqueKey Name="UK_TestDimB_TestDimAID">

                              <Column ColumnName="TestDimAID"/>

                        </UniqueKey>

                  </Keys>

                  <Sources>

                        <StaticSource>

                              <Row>-1,'Unknown',-1</Row>

                        </StaticSource>

                  </Sources>

            </Dimension>

      </Dimensions>

</Vulcan>

 

It’s as straightforward as the code shows.

By running Vulcan to process this DimensionDemo.xml, 2 packages are created in the binary folder you appointed, which can create TestDimA and TestDimB in DataWarehouse when you execute these 2 packages.

Both dimensions have 2 columns, and an identity key. Now the important line is as below:

                        <Dimension DimensionName="TestDimA" OutputName="TestDimAID"/>

 

The above line in the definition of TestDimB will create a foreign key named TestDimAID and will be linked to the identity or primary key of TestDimAID.

This is very easy, isn’t it?

The UK_TestDimB_TestDimAID is another unique key created upon the TestDimAID.

Many cases can use similar code as this sample, but if the requirement is to create foreign key on non-primary key, or multiple-column keys, the code will look different. Details will be shown in next lesson.