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.
No matter what reporting tool you use, you will have performance issues if you have complex snowflake joins in the background. This is because every user that runs a report will cause the same SQL to be run - some tools have clever caches, but this falls down when you have user selected prompts.
A SSAS cube is a good option as long as your users feel comfortable with this, but the ideal way is to have aggregate tables for your data (you could call this a mini datamart ) that are designed for your reports.
This works only if you can afford the time for the aggregate tables to be refreshed - if your users need real time data, this isn't really an easy option, but otherwise you can schedule an aggregate procedure to run at set intervals.
The real beauty of this, is that your aggregates can be tailored to your reports and you can get amazing performance.