Bryan Brandow

Bryan Redux #1: Left Joins in MicroStrategy

Readers:

I am occasionally going to re-blog posts from my friend, Bryan Brandow’s MicroStrategy site.

I consider Bryan one of the best in the business, but his passions lie in other areas these days.

I will denote these blogs by beginning them with “Bryan Redux.” If you want to visit Bryan old site, the URL is http://www.bryanbrandow.com.

Best Regards,

Michael

Bryan posted this on Tuesday, March 22, 2011.

Left Joins in MicroStrategy

Bryan BrandowAn interesting stance by MicroStrategy is that they really push you for proper warehouse modeling (or at least what they consider proper).  At the same time, the tool’s flexibility can really handle just about any model, and I’ve seen the SQL Engine come through in some amazing scenarios where other vendors laughed and walked out.  One commonly requested feature is the ability to left join two tables in a report.  That is, left join Dimension to Fact, not left joining multiple passes.  There are plenty of valid reasons you would need this feature, and for many years I would joke “MicroStrategy can do everything .. except Left Joins”.  Imagine my surprise when I discovered an extremely buried feature that does enable left joins!  I stumbled on this a several months ago and have no idea if it’s been there all along or was introduced recently.  Based on forum and friend activity, not many other people are aware of it either.  Today, I’ll show you the secret.

Build a normal report with Attribute1, Attribute2 and a Metric.  The SQL will come out like this:

select a12.Attribute1  Attribute1, a13.Attribute2  Attribute2, sum(a11.Fact)  Metric

from FactTable a11 join DimAttribute1 a12

on (a11.Attribute1Key = a12.Attribute1Key)

join DimAttribute2 a13   on (a11.Attribute2Key = a13.Attribute2Key)

group by a12.Attribute1, a13.Attribute2

But let’s say that you need to left join DimAttribute2 to FactTable.  Simply follow these steps:

Step 1: Edit the Attribute

  1. In the attribute editor, go to Tools -> VLDB Properties.
  2. Change the property Joins -> Preserve all final pass result elements to the third option, Preserve all elements of final pass result table with respect to lookup table but not relationship table.
  3. Update Schema.

Step 2: Edit the Report

  1. In the report editor, go to Data -> VLDB Properties.
  2. Change the property Joins -> Preserve all final pass result elements to the fourth option, Do not listen to per report level setting, preserve elements of the final pass according to the setting at the attribute level.

With those two options combined, the resulting report now generates this SQL:

select a12.Attribute1  Attribute1, a13.Attribute2  Attribute2, sum(a11.Fact)  Metric

from FactTable a11 join DimAttribute1 a12

on (a11.Attribute1Key = a12.Attribute1Key) left outer join DimAttribute2 a13 

on (a11.Attribute2Key = a13.Attribute2Key)

group by a12.Attribute1, a13.Attribute2

Conclusion Note that since you need to turn on a report level setting, changing the attribute won’t modify your entire system.  This is nice because you can choose to let some reports to left join on that attribute while not others.  One side effect I have experienced is that this attribute is no longer eligible for Intelligent Cubes. If you can live with that, this becomes a pretty handy trick.

Bryan’s Blog Entry Link:  http://www.bryanbrandow.com/2011/03/left-joins-in-microstrategy.html

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s