Optimal Database Structure for MicroStrategy

Question

I have spent most of my career developing data warehouses\marts as Star Schemas because they were typically used in conjunction with Microsoft's Analysis Services. However, we are starting to leverage MicroStrategy 9.0.1, and I have been told that Star Schemas are less than optimal for this platform. MicroStrategy does not have an official stance on this topic so I thought I would ask this community. Should I still continue to use the denormalized structures, or should I consider a more normalized approach in reagards to this platform?

My intent is not to start a Kimball vs. Inmon vs etc war, any real world experience would be appreciated

Answer 1

Hi Irwin, I work for a bank in Turkey, and we have been working with MicroStrategy for over 3 years. We do have over 20 different projects running on different databases, and different schema types. When designed(and implemented) correctly, MSTR is quite capable of handling star schemas, and do generate moderately nice sql statements. Getting used to MSTR's parent-child and lookup/fact table handling when designing the arcitecture can be a hassle, i should say though. But once you get over it, it's quite convenient.

Answer 2

I've had the pleasure (or otherwise) of working with MicroStrategy for the past eight years. I think it would be fair to say that the product was designed to be used with a schema in the third normal form. That is to say, it will be easiest to model your objects in the tool with the schema designed in this manner.

As Ugur says, MSTR is quite capable of working with a star schema, and depending on your data, it may be better to use a star schema (for performance purposes) even if the modelling is a little (or a lot) harder in the MicroStrategy project.

Answer 3

Its actually not a big deal using star schemas with MicroStrategy. It just takes a little getting used to, and it generates fine queries with that format.

From a very seasoned MSTR consultant, I've heard the data shape MSTR really likes is a sort of modified snowflake. Where the data dimensions are modeled as a snow flake, but each tier contains the data of the tables in the hierarchy above it.

I think you can see the pattern in the jumpstart project. Located here: http://www.microstrategy.com/BI-application-jumpstart/

Ultimately, I think you should continue with the techniques that work best for you. The setup of the logical data model shouldn't be too much trouble, and MSTR has a ton of performance optimization techniques (caching, in-memory cubes, ...) that you can apply afterwords to juice things up.