Consolitdations vs Custom Groups
At MicroStrategy World 2011, my ETL/DBA colleague attended to gain a better perspective of where MicroStrategy fits in. One of his comments struck me when he said he was pleasantly surprised that through the entire conference including all of the High Performance sessions, MicroStrategy always took the position of tweaking their side as opposed to pushing things to the database. While it may seem obvious to focus on MicroStrategy since it’s a MicroStrategy conference, the fact is that the greatest amount of time that a report takes to run is going to be due to the database. While schema design and database optimization is critical, MicroStrategy always tries to provide the developer with options to accomplish a task. While some will debate which method is the “best”, the truth is that it depends on each individual situation. It’s nice to know that MicroStrategy strives to provide as many flexible options and methods to provide a solution, and it’s up to you to choose the best tool for the job.
There’s a delicate balance, when developing a MicroStrategy Schema, on when to know to build an auxiliary object in MicroStrategy and when to push that work to the ETL/Database. Custom Groups and Consolidations are two such tools that can provide quick, low cost solutions but sometimes at a terrible performance price. Knowing when and how to take advantage of these two powerful objects is critical to making that 3 minute report run in 30 seconds. Today I’ll compare the two objects and mention their highlights.
Both a Custom Group and Consolidation are referred to as “Virtual Attributes”, in that the result on the report is indistinguishable from an Attribute. The difference is just how they accomplish this task.
A Consolidation is the faster of the two options, but can only be used in certain situations. It returns all of the values defined in the Consolidation in the query, and the Analytical Engine (in memory at run time) creates the groupings. The key restriction here is that you must explicitly define all elements, meaning that if new elements are added later you have to manually update your Consolidation. A Consolidation is a great choice when elements are mostly static, such as a Region Consolidation that is a grouping of States.
A Custom Group is more flexible, but slower. Each Custom Group element is a separate filter and generally creates a separate pass of SQL. With each release of MicroStrategy, they add more and more improvements to the SQL it writes. Custom Groups can use multiple attributes or even metrics, and do dynamic filters and comparisons without the need for explicit definitions.
A Custom Group can do everything a Consolidation can do, but a Consolidation is faster when it can be used.
Multiple Custom Groups on a report end up multiplying by each other in regards to the number of passes of SQL. So if you have a Custom Group with 10 elements, and a 2nd Custom Group with 10 elements, that’s 100 passes of SQL times however many passes the Metrics on the report create.
I prefer to use Conoslidations first, Custom Groups second, ApplySimple Attributes if performance of the Custom Group is too poor, and finally pushing the structure to the Data Warehouse schema if it is too complicated.
The main factor, again, is balance. Consolidations are simple objects to create, so if the scenario is basic, it’s a very low effort, high speed solution. Custom Groups are also low effort and can accomplish tasks that would be very complicated on a database, such as a Date Grouping (Yesterday, Week to Date, Month to Date). The reality though is that none of the solutions is the “best”, so just educate yourself on the options, choose the best tool for the job, and try all of them to see which performs the best for your project and solution.