The Building Blocks of Business Data: Facts » Modifying the levels at which facts are reported: Level extensions » Forcing facts to relate to attributes: Using cross product joins

Forcing facts to relate to attributes: Using cross product joins

You can use a cross product join when a join does not exist and you need to force a fact to relate to an attribute by extending the fact. The cross product join is an extension that allows a single fact value to relate to all elements of an unrelated attribute. This method can produce incorrect data because data can be repeated and counted twice in some cases.

Cross products should only be used when no other way to extend the fact exists. When you specify a cross product join to relate a fact to an attribute, you are creating a Cartesian product of the lookup attribute. Since this method can be inefficient, MicroStrategy does not recommend using the cross product join.

For example, in the following schema, Distribution Center does not relate to Dollar Sales:

To report Dollar Sales by Distribution Center, a cross product join must be used.

You can define this cross product join in the Level Extension Wizard in the Fact Editor. Open the Dollar Sales fact and extend it to the Distribution Center attribute. Next, select the Perform the extension through a cross product option. This option is set in the step immediately after Defining a join on fact tables using table relations of the procedure above. For this example, you do not need to specify an allocation expression.

Notice that no join attributes are specified. The MicroStrategy Engine always cross-joins the lookup tables of the attributes in the extension.

The SQL generated for a report containing Customer, Distribution Center, and Dollar Sales is:

select a1.DIST_CENTER, a2.CUSTOMER,
	sum(a2.DOLLAR_SALES)
from TABLE1 a1, TABLE2 a2
group by a1.DIST_CENTER

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