Add Fact Tables and Dimension Tables to a Data Model

Use fact tables and dimension tables to represent aspects of your business that you want to understand better.

About Fact Tables and Dimension Tables

Fact tables and dimension tables hold the columns that store the data for the model:

  • Fact tables contain measures, which are columns that have aggregations built into their definitions. For example, Revenue and Units are measure columns.

  • Dimension tables contain attributes that describe business entities. For example, Customer Name, Region, and Address are attribute columns.

Fact tables and dimension tables represent the aspects of your business that you want to understand better. See Components of Data Models.

Before you begin modeling fact tables and dimension tables, make sure that the data that you need to model is available in the source tables list. Also ensure that you have created any source views upon which to base model objects.

If you think the list of source objects in the database has changed since you opened Data Modeler, then you can click Refresh from the Database Actions menu. If the data that you need has not yet been loaded into the database, then you can load it.

Create Fact and Dimension Tables from a Single Table or View

Some source tables contain both facts and dimensions. For these source tables, Data Modeler provides a wizard to help you partition the fact and dimension columns into fact tables and dimension tables.

For example, you might have a source that contains both product and customer attributes, as well as revenue measures. Use the wizard to create the corresponding fact and dimension tables.

  1. In Data Modeler, lock the model for editing.
  2. In the Database menu in the left pane, right-click the source table that contains the fact and dimensional data that you want to model, select Add to Model, and then select Add as Fact and Dimension Tables.
  3. To let Data Modeler suggest some fact tables, dimension tables, and joins for the source table, select Let Data Modeler Recommend and click OK. You can review suggestions in Step 4.
    If you’d rather choose fact and dimension tables yourself from scratch:
    1. Deselect Let Data Modeler Recommend and click OK.
    2. Drag measures from the source table onto the fact table.

      Tip:

      You can also click the Plus icon in the column header area to select a column to include in the fact table.
    3. Enter a name for the fact table, such as Costs or Measures.
    4. Add a dimension table for each group of related attributes, and enter a meaningful name, such as Products. Drag and drop related columns from the source table to the appropriate dimension table.
    5. To add more dimension tables, click Add and repeat the previous step.
    6. To delete a dimension table, click X next to the table name.
    7. Specify the join columns for each of the dimension tables. Select the box beside the appropriate columns to mark those columns as join columns.

      If the join column you select is missing from the fact table, a corresponding column gets added automatically to the fact table.

  4. Review fact tables, dimension tables, and join columns. For example:
    • Rename fact and dimension tables.
    • Add or remove columns.
    • Add, delete, or merge dimension tables.
    • Move columns from one dimension table to another.
  5. Click Next.
  6. Review the objects that will be created.
  7. Click Create.
  8. Click Done.

    New fact tables, dimension tables, and joins display in Data Modeler. New views display in the Database pane.

Create Fact Tables Individually

You can add individual source tables containing fact data to your data model.

If you have distinct source tables with fact data, such as in a star source, then you can add them to your data model individually. For example, if you have a source table that contains only revenue measures, then you can use this method to create the corresponding fact table.

Alternatively, you might have sources with fact information spread across multiple tables, such as normalized transactional sources. In this case, create source views first to combine tables in a way that resembles a star model. For information about creating views, see Add Your Own Source Views. For information about modeling different source types, see Plan a Data Model.

Tip:

Create source views as a base for model objects when you think you might want to perform subsequent changes like extending model objects, creating filters, and adding pre-aggregation calculations. Creating a fact table based on source views provides greater flexibility than using source tables directly.

When you use this method to create individual fact tables, all columns in the source table or view are assigned to a single fact table and if the source has relationships with other tables or views, we'll offer to add them to your model.

After locking the model, perform one of the following actions to create fact tables individually:

  • Drag the source table or view from the Database menu in the left pane to the Fact Tables area of the Data Model.
  • From the Database menu in the left pane, right-click the table or view, then click Add to Model, then Add as Fact Table.
  • From the Database menu in the left pane, click Table Actions or View Actions, click Add to Model, then Add as Fact Table.
  • From the Database Table or View editor for a particular source table or view, click Add to Model, then Add as Fact Table.
  • In the right pane, click Add in the Fact Tables area of the Data Model. Then, select one or more source tables and views from the Database Objects list and click OK.
  • To copy an existing fact table, click Fact Table Actions for the fact table you want to copy, and then click Duplicate.

After adding the source table or view to the model, you can edit the fact table.

Create Dimension Tables Individually

You can add individual source tables containing dimension data to your data model.

If you have distinct dimensional source tables, such as in a star source, then you can add them to your data model individually. For example, if you have a source table that contains only customer attributes, then you can use this method to create the corresponding dimension table.

Alternatively, for snowflake or normalized (transactional) sources, create source views to combine source objects in a way that resembles a star model. For information about creating views, see Add Your Own Source Views. For information about modeling different source types, see Plan a Data Model.

Tip:

Create source views as a base for model objects when you think you might want to perform subsequent changes like extending model objects, creating filters, and adding pre-aggregation calculations. Creating a dimension table based on source views provides greater flexibility than using source tables directly.

When you use this method to create individual dimension tables, all columns in the source table or view are assigned to a single dimension table and if the source has relationships with other tables or views, we'll offer to add them to your model.

After locking the model, perform one of the following actions to create dimension tables individually:

  • Drag the table or view from the Database menu in the left pane to the Dimension Tables area of the Data Model.
  • From the Database menu in the left pane, right-click the table or view, click Add to Model, and then select Add as Dimension Table.
  • From the Database menu in the left pane, click Table Actions or View Actions for a table or view, click Add to Model, and then select Add as Dimension Table.
  • Click Add in the Dimension Tables area, and then select Add Database Tables. From the Database Objects list, select one or more sources and then click OK.
  • From the Database Table or View editor for a particular source table or view, click Add to Model and then select Add as Dimension Table.
  • To copy an existing dimension table, click Dimension Table Actions for the dimension table you want to copy, and then click Duplicate.

After adding the source table or view to the model, you can edit the dimension table.

Edit Fact Tables and Dimension Tables

You can edit properties of fact and dimension tables in your data model and preview the source data.

  1. In Data Modeler, lock the model for editing.
  2. Click the fact table or dimension table that you want to edit.
  3. Change settings on the Overview tab as needed:
    Field or Element Description

    Time dimension

    For dimension tables only. Specifies that hierarchies for this dimension table support a time dimension.

    Enable skipped levels and Enable unbalanced hierarchies

    For dimension tables only. Set properties for hierarchies associated with this dimension table.

    Column list

    Click the link for a column to edit that column in the Column editor. Or, right-click the row for the column and click Edit.

    Aggregation

    For fact tables only. Click to select a type of aggregation for the column from the list, or select Set Aggregation from the Column Actions menu. Aggregation types include:

    None: Applies no aggregation.

    Sum: Calculates the sum by adding up all values.

    Average: Calculates the mean value.

    Median: Calculates the middle value.

    Count: Calculates the number of rows that aren’t null.

    Count Distinct: Calculates the number of rows that aren't null. Each distinct occurrence of a row is counted only once.

    Maximum: Calculates the highest numeric value.

    Minimum: Calculates the lowest numeric value.

    First: Selects the first occurrence of the item.

    Last: Selects the last occurrence of the item.

    Standard Deviation: Calculates the standard deviation to show the level of variation from the average.

    Standard Deviation (all values): Calculates the standard deviation using the formula for population variance and standard deviation.

    Tip:

    Some calculated measures show Pre-Aggregated for aggregation. These measures have calculations involving measures that already have an aggregation applied. To edit a calculation that contains pre-aggregated measures, click the column name.

    Available

    Click to mark a column as Available or Unavailable to choose whether that column is displayed in analyses that are created. You can also select Mark as Unavailable or Mark as Available from the Column Actions menu.

    Edit All

    You can click to edit properties for individual columns in the table, or select Edit All to edit all rows at once.

    Add Column

    Click Add Column to display the Column editor and create a new column.

  4. From the Source Data tab, you can preview the first 25 rows of source data for the table. Resize the columns in the display table if needed. Click Get Row Count to retrieve a complete row count for the table or view.
  5. For dimension tables only: from the Hierarchies tab, edit the hierarchies and levels for the table.
  6. From the Permissions tab, specify object permissions.
  7. From the Data Filters tab, you can define data filters that provide row-level filtering for data model objects. See Secure Access to Data.
  8. Click Done to return to the data model.

Add More Columns to Fact and Dimension Tables

There are different ways to add more source columns to fact and dimension tables in your model.

Add Columns from Another Source to a Dimension Table

You can add the columns from another source table or view to an existing dimension table. For example, you may want to include attributes from a Product Category table in your Products dimension table.

  1. In Data Modeler, lock the model for editing.
  2. Select the dimension table you want to edit so its Overview tab displays.
  3. Drag and drop the source table or view that contains the columns you want to add from the Database pane to the dimension table (columns area).
    Alternatively, right-click the dimension table you want to edit, click Add Columns, and then select the source table or view that contains the columns you want to add.
  4. Select appropriate join columns and click OK.
View the dimension table to see the additional columns. The Source property shows that the dimension table is based on a new database view. Data Modeler creates a new database view whenever you add columns from another source.