The Building Blocks of Business Data: Facts » Modifying the levels at which facts are reported: Level extensions

Modifying the levels at which facts are reported: Level extensions

Facts are stored at a particular business level in the warehouse. The level of a fact is defined by the attribute IDs present in the table. For example, the fact table shown below contains several attribute IDs, including Item and Quarter. These attribute IDs imply that the fact is reported at the item and quarter levels by default.

Level extensions are necessary when facts are stored in the data warehouse at one level and reported at different levels. Every fact is tied to a set of attributes that may or may not satisfy all users’ reporting requirements. A fact extension is needed when a fact does not relate directly or indirectly to an attribute included on a report.

If the entry level of a fact is at the lowest level of a hierarchy, all attributes at a higher logical level in the hierarchy are available for use as well, without the use of level extensions. For example if you have a cost fact at the level of a date attribute in a time hierarchy, MicroStrategy can aggregate the cost fact data to the level of the year attribute because it is in the same hierarchy as the date attribute and at a higher level. However, facts require level extensions to be related to any attributes that are at a lower logical level in the same hierarchy than the entry level for a fact (see Lowering the level of fact data: Fact degradations).

You can use level extensions to change a fact level and extend a fact level to a level in a completely different hierarchy. For example, you record a Discount fact at the Item/Date level. That is, discounts apply to particular items on particular days. To see if some call centers are selling significantly more items at a discount than other call centers, you have to extend the level of the Discount fact to the Call Center level, which is an attribute from a different hierarchy.

Level extensions define how facts can be extended, lowered, or disallowed to other attributes across the schema. By creating a level extension, you are allowing facts or attributes that have been captured at one level to be extended to other levels to meet reporting requirements.

Level extensions are not required like the fact definition and column alias, and they tend to be used only in specific cases.

Before a metric containing a fact can be used with an attribute that is not in or related to the attribute’s entry level, a level extension must be defined for the fact. This is because if a fact is stored at a level unrelated to an attribute on a report, a level extension must exist to relate the fact data to the attribute. Otherwise, there is no way to make a connection between the fact data and the attribute.

You can create fact level extensions by using any of the following methods:

Defining a join on fact tables using table relations
Defining a join on fact tables using fact relations
Forcing facts to relate to attributes: Using cross product joins
Lowering the level of fact data: Fact degradations
Disallowing the reporting of a fact at a certain level

You can find complete descriptions for each of these methods in the online help for the Level Extension Wizard in the Fact Editor.

You can use the Fact Editor to create level extensions.