Microstrategy cube and SQL Server columnstore indexes in 2022

We had already conducted such a study in 2015 for another client but the subject came back 7 years later in 2022. **Was direct query feasible on SQL Server 2019 EE to compete with Microstrategy cubes?**

In this case study, the client wants to improve data delivery time and cube loading is the main obstacle to rapid refresh.

A Microstrategy cube studied and causing problems is composed of 2 main measures and 12 dimensions.

Two new physical machines are provisioned (PROD & DRP/DEV/QUAL). In the age of all cloud this may seem heretical but these competition machines allow you to enjoy very large CPU, memory, and disk capacity whose cost would have been prohibitive at any cloud provider.

**The two MSSQL machines are identical and will run 24/7:** - 64 cores / 128 Threads - 768GB RAM - Dedicated and local storage: 14 TB of NVMe disks delivering 14GB/s read and 11GB/s write for data and log (14000 IOPS of 1MB), 7GB/s read 1.5GB/s for tempdb (7000 IOPS of 1MB) - Network 2x10Gbs to application servers + 2x10Gbs for backup outsourcing

A single machine of this type would cost around $180k per year at AWS (R6i metal) (and probably more on Azure or GCP), which is more than the purchase price of the physical machine…

**Microstrategy Cubes Loading**

During the audit we saw that some cubes was big and not fast to reload. and another problem is that the loading of the cubes are relaunch just when the previous one finished

**Microtrategy VLDB limitations for using partitionning**

The cubes did not use Microstrategy partitioning. This technic can reduced the load time, but this need some change in the VLDB options. This change will increase a lot the volume of data transfered between the database and the Microstrategy Intelligence Server

**CPU consumption during a partioned cube loading**

The problem of the Microstrategy partitioning is that the performance improvement depends on the number of CPU of the Microstrategy IS. In the case of our test we decided to push the partitions number to 8 on a 8vcpu VM. This is not a good practice : in the ideal case, the partitions number should be 1/2 the number of vcpu. If you want a faster extraction you will need many cpu on the Microstrategy IS.

**So what can we do ?**

Before the new Hardware of the database and new SQL Version : performance of direct query was not performant and Microstrategy Cubes were mandatory. With the new hardware and new SQL Server version, the equation is completly different.

One little problem remain with direct SQL Query : for some queries, when the few restrictions sometimes leave the data too little filtered, the on the fly aggregation take too much time. For exemple aggregate 4M rows to 250k Rows, SQL Server can take 6s in our context. (using 30s of cpu because of the SQL Server Parallelism)

**The solution : in databases stored aggregates tables**

Aggregate Tables, old good solution for improve direct query performance can be used. We have some dimension elements that are mandatory in all queries. Theses dimensions will constitute the fix part of all the aggregate tables. On the other part, we axe 3 axis with 6 to 7 levels each. They will constitute the mobile part of the candidates for aggregate tables. We calculate all the number of rows for each candidate aggregate tables. We will also cross theses information we the custom groups used be final user. This will help to choose the best aggregate tables.

The question will be : how long will it take to refresh aggregate tables ? With the new hardware and using clustered columnstore the performance are quite good (< 15s) and the space used for the aggregate is ridiculous (1GB)