Fact Extensions - Crazy Automator

Facts are the objects which store actual data values in the data warehouse at a specified business level in MicroStrategy. Whenever the fact definitions need to be extended or changed beyond its warehouse level, there come the roles of Fact Extensions. So in this article, we are going to see brief explanations on Fact Extensions.

Features:

It is needed when a fact has no relation to an attribute in a report.
Restrict the fact level to limit the data to the user.
It is useful in changing the fact to a different level in a completely same or different hierarchy.

Types:

Degradations
Extensions
Disallows

Degradations:

Degradations is used when the fact data stored at a higher level in a hierarchy, and the user requires at the lower level from the same hierarchy.

For example, the data warehouse stores only monthly information on sales, but any reports require the sales generated fact to derive information on a daily level. Then it becomes necessary to estimate the average value of the sales per day using this property.

In the fact table, the data stores in the Month level. Hence by fact extension, day level data are retrieved by an expression(month/30).

SQL of the report after fact degradation:

select	a11.MONTH_ID  MONTH_ID,
	max(a12.MONTH_DESC)  MONTH_DESC,
	a12.DATE_ID  DATE_ID,
	max(a12.DATE_DESC)  DATE_DESC,
	sum((a11.SALES / 30))
from	SALES_FACT_MONTH	a11
	join	DATE_TABLE	a12
	  on 	(a11.MONTH_ID = a12.MONTH_ID)
group by	a11.MONTH_ID,
	a12.DATE_ID

Extensions:

Extensions are required to extend the level of fact data to another hierarchy by a relationship table or cross join.

For example, if sales are stored in the data warehouse at Product and Customer levels, but not demographic.

Now suppose if a report requires the information at the customer income group (demographic level) that is of different hierarchy, then the fact has to be extended to that level.

SQL of the report after fact extension:

select	a12.DEMOGRAPHICS_ID  DEMOGRAPHICS_ID,
	a11.CUST_ID  CUST_ID,
	max(a13.CUST_INCOME_GROUP)  CUST_INCOME_GROUP,
	sum(a11.SALES)
from	SALES_FACT	a11
	join	CUST_DEMO_REL	a12
	  on 	(a11.CUST_ID = a12.CUST_ID)
	join	DEMOGRAPHICS	a13
	  on 	(a12.DEMOGRAPHICS_ID = a13.DEMOGRAPHICS_ID)
group by	a12.DEMOGRAPHICS_ID,
	a11.CUST_ID,
	a12.DEMOGRAPHICS_ID

Disallows:

This property restricts the data in the fact of being extended to the specific level used in the report.

For example, monthly level information will not be available to the report user. Hence the fact shows the information at all levels except monthly data.