Create a Fact Table

To create a Fact table, right click the ‘Fact Tables’ folder (or one of its sub folders) and select Create New. There are two ways to create a Fact Table. By far the quickest way is to import the Meta Data from a Source System, and then edit as required. If the Source Meta data is not available, then you can manually enter the information.

Import Schema

Use the Import button on the Fact Table tab to import the Meta Data for the Fact Table from a Source System. You must first create a connection to the Source System through a Connection Manager prior to import. To Import the Meta Data.

  1. On the Fact Table Measures tab, click the Import Schema button.
  2. Select the Connection where the source table resides.
  3. Select the Table and press OK.

The Fact Table Measures will be populated with the column names and data types from the source table. Some connection types don’t return column data types.

Fact Table Tab

  • Fact Table Name. The Fact Table Name is the name of your table in your data warehouse relational database. Spaces in the name will be converted to _ automatically when you save. The prefix for the table name can be set in the configuration utility.
  • Fact Friendly Name. This is the Measure Group Name the user would see for the Fact Table through a Cube browser. Spaces are not replaced in this name.
  • Fact Table Description. A description of the contents of the Fact Table. Optional. This is used when documentation is generated.
  • Reference. This is a free form text you can use to reference the dimension design back to another design document, perhaps a business requirements document.

Fact Table Measures Tab

On this tab you need to define both the Business Keys (Natural Keys) and Measures of the Fact. All other columns that may have been imported from a Source table should be deleted. Columns used to lookup dimension don’t need to be listed in this table.

  • Measure Name. Enter the measure name. Spaces are replaced with _ when the Fact Table is saved. The Name must be unique within the set of Measures for the Fact.
  • Data Type. Select a data type from the dropdown list, or just type the data type. The data type will be validated to ensure it is a valid SQL Server data type when the data type field loses focus. If the data type is invalid, a dialog box will display, and the field cleared.
  • Description. Add a description of the measure. Generally the description should make sense to an end user. Descriptions are used in Data Dictionary generation.
  • Reference. This is a free form text you can use to reference the measure back to another design document, or perhaps a report the measure appears on.
  • Business Key. Tick the row(s) that represent the Fact Table’s business/natural key. This is used in the definition of the Transform/Load process for the table.

To Delete a Measure

Click the delete link next to the measure in the Measures table. Changes are only made permanent after the document is saved.

To Change a Measure

Select the measure row and tap the delete key, or click the Delete Attribute(s) button. You can select multiple rows.

Associating Dimensions

In order to complete the star schema of the fact table you must associate it with one or more of the dimensions in the project. To associate a Fact Table to a dimension, use the Dimensionality tab of the Fact Table. Select the dimension you want to associate to the fact table in the drop down list in the Dimension column. Click on another cell to ensure the association is recognized. If you intend to associate the dimension as a role play dimension, then give it a role play name. A dimension can be associated to a fact table multiple times, but only once without a role play name. That is, you can associate a dimension to a fact without a role play name once, and every other association must have a role play name and each role play name must be different. Changes are not saved until the Fact table is saved.

Custom Management Columns

What are Management Columns? Management columns are columns that are added to your Fact and Dimension tables to help manage the data, during ETL. Management Columns include things like:

  • Batch Identifier.
  • Row Status (Current – Not Current).
  • Row Effective Dates.
  • Checksum (if you use checksums in your ETL)

Dimodelo Data Warehouse Studio allows you to specify Custom management columns on an individual Fact/Dimension basis. A set of Standard Management Columns is specified for all Fact/Dimension tables. See the Standard Management Columns section for an explanation of Standard Management Columns. Custom Management columns can be used for Fact and Dimensions that have custom ETL that requires additional management columns for its operation. Custom Management Columns can be added, changed and deleted through the Management Columns tab > Custom Management Columns table in the Fact and Dimensions editor.