Fact tables: Fact data and levels of aggregation

Fact tables are used to store fact data. Since attributes provide context for fact values, both fact columns and attribute ID columns are included in fact tables. Facts help to link indirectly related attributes. The attribute ID columns included in a fact table represent the level at which the facts in that table are stored.

For example, fact tables containing sales and inventory data look like the tables shown in the following diagram:

For more details on the level of aggregation of your fact data, see Fact table levels: The context of your data.

Base fact columns versus derived fact columns

The types of fact columns are base fact columns and derived fact columns:

• Base fact columns are represented by a single column in a fact table. The following diagram shows an example of a fact table and base fact columns:

• Derived fact columns are created through a mathematical combination of other existing fact columns. The following diagram shows an example of a fact table and how you can create a derived fact column from base fact columns:

In the example, the derived fact Tot_Dollar_Sales is created using the Qty_Sold, Unit_Price, and Discount fact columns. Also, the derived fact exists in several tables, including Item_Mnth_Sls and City_Ctr_Sls.

Because facts in different fact tables are typically stored at different levels, derived fact columns can only contain fact columns from the same fact table.

There are advantages and disadvantages to consider when deciding if you should create a derived fact column. The advantage of storing derived fact columns in the warehouse is that the calculation of data is previously performed and stored separately, which translates into simpler SQL and a speedier query at report run time. The disadvantage is that derived fact columns require more storage space and more time during the ETL process.

You can create the same type of data analysis in MicroStrategy with the use of metrics. Metrics allow you to perform calculations and aggregations on fact data from one or more fact columns. For more information on what metrics are and how to create them, see the Advanced Reporting Help.

For more information on the different types of facts in MicroStrategy and how they are defined, see How facts are defined .

Fact table levels: The context of your data

Facts and fact tables have an associated level based on the attribute ID columns included in the fact table. For example, the following image shows two facts with an Item/Day/Call Center level.

The Item_id, Day_id, and Call_Ctr_id columns in the table above represent practical levels at which sales and inventory data can be analyzed on a report. The Sales and Inventory facts can be analyzed at the item, day, and call center levels because those levels exist as ID columns in the fact table.

You do not need to include more lookup column IDs than are necessary for a given fact table. For example, notice that the table above does not include the Customer_id column; this is because analyzing inventory data at the customer level does not result in a practical business calculation. Fact tables should only include attribute ID columns that represent levels at which you intend to analyze the specific fact data.

The levels at which facts are stored become especially important when you begin to have complex queries with multiple facts in multiple tables that are stored at levels different from one another, and when a reporting request involves still a different level. You must be able to support fact reporting at the business levels which users require.