asked
Using Microstrategy on Highly Normalized SQL Server DB
Hello everyone. I am interested in comments and insights relating to using Microstrategy to report against a complex snowflake SQL Server database comprising of several transactional, normalized (3NF) tables. Specifically, what is the best approach or the challenges on reporting in such an environment? Currently, there are some complex views that serve as analytical fact tables using complex SQL joins between the several transactional tables. The transactional tables also have their own dimensions, and so on. The views seem to work fine in SSRS. However, I have read that Microstrategy is not ideal for reporting against such a complex database (not due to performance of the tool, but more so because of the complexity of the SQL in building these metrics in Microstrategy). What would be the best approach on reporting in such an environment? Would building an SSAS cube on the current data warehouse be a good idea? Should reporting be done on the database, or should a new database or mart be created, specifically to be used by Microstrategy, with only the relevant views for basic reporting? Any advice or opinions are appreciated.
Hi ThoughtExpirement, Microstrategy writes its own queries as it's pulling all of that data in to cache it for reporting. You can customize the query a little if you're brave and want to mess around with the internals, but by default it generates the query on its own. Because of this, SQl views are probably going to give you some pretty sizable performance challenges that you'll have to overcome to keep Microstrategy running decently. You mentioned about the complexity of the code that microstrategy has to generate, this will cause your performance issues because of the way that microstrategy generates that code. I would suggest that you create a new database strictly with your tables, create some ETL to bring them in as facts and dimensions as actual tables. Then you can create and modify your indexes as you need. Additionally, creating an SSAS cube and having Microstrategy just report off of the cube as well would help with some of it. If I remember correctly, Microstrategy works differently than SSAS in that Microstrategy focuses on a ROLAP strategy for its' cubes, whereas SSAS by default focuses on MOLAP. If you have volatility in your tables and dimensions you will probably get better performance out of the SSAS cube, rather than just giving it to microstrategy and letting it cache what it needs on its own.
13 People are following this question.
