During my days at Cybage, I used to read lots of internal documentation from MicroStrategy. Well, I had access to or were give documents for knowledge purpose. For months, even after having left Cybage, I used to try to decode why MicroStrategy likes Snowflake schema more than Star Schema. (Kimball hates Snowflake and abuses it like anything, read Warehouse Toolkit by him). That’s why I used to like reading about Enmon more. I came to know about this so call affinity was actually a shortcoming the way MicroStrategy was designed and/or programmed.

MicroStrategy document used to say and recommend Snowflake over Star. Reason – MicroStrategy is designed to make most of snowflake schema. Well actually it is a bug in MicroStrategy that will prevent it to work perfectly in Star schema.

Snapshots are from MicroStrategy Tech Notes:

This schema is characterized by one lookup table per dimension, with base tables at the lowest level. This is the fastest way to set up a data warehouse:

This type of schemas is fully supported but difficulties may arise when adding aggregate tables:

Problem —-> Double counting

According to the diagram above, a report that contains [Month] and the a metric SUM(SALES_AMT) will go to the aggregate table [MONTH_STORE_SALES] and join to the [MONTH_ID] column to retrieve the description from the [LU_TIME] table. Since the [MONTH_ID] column is not unique in its lookup table, the results will appear duplicated.

Why —-> MicroStrategy is optimized to work with snowflake schemas, where each attribute level has a distinct lookup table.

Solution —-> If aggregate tables are needed, use one lookup table per attribute to avoid double counting.

Reaction —–> Give me a break.

I could never understand why this affinity. I came to know about this during my early months working on MicroStrategy and no one could solve it.

BTW, I was lucky to know this thing. Person holding position of Director had come to India for knowledge transfer. He is the man behind getting MicroStrategy into the company.I had gone for a tea break and when I came back I didn’t notice that every MicroStrategy developer is missing. I thought they must have gone for a break. But I thought checking out what’ was going on. Karthik told me there was a session by Asif but he said it won’t help me much. Still, I thought I should attend and in that meeting I came to know this. Wow.. this made the visit fruitful for me.

Status of this defect —–> It has been weeded out. I’ve done several implementation of it. But working on 7.2.2 was pain. I had to go for duplicate Logical tables. Those days it was not a good help too.

—-
Update on 21st Sept 2008

MicroStrategy have updated technote last month stating that custom logical tables are workaround to solve this problem.