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.

All Things Data, Data Warehouse, Things I Learned

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.

All Things Data, Data Warehouse Business Works, Pervasive SQL

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%')

All Things Data, Basic Series, Data Warehouse, Tips and Tricks SQL Column Search

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.

Consolidations, Data Warehouse, Tips and Tricks Attribute Sort

I can’t stress enough the importance of planning your data warehouse properly. I don’t think that I’ve come across a developer who has “done it right” the first pass…or, to clarify, an architect who said “you know what would have been nice, if we would have…”

Things to think on.

• Aggregate Tables – Sometimes it just makes things quicker.
• Pick a date – It’s really helpful to have one Primary date to that you can easily marry your facts. Have this date tied to a lookup heirarchy…I’m open to other suggestions on this though, I’m sure there are some creative ways of dealing with Multiple date fields on one record (eg. Post Date, Create Date, Transaction Date, etc)
• Proper Lookup Heirarchies – Don’t put everything everywhere and expect it to work. You don’t want to have conflicting messages, or multiple joins from table to table. Try to keep one to one relationships.
• COALESCE is your friend in SQL…the idea is if a lookup doesn’t have a match in your data warehouse, have the ID be 0, and have that ID attached to an UNKNOWN. Don’t filter out nulls at the table join level. This is the most important lesson I learned in development from a friend of mine, it keeps things clean…

Anyone else have any input…this is just a prelim list here. We’re human, so we can learn from others mistakes…let’s use that.

Data Warehouse, Things I Learned Data Warehouse