MicroStrategy doesn't have affinity to Snowflake schema (anymore)

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.

'Cause it's a bittersweet symphony this life

Trying to make ends meet, you're a slave to the money then you die.

"I must have done grave sin in my last birth that I landed in Bangalore." this would be the biggest understatement that I would have in my life. This city is said to be IT capital on Asia. This is also the worst metro city in India. It lacks everything that you should expect in a such a kind of city. 24x7 clean running tap water, roads, traffic manner, good housing, wide roads, electricity, affordable and good transport, hospitals, police stations, fire brigades, eateries serving at least good south Indian food. It lacks everything.

Life has come to a stand still coming to Bangalore. I have not made any good friend after coming here except a few whom I knew before coming to Bangalore. Two close friends were lucky enough to move back to Pune or Mumbai. But, sadly I'll be here for long time to come. At least employer is good and paying good too even though there is turbulence in USA Real Estate market. No company would take me. I could be having one of the shakiest retention record in India, specially in IT. I know ITeS can beat me by large. A friend of mine has never completed training in last eight jobs in two years, but he is still surviving. I can't survive long in IT if I go this way. Guru, a warehousing professional working with Covansys (ex-employer) for 7+ years, suggested me during my last days, to stick with a company for 3 years at least, else I'd never get good role after having 10 years of experience. I think it is all coming back to me.

I pity myself and everyone who has come to Bangalore for any reason and suffering. May God help them being strong! Amen.