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

Normally, I’m a MicroStrategy cheerleader, with almost nothing that I think the product can’t do. However, I recently was exposed to a product called Spotfire. The product is basically a high end analysis tool that throws together visual tools in an easy to use, drillable format….nothing to new there…but, what it does far better than MicroStrategy does (and I’d love to be corrected) is “MicroCharts” – massive, quick responding, threshold sensitive microcharts.

It makes the visual browsing of adhoc data quick and efficient. You connect to the database table or view, and just drag and drop what you want. Where it bests MicroStrategy (as I’ve used it) is the numerous amounts of dynamic charts that can be created at a whim without lots of planning. So, view the site, play around with it, and let me know if this is something that MicroStrategy can do, especially in regards to the idea of 50 or more charts on one page, quick, and responsive.

All Things Data

So, I’ve logged my first enhancement request…which I’m monitoring with bated breath.

Remember when I did this post about how awesome derived elements are? Well, they’re still awesome, but sometimes I forget that I have awesome all powerful access equal to that of the Gods…sometimes it comes back to bite me.

I rolled out the Derived Elements function to our users. They were as thrilled as I was. Since we’re a project based organization (I work in Video Games) projects come and go, and our Data Warehouse has a constantly expanding list of projects. So, our user base gets to dynamically group like projects based on their predesignated lists (it varies per Business Unit); one list might be “fighting games” and the other “downloadable games”, but next month, there might be more.

Things fell apart when the Web Professional users had new projects that appeared, and they were unable to edit their Derived Elements…once they created them and saved them out to their “My Objects” folder…that was it…it became static. Great functionality…but only so much. As a desktop user, I can get into them and edit them, but our users don’t want to call me to say, “Hey, add project A to group B.” The tutorials like to use “Seasons” to group months, but I think the assumption here was that the lookup lists were static, which isn’t the case with an organization such as ours. So case logged. P4.

I’m thinking Hot Fix 3 has my name written all over it…right?

All Things Data, Enhancement, Wish List

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