As discussed, the below tech note was the classy example of working with fact extensions in MicroStrategy & that would pave an idea for you to know when and where fact extensions is required.
In MicroStrategy Architect 8.x, it is possible to extend a fact definition beyond the level at which its data is stored. This can be done by using a fact extension.
There are three types of fact extensions:
NOTE: Always Remember to Update Schema after making any changes to the users’ facts.
Degradations are designed to allow users to lower the level at which a Fact’s data is stored.
Users determine Units Received every quarter. Therefore, only quarterly information is related to it. Suppose, however, that certain metrics use the Units Received fact to derive information on a monthly level. It, then, becomes necessary to estimate the average value of the inventory per month.
This can be executed by following the steps below:
- Open the Fact Editor
- Click on the ‘Extensions’ Tab
- Select ‘New’ and at the second screen in the Fact Extension Wizard, select ‘Lower the fact entry level’
- Select the attribute level to which to extend the fact definition. In this case month is chosen.
- Select the attribute with which to connect to this level; in this case quarter is chosen because there is information for the fact based on quarter, and there is a connection between month and quarter.
- Select whether to connect only to this attribute or to the entire hierarchy below the attribute. In this case, the attribute is chosen as month is a child of quarter.
- Set up a relationship by which the fact will be extended. In this case, as there are three months in one quarter, the obvious relationship is to divide Units Received by three.
An Extension is designed to extend the level at which the information is stored in a fact, to a higher level. For instance, imagine that one has Unit Cost stored at levels of Product and Time, but not Geography. Now suppose, that a report requires this information to be produced on a report at the geography level.
- Start by opening the Fact editor.
- Select the second option: ‘Extend the fact entry level.’ In this case, extend this to Employee, as that is the lowest level of the Geography hierarchy.
- Select the third option, as there is no direct correlation between these levels.
As is indicated, this is usually not a preferable method, as this is an inefficient cross product, which results in a Cartesian Join. In general, the other two options should be used.
For the first option, users are prompted to select a relationship table, that can be used to extend the level.
In the second option, users are directed to specify a fact table. These are the three ways in which one can extend the fact to a higher level. All three give the users the option of specifying an allocation level, as above (i.e., an equation by which this relation is constructed).
A Disallow prevents the information in a fact from being extended to the level used.
Once this is selected, users are directed to select the level at which the information will be made unavailable.
In this case, Customer; the fact information will no longer be available at that level. This would be useful, if users wished to see information at all levels, except for at the customer level, in order to keep the customer’s privacy intact.