Microstrategy Blog

At this point I think that I’ve worked with enough instances and installations of MicroStrategy to know bad and good ideas when I see them. The most common irritation that I’ve come across is this idea that MicroStrategy is where all of the heavy lifting of the parsing and hierarchies takes place. While MicroStrategy can do the heavy lifting, this isn’t always ideal.

For instance:

I worked with a client whose Accounting General Ledger was your basic structure. Let’s say the account structure was thus:

Revenue accounts were 5000 – 5999
Cost of Goods Sold were 6000 – 6999
Expenses were 7000 – 9999

Now, in a MicroStrategy environment, one could create custom groups so that these GL accounts could be properly categorized that defines ranges, but that’s seldom the best way to go about it.

I have predefined categories that I know that I want postage as an expense to roll into. Postage -> Shipping ->Expense.

I create a table called GL_GROUP, which is the next level up of the hierarchy, and added a column to the GL_ACCOUNT able which was a reference to this new table. Here’s the GL_ACCOUNT table.

Note that the GL_GROUP_ID Column is currently Null.

The GL_GROUP table looks like this.

The script that accompanies this table is as such:

UPDATE lugl
SET lugl.GL_GROUP_ID=lug.GL_GROUP_ID
FROM GL_ACCOUNT lugl
JOIN
GL_GROUP lug
ON
lugl.GL_ACCOUNT_ID between lugg.GL_LOW AND lug.GL_HIGH

This has been incorporated into the ETL process so that as any accounts are added and they fall into the appropriate range (the Accounting people have to be on top of good logic on their side). Once complete, you get this.

Now, pull the GL_GROUP table into the Warehouse Catalog, add a GL_GROUP attribute, and modify your GL_ACCOUNT attribute so that it’s the child of GL_GROUP as a one-to-many. Now some of you will look at this and just go “Duh”, but you’d be surprised not only how many MicroStrategy installations I’ve come across where people have made Custom Groups to deal with this (because of the “ranges” of accounts). Custom Groups slow things down.

In short, let the Warehouse do the heavy lifting during your ETL process…and let MicroStrategy be a clean and streamlined as possible when it pushes the SQL through. MicroStrategy can solve this problem alone. But should it? I think not.