Defining a join on fact tables using fact relations

Fact extensions can be defined by a fact relation instead of a table relation. With a fact relation, the table join is possible on any table that contains the fact. This allows more flexibility in defining the relations, since the MicroStrategy Engine is responsible for choosing the appropriate table to join, rather than you having to select tables manually.

The following diagram shows the schema from the example in Defining a join on fact tables using table relations after two summary tables are added to it.

To extend the entry level of the Freight fact to Customer, you can create a fact relation using the Order Unit Sales fact.

The MicroStrategy Engine tries to join a table containing Freight to a table containing Order Unit Sales. The engine can make the following joins, depending on the join attributes specified:

Table 1 and Table 2 on Distribution Center, and Order
Table 1 and Table 4 on Distribution Center
Table 2 and Table 3 on Distribution Center
Table 3 and Table 4 on Distribution Center

The joins described above demonstrate how the join attributes can be either Distribution Center and Order or just Distribution Center.

You can define the fact relation in the Level Extension Wizard which you can access from the Fact Editor. Open the Order Unit Sales fact and extend it to either Distribution Center and Order or just Distribution Center. Next, select the Select the relationship table dynamically option and specify the tables to use for the extension. This option is set in the step immediately after Defining a join on fact tables using table relations in the procedure above. The tables and attributes you specify in the wizard determine the different types of joins that are created, as explained above.

The SQL generated for a report containing Distribution Center, Customer, and Freight is shown below, if the only join attribute is Distribution Center.

select a1.DIST_CENTER, a2.CUSTOMER,
	sum(a1.Freight)
from TABLE3 a1, TABLE4 a2
where a1.DIST_CENTER = a2.DIST_CENTER
group by a1.DIST_CENTER, a2.CUSTOMER

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

As with table relations, you can specify the best fit as the join strategy so that the engine calculates the joins. In a best fit join, the set of join attributes must contain the entire key of the left-hand-side fact table (Table 3 in the example SQL above).