Lesson 5: FactTable and the “-i” command line usage

  • Article

We’ve learned to create dimensions and use simple foreign keys in previous lessons. Now it’s time to learn creating a FactTable. Although the FactTable shown here is simple, you can expand it to customize your own complex fact tables easily.

The content of TestFactTable.xml is as below:

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

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

    <Fact Name="TestFactTableA" ConnectionName="DataWarehouse">

        <Columns>

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

                  <Column Name="Name" Type="WSTR" />

                  <Column Name="Price" Type="INT32" />

                  <Column Name="Amount" Type="INT32" />

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

        </Columns>

        <Keys>

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

                        <Column ColumnName="ID"/>

                  </Identity>

            </Keys>

    </Fact>

</Vulcan>

It’s a straightforward file, defines some columns, an identity key and a foreign key.

Well, if you just use simple “Vulcan” command to process it, you’ll see errors or warnings when opening the generated solution. The reason is that this file contains only its own definition for clarity, so that the external definitions, such as the Connection, and the Dimension, also need to be referenced. Remember the dimension file created in last lesson? It’s time to use it in the command line as below:

vulcan TestFactTable.xml -i TestDim.xml

The “-i” switch tells Vulcan to look for external definitions in other xml files. Actually you can add more xml files after that, with “-i” only shown once.

After executing this command line, you’ll see the generated solution and its files, and of course you can run it and find a new table TestFactTableA is created in DataWarehouse. The columns and keys should be created as described in this xml file.

In the following lessons, we’ll learn how to write ETL process to load data to tables, especially fact tables. You’ll find out this time-consuming task becoming easier to control in Vulcan.