Strategy One

Mapping Physical Columns to Facts: Fact Expressions

A fact expression maps facts to physical columns in the data source. These expressions can be as simple as a fact column name from the data sourceor 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 data source. 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 use the Fact Editor to create fact expressions in Workstation; see Creating and Editing Facts.

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

Most facts represent physical columns in the data source. However, some facts do not exist at all in the data source 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."

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 source. For example, a table in your data source 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 Strategy with the use of metrics. Metrics allow you to perform calculations and aggregations on your fact data.

Example: Creating Derived Facts

The Cost fact in the Tutorial project 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.

Create a Derived Fact

  1. Open the Workstation window with the Navigation pane in smart mode.
  2. Connect to your environment(s).
  3. Ensure that the project schema for your new fact is available for editing. You can unlock the schema in the Schema Editor by clicking the lock icon for the project.
  4. From the File menu, select New Fact. The Fact Editor, labeled New Fact, opens.
  5. Type a descriptive name for the new fact in the Name box.
  6. In the All Tables pane, search for and select the source table for the fact. For this example, select the ORDER_DETAIL table.

  7. From the Available columns pane, double-click or drag and drop a column into the Expression pane. For this example, double-click the QTY_SOLD column.

    8. 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.

  8. With the cursor in the Expression pane, click X (multiplication operator) to add it to the expression.
  9. From the Available columns pane, double-click the UNIT_PRICE column to add it to end of the fact expression.
  10. Click Validate to check whether the syntax of the expression is correct. The expression should display as shown below:

  11. Click Done. The derived fact expression is validated and displays in the pane on the far left.

    Save the fact
  12. Click Save.
  13. Navigate to the folder to save the fact in and click Save.
  14. Close the Fact Editor.

    Update the schema

    Update the schema to load the new fact into the project.

  15. In the Workstation Navigation pane, click Schemas, and then click the Reload Schema icon next to your project.

Facts with Varying Column Names: Heterogeneous Column Names

In your data source, the same fact data can be included in columns with different column names. In the example below, two fact tables in a data source 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.

Strategy 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 the Tutorial project 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 displays on reports.

The following procedure describes how to create the Units Sold fact that already exists in the Tutorial project. In the procedure, you create the Units Sold fact and map its corresponding heterogeneous fact columns to it.

Create a Fact with Heterogeneous Column Names

  1. Open the Workstation window with the Navigation pane in smart mode.
  2. Connect to your environment(s).
  3. Ensure that the project schema for your new fact is available for editing. You can unlock the schema in the Schema Editor by clicking the lock icon for the project.
  4. From the File menu, select New Fact. The Fact Editor, labeled New Fact, opens.
  5. Type a descriptive name for the new fact in the Name box.
  6. In the All Tables pane, search for and select the source table for the fact. For this example, select the ORDER_FACT table.
  7. From the Available columns pane, double-click or drag and drop a column into the Expression pane. For this example, double-click the QTY_SOLD column.
  8. Click Done. The fact expression is validated and displays in the pane on the far left.

  9. Click Add New Expression.

  10. In the All Tables pane, search for and select the source table for the fact. For this example, select the CITY_CTR_SALES table. This is the other table which contains a fact column for the Units Sold fact.

  11. From the Available columns pane, double-click or drag and drop a column into the Expression pane. For this example, double-click the TOT_UNIT_SALES column.

  12. Click Done. The fact expression is validated and displays in the pane on the far left. Now the Units Sold fact you are creating maps correctly to its heterogeneous fact columns.

    Save the fact

  13. Click Save.
  14. Navigate to the folder to save the fact in and click Save.
  15. Close the Fact Editor.

    Update the schema

    Update the schema to load the new fact into the project.

  16. In the Workstation Navigation pane, click Schemas, and then click the Reload Schema icon next to your project.