The Building Blocks of Business Data: Facts » How facts are defined » Mapping physical columns to facts: Fact expressions

Mapping physical columns to facts: Fact expressions

A fact expression maps facts to physical columns in the warehouse. These expressions can be as simple as a fact column name from the warehouse or as sophisticated as a formula containing multiple fact column names and numeric constants. Regardless of how it is defined, a fact expression represents a mapping to specific fact information in the warehouse. A fact definition must have one or more fact expressions.

The following image illustrates a column in the fact table and the associated fact expressions:

Valid fact expressions are formulas constructed from fact columns with or without numeric constants or mathematical operators. The mathematical operators that can be used in a fact expression are:

Addition (+)
Subtraction (-)
Multiplication (*)
Division (/)

You can use the Fact Editor to create fact expressions. These steps are covered in Creating and modifying simple and advanced facts.

A fact can be defined using an ApplySimple function. Apply functions are discussed in the Pass-Through Expressions appendix in the Advanced Reporting Guide.

Most facts represent physical columns in the data warehouse. However, some facts do not exist at all in the warehouse and are defined in other ways, as explained in the following sections.

Implicit facts and implicit fact expressions

Implicit facts are virtual or constant facts that do not physically exist in the database. An implicit fact indicates a fact table from which to retrieve data. The implicit fact can have its expression defined as a constant value, although nothing is saved in a table column.

For example, you can use implicit fact expressions to create “temporary columns” in the database with a value of “1” for every row. These temporary columns allow you to keep track of how many rows are returned for a certain attribute. You may also find it helpful to use implicit facts when building metrics, where you can sum the column holding the constant to create a COUNT. For example, if you want to build a metric defined as Sum(1), you can define a fact equal to the constant “1.” For detailed information about metrics, see the Advanced Reporting Guide.

Derived facts and derived fact expressions

A derived fact has its value determined by an expression that contains more than just a column in a table. Any operation on a column such as adding a constant, adding another column’s values, or setting the expression to be an absolute value, creates a derived fact. In other words, you are creating a fact from information that is available in the data warehouse. For example, a table in your data warehouse contains the following elements:

You can create a new fact, Sales, by creating the following derived fact:

Sales = Quantity_Sold * Price

One advantage of creating a derived fact is that a derived fact allows one consistent fact to exist in the project in lieu of having to retrieve multiple intermediary facts from multiple tables. Using a single fact saves storage space and limits the number of SQL passes used in queries.

Rather than creating a derived fact, you can create such analysis in MicroStrategy with the use of metrics. Metrics allow you to perform calculations and aggregations on your fact data. For more information on what metrics are and how to create them, see the Advanced Reporting Guide.

Example: creating derived facts

The Cost fact in the MicroStrategy Tutorial contains the derived fact expression Qty_Sold * Unit_Cost. This expression implies that columns containing data about the quantity of items sold and the price of those units can be multiplied to produce a useful business calculation. In this case, the columns are used to answer the business question, “How much did it cost the company to create the items purchased by customers?”

The following procedure describes how to create a derived fact that uses the derived fact expression described above. You can also created derived facts that use derived fact expressions using Architect, which is described in Creating and modifying multiple facts.

To create a derived fact

1 In MicroStrategy Developer, log in to the MicroStrategy Tutorial project.
2 Navigate to the My Personal Objects folder, and open the My Objects folder.
3 From the File menu, point to New, and then select Fact. The Fact Editor opens, with the Create New Fact Expression dialog box displayed on top of it.
4 From the Source table drop-down list, select the ORDER_DETAIL table.
5 From the Available columns pane, double-click the QTY_SOLD column to add it to the Fact expression pane on the right.

To complete the derived fact expression

A derived fact expression includes a combination of columns, numerical constants, and mathematical operators. The steps below continue the example scenario to provide a guideline of how to create derived fact expressions.

6 With the cursor in the Fact expression pane, click * (multiplication operator) to add it to the expression.
7 From the Available columns pane, double-click the UNIT_PRICE column to add it to end of the fact expression.
8 Under Mapping method, select Automatic.
9 Click Validate to check whether the syntax of the expression is correct. The expression should appear as shown below:

10 Click OK. The derived fact expression appears in the Fact expression pane in the Fact Editor.
11 From the File menu, select Save As. The Save As dialog box opens.
12 Enter a name for the derived fact and click Save.
13 When you create a fact for your project, at this point, you must update the project schema. However, since this is only an example, it is not necessary to update the schema.

Facts with varying column names: Heterogeneous column names

In your warehouse, the same fact data can be included in columns with different column names. In the example below, two fact tables in a warehouse each contain columns for dollar sales. Table 1 contains a fact called Dollar_Sales. Table 2 includes a fact called Dollar_Sls. These two items represent the same information.

MicroStrategy allows you to identify heterogeneous fact column names for each fact. With heterogeneous column names, you can refer the same fact to multiple columns with different column names and from different tables that identify the same quantitative value.

In the example above, creating a heterogeneous fact column name for dollar sales informs the system that the Dollar_Sales and Dollar_Sls columns represent the same fact. When you call for the information in a report through the use of a metric, both fact columns are used in the SQL, resulting in an accurate representation of the fact in the report.

Example: Mapping heterogeneous fact columns

The Units Sold fact in MicroStrategy Tutorial consists of two fact columns in the warehouse, Qty_Sold and Tot_Unit_Sales. Although these fact columns have different names and exist in different fact tables, they represent the same data and are therefore both mapped to the Unit Sold fact.

You must map heterogeneous fact columns to their corresponding facts to ensure that accurate and complete data is displayed on reports.

The following procedure describes how to create the Units Sold fact that already exists in MicroStrategy Tutorial. In the procedure, you create the Units Sold fact and map its corresponding heterogeneous fact columns to it. You can also use Architect to create a fact with heterogeneous column names, which is described in Creating and modifying multiple facts.

To create a fact with heterogeneous column names

1 In MicroStrategy Developer, log in to the MicroStrategy Tutorial project.
2 Navigate to the My Personal Objects folder, and open the My Objects folder.
3 From the File menu, point to New, and then select Fact. The Fact Editor opens, with the Create New Fact Expression dialog box displayed on top of it.
4 From the Source table drop-down list, select the ORDER_FACT table. This is one of the tables in which a heterogeneous fact column for the Units Sold fact exists.
5 From the Available columns pane, double-click the QTY_SOLD column to add it to the Fact expression pane on the right.
6 In the Mapping method area, select Automatic.
7 Click OK. The Fact Editor opens and the fact expression you just created appears in the Fact expression pane.

Now you must add the other heterogeneous fact column as separate expression for the Units Sold fact.

8 Click New. The Create New Fact Expression dialog box opens.
9 From the Source table drop-down list, select the CITY_CTR_SALES table. This is the other table in which a heterogeneous fact column for the Units Sold fact exists.
10 From the Available columns pane, double-click the TOT_UNIT_SALES column to add it to the Fact expression pane on the right.
11 In the Mapping method area, select Automatic.
12 Click OK. The Fact Editor opens and the fact expression you just created appears in the Fact expression pane. Now the Units Sold fact you are creating maps correctly to its heterogeneous fact columns.
13 From the File menu, select Save As. The Save As dialog box opens.
14 Enter a name for the new fact and click Save.
15 When you create a fact for your project, at this point, you must update the project schema. However, since this is only an example, it is not necessary to update the schema.