Microstrategy cube and SQL Server columnstore indexes
Context
Business intelligence project
Challenge
Determine a technical solution among several offered by the database (SQL Server) or reporting tool (Microstrategy). Study the possibility of removing Microstrategy cubes in favor of MSSQL columnstore indexes
Technologies used
DBMS: MSSQL Server 2014 EE • Reporting: Microstrategy 9 • ETL: SSIS
Methodology and implementation
The objective of this study was to compare performance between Microstrategy cubes and SQL Server 2014 columnstore indexes.
Two approaches were tested: 1. Using native Microstrategy cubes 2. Replacement with columnstore indexes on SQL Server with direct queries
The tests showed significant differences both in calculation times and user query response times.
Results
Once created, the cube responds in a few hundredths of a second. For end users, there is no question of doing without such performance. Microstrategy cubes will still endure here. But the calculation times have been reduced by 10!
Visualizations
Data compression analysis
Comparison of dashboard build times
Compilation time vs execution time