Using the table relation method for a fact extension forces the SQL Engine to always join the desired attribute and fact using a particular table. Creating a fact extension using a table relation consists of the following steps:
1 Select the attribute level to which you want to extend the fact.
2 Select the table you want the SQL Engine to use to join the fact to the attribute to which you want to extend the fact.
3 Select the attribute or set of attributes the SQL Engine can use to join the fact to the attribute to which you want to extend the fact.
4 Determine the join direction between the join attributes and the fact.
5 If necessary, define an allocation expression.
The following topics describe each of these steps in more detail.
Selecting the Attribute for the Fact Extension
When you create a fact extension, the fact is completely unrelated to any attributes in the given hierarchy. The attribute to which you want to extend the fact depends on how you want to analyze the fact. If you want to report on the fact at any level in the hierarchy, you should select the lowest-level attribute in that hierarchy.
For the Freight fact extension, if you want to report on the Freight fact at any attribute level in the Product hierarchy, you select the Item attribute, which is the lowest-level attribute in the hierarchy. Selecting a higher-level attribute from the Product hierarchy, such as Subcategory, only extends the fact to that attribute level or any attribute above it in the hierarchy. Extending the Freight fact to the item level enables you to create reports that analyze freight data using any attribute from the Product hierarchy.
Selecting the Table for the Join
The SQL Engine needs to join the table that contains the fact you are extending and the lookup table that stores the attribute to which you are extending the fact. Because these two tables are not related, you have to select another data warehouse table to serve as a relationship table between the fact and lookup tables.
After you select the attribute to which you want to extend the fact,
MicroStrategy Architect searches the project warehouse catalog and returns a list of all tables that contain the ID column of that attribute. Using this list of candidate tables, you can then select the optimal table for the join. In selecting a table, you should consider several factors, including the number of possible join paths, the optimal join path for a given allocation expression, and any other characteristics specific to your data warehouse environment. For example, you may want to use a table for the join that you know has better indexes or is updated more frequently.
In the previous example, the Freight fact is stored in the ORDER_FACT table.
The following image shows the logical view for the ORDER_FACT table:
ORDER_FACT Table—Logical View
Notice that several attributes from multiple hierarchies map to the
ORDER_FACT table. Therefore, all these attributes relate to the Freight fact and could possibly be used to relate the Freight fact to the Item attribute.
The ORDER_FACT table is the only table in the data warehouse that contains the Freight fact. Therefore, you have to join the ORDER_FACT table to the LU_ITEM table to relate the Freight fact to the Itemattribute.
When you extend the Freight fact to Item using a table relation, MicroStrategy Architect returns the following list of candidate tables:
List of Candidate Tables
The list of candidate tables includes the LU_ITEM and REL_CAT_ITEM tables. These lookup and relationship tables contain only product-related information. Therefore, you can eliminate them as possible tables to use for the join since no attributes from the Product hierarchy are related to the Freight fact. In general, you can usually eliminate tables from the hierarchy to which the attribute belongs since these tables typically do not provide any way to join to other hierarchies.
The remaining tables are all fact tables that contain the Item attribute.
However, most of them have only one or two attributes in common with the ORDER_FACT table. However, the ORDER_DETAIL table contains many of the same attributes as the ORDER_FACT table, including Employee, Day, Customer, and Order. The following image shows the logical view for the ORDER_DETAIL table:
ORDER_DETAIL Table—Logical View
You could use the ORDER_DETAIL table to join the LU_ITEM and
ORDER_FACT tables using any of the common attribute columns. Because the ORDER_DETAIL table provides multiple join paths, it is the best table to use to join the Freight fact to the Item attribute.
The ORDER_DETAIL table is the optimal join table provided you can use it in conjunction with the allocation expression for the factextension. You also have to consider any characteristics of the table that might render it less optimal because of factors unrelated to its structure.
For example, if this table is only updated monthly and you want reports that provide the most current data, it would not be the best table to use for the join.
For the Freight fact extension, you select Order as the join attribute. You could use other attributes in the ORDER_FACT table as the join attribute. These attributes are listed in the Level Extension Wizard, as shown below:
Possible Join Attributes
However, the allocation expression for this fact extension uses facts that are related to individual orders, so Order is the optimal join attribute.
Determining the Join Direction
If you allow the SQL Engine to dynamically select the join attributes, you do not perform this step. However, if you manually select the join attributes, you have to determine how you want the SQL Engine to perform the join between the join attributes and the fact. Just as with fact degradations, there are two possible join directions. You can join to the fact using only the join attributes themselves, or you can allow the fact to also join to the children of the join attributes.
If you allow the SQL Engine to join against the join attributes and any of their children, you need to ensure that the allocation expression you use for the fact extension returns values that are valid at any of those attribute levels.
For the Freight fact extension, you allow the SQL Engine to join only against the Order attribute itself. Since the Order attribute is already the lowest-level attribute in the Customers hierarchy, it does not have any child attributes you could use to join to the fact.
Defining an Allocation Expression
Just as with fact degradations, when you create a fact extension, you may need to define an expression to allocate the fact data at the extended attribute level.
Some facts are static and do not change value from one attribute level to another, while other facts have values that do change, depending on the attribute level.
As with fact degradations, a valid allocation expression can include attributes, facts, constants, and any standard expression syntax, including mathematical operators, pass-through functions, and so forth.
For the Freight fact extension, you could create the following allocation expression:
(Freight * [Item-level Units Sold]) / [Order-level Units Sold]
In the allocation expression, the value of the Freight fact at the order level is proportionally distributed among items sold in this particular order according to the units sold. If there are 3 units of the same item in an order of 10 items total and the freight for this order was $100, then the item-level freight for that particular item is $30.