KB6458: How to create an outer join between two different attributes in MicroStrategy Developer?

SYMPTOM:

This document first explains a particular situation in which an outer join between lookup tables is needed. Then, this document addresses how to workaround this particular issue.

Having the following hierarchy:

Country > Distribution Center > Calling Center >

Where Country is defined as having a one-to-many relationship with Distribution Center. This is, for every Country; there may be one-to-many Distribution Centers.

In the Data Warehouse, there are the following entries:

LU_Country

From analyzing these two tables, the following two countries, Canada and Spain, do not have Distribution Centers.

If a report with Country and Distribution Center on it is executed, users will receive the following results:

Users may want a report that displays all of the Countries and if they do not have a Distribution Center, then it should return a Null value. To achieve this result, an outer join between the two Lookup tables would be needed.

CAUSE:

In MicroStrategy, outer joins are not supported between attributes. These are only needed when the data model contains a ragged/unbalanced hierarchy which is not a supported data model in MicroStrategy.

WORKAROUND:

Since outer joins between two attributes are not supported, users can use four different techniques to workaround this issue.

Workaround 1:

This method consists of creating a fact based on the attribute's column name.

1. Create a fact based on the attribute's column name. In the example above, a new fact would be created for the 'Dist_Ctr'.

1. Update Schema.
2. Create a metric for the fact created in Step 1.

1. Please note that the fact is defined as Max(Dist Ctr Name). This means that only one Distribution Center is available for this metric.
2. Create a report with the attributes Country and Distribution Center.
3. Go to Data > VLDB Properties > Joins > Preserve all lookup table elements and set this to 'Preserve lookup table elements joined to final pass result table on template attributes with filter.

Run the report. The result set should now look as show below

The Null values now appear with their respected parent attribute. In the screenshot above, each Country has its respected Distribution Center. However, if there is no Distribution Center, then a Null value is present.

1. Insert Null values in the database. The table for LU_DIST_CTR has the following values:

1. This table can be modified so that it accounts for all of the Null values. In the table below, two new Null values were added.

   Note: Note in this table that the Null values are added for the description column only. The Null rows must have legitimate, real values for the Dist_Ctr_ID column. Attribute ID columns should never contain null values.

   Dist_Ctr_Id	Dist_Ctr_Name	Country_Id
   1	London	3
   1	Paris	4
   1	Berlin	5
   2	Seattle	1
   3	Washington, DC	1
   4	San Diego	1
   5	Miami	1
   6	Boston	1
   8	New York	1
   9	Salt Lake City	1
   10	Atlanta	1
   11	San Francisco	1
   12	New Orleans	1
   13	Memphis	1
   14	Fargo	1
   15	Charleston	1
   16	Milwaukee	1
   17		2
   18		6

2. When this report is run in MicroStrategy Desktop, the following result set is returned:

Follow the steps outlined in KB10818- How to use Logical Views introduced in MicroStrategy to specify an outer join between two attribute lookup tables when only attributes are on a report?

1. Create a metric based on the attribute Country, for example Max(Country).
2. Place attributes Country and Distribution Center on report template, and the above metric either on report template, or in Report Objects window if you have an OLAP license.
3. Go to Data > VLDB Properties > Joins > "Preserve all final pass result elements" property and choose "Preserve all final pass result elements" option.
   

When this report is run, the following results are displayed: