Defining a join on fact tables using table relations

A table relation defines a join on tables. When you specify a table to join with a fact, you are creating a table relation to extend a fact. A fact extension can be used to relate a fact to an attribute using a fact table. The join is important as the table contains an attribute in the entry level and the attribute to which to extend.

For example, the MicroStrategy Tutorial project includes a Freight metric. This metric has a table relation fact extension to the Item attribute. Since the ORDER_FACT table that defines Freight does not include the identity column for the Item attribute, the Freight fact cannot be reported at the Item level. A fact extension is required to view freight values for each item included in an order. In this example, the ORDER_DETAIL table is used to create the Freight fact extension to Item because:

1 The ORDER_FACT and ORDER_DETAIL tables both contain the Order attribute’s identity column to join the tables, and ORDER_DETAIL contains the Item attribute’s identity column to extend the fact to Item.
2 The Freight fact cannot simply be joined with a table containing Item information to return a meaningful freight value for each item. An allocation expression is required to extend Freight to the Item level. Notice that the ORDER_FACT and ORDER_DETAIL tables include Order-level Units Sold and Item-level Units Sold columns respectively. These two columns are used to allocate the fact expression in the procedure below.

The following procedure steps through how to create the fact extension that has been created for the Freight fact of the Tutorial project. The procedure also describes general principles of creating fact extensions which you can use to create fact extensions for the facts in your project.

To define a fact extension with a table relation

1 In Developer, log in to the MicroStrategy Tutorial project.
2 Browse to the Facts folder and double-click the Freight fact to edit it. If a message is displayed asking if you want to use read only mode or edit mode, select Edit and click OK to open the Fact Editor in edit mode so that you can make changes to the fact. The Fact Editor opens.
If you are only given the option of opening the Fact Editor in read only mode, this means another user is modifying the project’s schema. You cannot open the Fact Editor in edit mode until the other user is finished with their changes and the schema is unlocked.
For information on how you can use read only mode and edit mode for various schema editors, see Using read only or edit mode for schema editors.
3 Click the Extensions tab.
4 Select Extension to Item and click Modify. The Level Extension Wizard opens.

To create a new fact extension you would click New. However, this example steps through how the Freight fact extension Extension to Item was created.

5 Read the Welcome statement and click Next. The General Information page opens.

To lower, extend, or disallow the fact entry level

6 Enter a name and a description for your fact extension (already provided). Then select whether you want to:
Lower the fact entry level: define a fact degradation (see Lowering the level of fact data: Fact degradations)
Extend the fact entry level: define a fact extension on a table relation, dynamic fact relation, or a cross product join
Disallow partially or completely the fact entry level: define a fact extension that does not allow a fact to be reported at a certain level (see Disallowing the reporting of a fact at a certain level)

For this example you are creating a fact extension on a table relation, so select Extend the fact entry level, and click Next. The Extended Attributes page opens.

To select attributes to extend the fact to

7 Select the attributes you want to extend the fact to, allowing the fact to be reported at the new level. For this example Item is already selected. Click Next. The Extension Type page opens.

To extend the fact so that it can be reported at any level in a hierarchy, choose the lowest level attribute in that hierarchy.

To select the type of fact extension

8 Select how you want to extend the fact:
Specify the relationship table used to extend the fact: select a relationship table and join attributes.
Select the relationship table dynamically: select a fact and join attributes. This allows the MicroStrategy Engine to select the table that includes the fact and join attributes you choose to create the fact extension (see Defining a join on fact tables using fact relations).
Perform the extension through a cross product: select to apply a cross product join (see Forcing facts to relate to attributes: Using cross product joins).

For this example select Specify the relationship table used to extend the fact, and click Next to continue defining your fact extension on a table relation. The Table Selection page opens.

To select the table, join attributes, and define the allocation expression

9 Select the table used to extend the fact to the new level. For this example, the ORDER_DETAIL table is already selected. Click Next. The Join Type page opens.
10 Select whether to allow Intelligence Server to dynamically select what attributes to perform the join, or manually select the attributes. Since you know that you want to join the ORDER_FACT and ORDER_DETAIL tables using the Order attribute, select Order and click Next. The Join Attributes Direction page opens.
11 You can choose to join using the attribute, or join using the attribute and its children. In this case Order has no children, so you do not have to click the Join against arrow to change the default. Click Next. The Allocation page opens.
12 Enter an allocation expression that calculates the fact at the new level. For this example, the allocation expression is already provided, ((Freight * [Item-level Units Sold]) / [Order-level Units Sold]).

Take a moment to review the allocation expression. Notice that the expression returns an average freight amount per item of an order. Therefore, the extension of Freight provides an estimate of the freight for each item of an order, not an exact calculation. A more detailed description of why this occurs follows this procedure.

13 Click Finish to create the fact extension.

When the engine processes a report containing Order, Item, and Freight, it joins ORDER_FACT and ORDER_DETAIL and considers the resulting table as one logical fact table at the Item, Day, Order, Employee, Promotion level. The SQL generated for the report containing Order, Item, and Freight (metric mapped to the Freight fact) is:

select a11.[ORDER_ID] AS ORDER_ID,
	max(a11.[ORDER_DATE]) AS ORDER_DATE,
	a12.[ITEM_ID] AS ITEM_ID,
	max(a13.[ITEM_NAME]) AS ITEM_NAME,
	sum(((a11.[FREIGHT] * a12.[QTY_SOLD]) /
	a11.[QTY_SOLD])) AS WJXBFS1
from [ORDER_FACT] a11, [ORDER_DETAIL] a12,
	[LU_ITEM] a13
where a11.[ORDER_ID] = a12.[ORDER_ID] and 
	a12.[ITEM_ID] = a13.[ITEM_ID]
group by a11.[ORDER_ID], a12.[ITEM_ID]

The SQL statement above is for an Access database. The SQL for your reports may vary depending on the type of DBMS that you use.

To view how the fact extension is an estimation of freight values for each item of an order, review the values of the first order with an extra metric that calculates the number of each item type in an order shown below.

Notice that the Freight metric averages the amount of freight per item in an order. The larger freight values occur because more than one of the item type was included in the order. This illustrates how fact extensions often provide an estimation of values at a different level rather than an exact calculation. If you want to provide exact values of data at a certain level, you most likely need to capture such data and store it in your data source.