Microstrategy Blog

I’m sure any MicroStrategy consultant who has worked on this kind of stuff before has dealt with…things change. In this case, I started working on the dashboard for my client, and it was going well, I had a basic graph that presented the inventory and sellthrough yada, yada, yada….but, what followed was a barrage of requests not for the dashboard, but new data points. Adding previously uncaptured extracts from the web and other systems so that MicroStrategy could normalize it all in one place and they could see MORE MORE MOREEEEE!!! There’s only two of us right now…and the other guy is a PERL guru…

It’s funny, when I started Orange Box I really thought that most of the business would be in the actual MicroStrategy development…I was wrong. Data points, web scraping for prices, feedback, randomness, then compiling, subdividing and filtering, all with the intent of eventually getting it into a report, we just haven’t reached the intent part yet. Know wat I mean folks? The taste of the dashboard made them data addicts. This is how heroin started…take note.

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.

I have the pleasure this last week of dealing with Sage’s BusinessWorks software as a data source for MicroStrategy. Hence the slow amount of posts.

Some quick lessons…

After you created a linked data source using an ODBC connection for the Pervasive SQL database (seemed the easiest way) I found that querying it was a pain…

Sometimes I’d get:

The OLE DB provider "MSDASQL" for linked server "BUSINESSWORKS" returned an invalid column definition for table.

That’s because half the time "SELECT * FROM BUSINESSWORKS.DATABASE_NAME..GLAccount " just doesn’t work.

The solution?

SELECT * FROM OPENQUERY(BUSINESSWORKS, 'SELECT * FROM DATABASE_NAME.GLAccount')

This seems to be the fix for any Pervasive SQL linked database in SQL Express. After you get the connectivity stable, now you have to deal with the fact that this is one of those databases that has columns named “History01, History02, etc”.

Good times.

How to find that elusive column in your SQL server.

Tip of The Day:

SELECT
name
FROM
sysobjects
WHERE
id IN (SELECT id FROM syscolumns WHERE name like '%COLUMN_NAME%')

The quick answer? There’s no easy answer, in my experience.

Here’s the two ways I’ve used to work out sort issues.

1. Consolidations – This is a great option if you’re the guy who builds all the reports. You don’t want you’re user base to have to have to drag a consolidation into a report every time they want to sort it…on the other hand…if it’s not an attribute you’re using all the time, then this might be for you.



2. Data Warehouse – This is the more “Elegant” approach. The idea is, in your warehouse, you add a column in addition to the ID and DESC, in this case ACCOUNT_GROUP_SORT, and create a new Form. Tag it as the default sort column with “ascending”. I usually also have the field as viewable, just because many of your users will say something like “see line 12″, and it’s a better visual search key. The downside with this, is you have to actively maintain this column, either manually, or with the ETL process.



Either way, there is no easy permanent fix. Both require maintenance and care, but ultimately make your report more readable.