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.

Another approach is to create an Attribute to do your dynamic grouping.  You can use ApplySimple() in the Attribute Form Expression and simply do a pass through case statement.  You get the flexibility of Custom Groups but it can be performed in a single pass instead of all of the multiple passes.  This could complicate your schema in some situations, but it’s mostly safe when it’s simply a Parent Attribute on a Dimension.

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.

You may also like...

34 Responses

  1. Amigoser says:

    I guess since 9.0.1 the SQL of a Custom Group is much more better than before. It uses a lot of case statements to reduce the number of separat passes. I saw scenarios with more than one consolidation on the template where the custom group performed better.

  2. Bryan says:

    Yeah, they’re definitely getting closer in terms of speed, and there’s hardly a set rule any more. I suspect at some point, they’ll just merge into the same object. I really don’t see a reason why the SQL Engine can’t determine that if a Custom Group is only referencing a single attribute, then just handle it in memory as an option like a Consolidation.

  3. ruggedboyz says:

    thanks for the post, did give me more insights into the world of virtual attributes

  4. iliyas says:

    /*2nd Custom Group with 10 elements, that’s 100 passes of SQL times however many passes the Metrics on the report create.*/

    it should be 20 passes right?
    1 custom group with 10 elements, 10 passes will create and each pass will have condition for each elements

  5. Bryan says:

    They get multiplied and not added, so it’s all of CG2s passes for each element of CG1. It’s entirely possible that through various VLDB settings that’s not 100% true, but bottom line, it’s a lot less efficient 🙂

    • Nisha says:

      Bryan, I am not sure I understand how it get multiplied than added. I have 4 custom groups of 3 elements each and one metric and the total no. of passes were 5 only. Could u please explain. Thanks

  6. New to MSTY says:

    Hi Bryan..

    Nice Post..

    Hope I can get some help where I am finding some difficulty as I am new to MSTY.
    I have a requirement to group the timestamp column based on time value as 5A-7A,7-9A.,etc.,These groups should be available in prompts as well..Users might wish to see whether a single group or multiple group on the report output.

    How can this be achieved? I tried using Custom Group.But how to put up these groups in your prompt..

    • Bryan says:

      I would build this as an Attribute using the ApplySimple() method mentioned at the end of this post. That’s basically just a case statement which will do the groupings and yield the elements. That method is faster than the Custom Group method.

    • New to MSTY says:

      Thanks Bryan for your prompt response..Let me try using case stmt as u mentioned..

  7. Hi Bryan

    Great post!

    So when using the ApplySimple() method in the attribute definition we essentially get a case statement, so i’m guessing each group element (each “when a = ?” clause) is mutually exclusive. This is the reason I see the custom group objects as slightly more powerful since it can aggregate in a similar way to olap cubes (via multi-pass sql as you mentioned), i.e. we can have a “total sales” row followed by a “new product sales” row, followed by a promotion product sales row. I ran into a problem today whereby I had to create a % to total (“total sales row”) over the custom group, the only way I could get there was with an ApplyAgg() in a metric passing through a sub-query for the aggregation, seems nasty. Have you done this before?

    • Bryan says:

      Yeah, in the case you described, Custom Groups would be the way to go. It’s really about choosing the method that works best for your situation. Custom Groups come at a higher cost, so they should only be used when they provide something that the others don’t (such as the ability to count a row in multiple places).

      I don’t think I’ve encountered a time where I had to do a calculation across a Custom Group level, but ApplyAgg sounds right.

    • Hi Bryan

      Just though id update you on where I got with this, it was for a “% to total” calculation where I needed a grand total that spanned across custom group elements. In the Level(dimensionality) of my grand total metric i needed to set filtering=”ignore” and grouping=”None” for any attribute that was used within the custom group element conditions. The “apply agg” route cause too many performance issues.

      Thanks

      Dan

      • Raghav Sharma says:

        Hi Bryan

        I’m sorry but don’t you think your statement should be modified as follows?

        “A Custom Group can do everything a Consolidation can do (except the arithmetic operations between elements), but a Consolidation is faster when it can be used.”

        I could do an OR between elements’ filters to ADD them. However the AND NOT does not work always for subtraction. Specifically in a case where there is no common / overlapping data between two elements. Maybe it is my lack of knowledge, but so far I haven’t been able to achieve the subtraction part using Custom Groups.

        Thanks
        Raghav

  8. Chirag says:

    /* Another approach is to create an Attribute to do your dynamic grouping. You can use ApplySimple() in the Attribute Form Expression and simply do a pass through case statement. You get the flexibility of Custom Groups but it can be performed in a single pass instead of all of the multiple passes. This could complicate your schema in some situations, but it’s mostly safe when it’s simply a Parent Attribute on a Dimension. */

    Hi Bryan,
    How do we do that? Do you have any example or case statement which can provide group of attributes dynamically ?

    For e.g. I have Week Attribute. I want to build a grouping dynamically based on the latest week, last 2 weeks, last 3 weeks etc. I don’t want to use Custom Group as it degrades the performance drastically. Neither can I use consolidations because it doesn’t help me to group dynamically based on the latest updates in Week dimension table.

    I am only left with the 3rd option that you mentioned in this thread but I did not get any idea about how we can achieve this. Any help on this is greatly helpful.

    Thanks,
    Chirag

    • Bryan says:

      In this case, Custom Group is your only option. The reason is that if you have 1 row of data, then that row is valid for this week, last week, week before, etc, so you need to count the row 3 times. A case statement is going to assign that row a single value, and not generate the extra rows. In order to do that, you have to do unions, which is effectively what a Custom Group does (and why it’s slower).

  9. S1Group1 says:

    Hi Bryan, regarding the sentence
    “There are 2 key restrictions here, which are that you can only use a single attribute for the Consolidation and you must explicitly define all elements, meaning that if new elements are added later you have to manually update your Consolidation”
    you did mention that only one attribute can be used, but I’ve tried to include elements using different attributes for a consolidation and it can be done.
    Is this what you mean or am I understanding it in the wrong way?

    Thanks,
    Izzat

  10. Linda Schatz says:

    Hi – thanks for sharing.
    I am trying to put together a a rolling four weeks, four months, and four quarters (as columns), and I set up a custom group with those three filters. Problem is it doesn’t split out the weeks, months, and quarters; it rolls them up. Any suggestions??

    • Bryan says:

      So you want 12 columns in total? The only way to do that would be to create the 12 custom group elements doing things like last week, last week-1, last week-2, etc.

  11. alex says:

    Hey,
    I have created consolidations for attributes I need in a particular way. Custom groups are too slow because my dataset is too big, so I would rather work with consolidations.

    I have created sum, avg, median, and max metrics for a particular fact.

    When I run the consolidation with the metrics for sum I get the correct result. For Average I had to change the evaluation order but it works perfectly.

    I am having trouble with the median and max ? Do you know how to configure consolidations with this specific mathematical functions.

    • Bryan says:

      Not sure, and I’m surprised you got Avg working. In general, those non-additive functions don’t work with Consolidations. How about creating an Attribute using ApplySimple() to do the grouping in the database?

    • alex says:

      Thanks for the reply.
      Interesting,
      I have already created consolidations for the attribute. How can I use this consolidations in the ApplySimple ()because I do not want to specifically list out all elements of the Consolidation.

    • Bryan says:

      You can’t, unfortunately. You’d have to specify them all again in a case statement in an ApplySimple()

  12. Sumesh says:

    I am new to Microstrategy and looking if anyone can help me. I am trying to do copy and paste in element with in cosolidation and it isnt working. its displaying {Error}.

    Can you please help.

  13. Unknown says:

    Bryan, Does the custom group any better in MSTR 9.3.1 or later version. Do you have any thoughts or update on it.

    • Bryan says:

      I think in 9.4 there is a new SQL optimization where Custom Groups will be contained in a single pass using case statements instead of separate passes, which should make it faster.

  14. Raghav says:

    Is there a way by which we could maybe copy a part of the expression of an element, and paste it under another element? We have huge consolidations with element expressions as lengthy as hundreds of lines. It is very difficult to maintain such consolidations, and in a big firm having around 1500 users it is not feasible to make frequent changes in Data Model on every other user’s requirement.

    • Kendra says:

      Yes, you can copy part of a consolidation element into another element of the same consolidation using ctrl-C, ctrl-V.

      You can also copy a consolidation element into a different consolidation. In the destination consolidation editor, on the Elements menu, select Import Elements. It has to be from the same project though.

      • Efim says:

        Ctrl+C works, but Ctrl+V doesn’t.
        ‘Import’ is available only within one single project. I mean I can’t import elements from another project.

      • Raghav Sharma says:

        Thanks for the reply. But I noticed that crtl+c works only for limited type of element groupings. For example: if I only group Account and Department, it is easily copied and validated in the target element. But if the grouping gets more complex like, account and department and region and so on, in such a case the grouping does not get validated in the target element. Haven’t found any work arounds for this so far.

  1. March 8, 2017

    […] Source: TN32015, Bryan Brandow’s BI Blog […]

  2. March 8, 2017

    […] Source: TN32015, Bryan Brandow’s BI Blog […]

Leave a Reply

Your email address will not be published. Required fields are marked *