All user groupings comparison By: Jakub Holásek

In this case, best practice comprises of usability and efficiency. Aimed to all user groups, end users using web takes priority.
A very short description of each grouping object type will be provided and then there will be comparison over various of dimensions. End users perspective means sorting priority for comparison dimensions like web use and creation, response, GUI, metrics support and other features.
You may conclude that the most usable are custom groups and derived elements which is true in most of cases. For specific feature support look at specific sections in the post.
 
Object grouping types
Consolidations
Consolidation is simple grouping on attributes (dimensions).  It represents group of attribute elements from one or more attributes in a way which user wants to.
Custom groups
It is simple / advanced grouping on a report. You can imagine custom group like subreport of the current report where you can perform additional advanced groupings - not only you can do groups similary to consolidations but you can rank or make bands, qualify and much more.
Derived elements
Grouping which may be created for single attribute only. Supports Intelligent cubes reports as well.
Features comparison
In this section you can find comparison between various features of individual grouping types
Web use

• Using for read-only it is without limitation for all grouping types.
• Visibility of custom groups is better than other groupings because you can see them while browsing through project folders. Both consolidations and derived elements they are visible at a report level only.
• Since MSTR 10 it is possible to assign all grouping types not only to reports but to VI dashboard directly.

Web creation & modification

• Consolidations cannot be created nor edited over web at all
• Custom groups may be created and modified if no banding/ranking is used. Also some formatting options are not in Web available. Still if it is created in developer, the function of the group works perfectly well in the Web, just cannot be modified
• Although Web GUI for custom groups is far from perfect, the creation for end users is most visible at a folder level, because CG creation button is at the home page of a project by default and creation of derived elements is possible at a report only (for both - on the fly and standalone objects).
• Creation of simple derived elements is very quick at a report. For changing certain attribute element name this may be the fastest and easiest way for end user.

"On the fly" object creation - on a report/dashboard

• Quick creation of the user grouping at a report is currently available for derived elements only.
• Since MSTR10 it is also possible to create derived elements directly in VI dashboards.

Intelligent cubes & reports based on them

• For intelligent cubes there is not much to choose, only derived elements support IC based reports
• It's possible to have report sourced trough dynamic souring without losing previously created "on the fly" derived elements.
• No report containing either custom group or consolidation is eligible for dynamic sorucing of an intelligent cube.

Metrics with DISTINCT count

• Consolidations and custom groups can support, because another sql statement may be created and retrieve the correct results from base tables.
• In some cases custom group/consolidation on a report may cause metric value not to be shown. Moving the CG/Consolidation to the left as a first column may fix the problem.
• Derived elements and distinct count metrics arent supported so far, you can read more in TN41160 (with subtotals) or vote for a feature which could bring the support.
• There is an idea or improvement for Derived elements

Banding and ranking build-in functionality

• The only native functionality for banding/ranking operations is with custom groups. Unfortunately these powerful features are available for creation in Developer only. Once created, it may be used without issues over web.
• In most of cases it is possible to create additional metrics with lag/banding/case functions which may work as usable workaround

Row level operations
WIth row level operations you can sum,subtract,divide multiple rows. Imagine you have revenue metric and month attribute and you want to see difference between two particular months.

• Both derived elements and consolidations support row level operations. Simple creation of these calculation groups is very easy in Web for derived elements not to speaking consolidations cannot be modified over web at all
• For custom groups you may create multiple derived metrics to accomplish this task which may be useful in some cases

Separate column for grouping object
Does user have custom group/consolidation/derived element as a separate column and possibility of adding related attributes as separate columns?.

• Especially custom groups created in Developer offer formatting functionality providing ability to have name of the group and its value as separate columns. Also possible in web with limited formatting options.
• Along with consolidations there can be the same attribute(s) as are defined in the consolidation on the report template
• It is not possible for derived elements to have original attribute with original elements and another column with attribute augmented by derived elements. You need to duplicate the attribute to do so. Fortunately since MSTR10 it is possible to do so at a document/dashboard.
• For derived elements, if you need to have just a number of items in the group there is a workaround TN34916

Multiple attributes for definition of one group
Lets say you want to create a group which definition would be similar to MyFavoriteVeggies = Cucumber, Carrot, Kale (Vegetable attribute) + Leafy and salads (Vegetable category attribute)

• The best gui and support is for Custom groups definition when creating multiple attribute qualification groups
• Because derived element is always related to specific attribute it is not possible to created DE over multiple attributes.As a workaround In some cases you may create additional attribute form which could contain the additional attribute elements (may be useful for product category-subcategory) or you can add this information to the original attribute desc as suffix and use filtering qualification like "contains".

Attribute elements filter qualifications
Lets say you want to create your group by definition All products beginning with "M" or containing "strateg" words in attribute elements.

• For consolidations you are allowed to select only specific elements, nothing else.
• Both custom groups and derived elements support filtering qualifications

All other option
Lets say you want to create three groups - 1: Product A+B, 2:Product C+E , 3:All the remaining products (not specified in the first two groups).

• For consolidations there is no way to specify All other option hence this grouping type is NOT recommended for lookups which change often - you would need to edit the object elements everytime a change has been performed. Of course there is no problem for attributes with non changing elements count (like months in a year).
• There is no direct option in Custom groups to specify All other option, but you can utilize filter qualifications and construct any conditions which may result in desired output. Still, having to create definitions like "Get me all products - products from group A and products from group B" may be tedious especially because you need to use groups conditions - you cannot reference already created group in custom group
• As for derived elements - not only there is native easy All other option, but there is also possibility of displaying the items of the group consolidated (as one) or let them separate

Standalone object creation

• All grouping types can be created as standalone objects to be used in multiple reports without recreating the grouping object.

Database processing time

• Although consolidations add some additional SQL, because of only simple groupings it doesnt have significant performance affect to the DB, case function is often used and groupings are done in one table/subquery most of the time.
• Custom groups generate additional SQL. The more groups in CG the more Sql. The bigger fact table the longer time to process. Since MSTR 9.4 simple custom groups may be created in one pass even with multiple groups so there has been pretty good optimalization over time (check out SQL global optimization vldb property in administrator supplemental ). So the real processing time really depends on your MSTR version and settings.
• Derived elements are fully processed in MSTR OLAP engine almost instantly, no DB processing time.

Developer creation & Modification

• For creation in Developer the most intuitive and feature rich is to use Custom groups. Since all grouping object types can be created there this dimension is not the decision point most of time especially because most of end users do not use Developer.
• As for derived elements -if one wants to create "on the fly element" or existing derived element to be modified, the report must be based on an intelligent cube. This effectively means if you need to modify "on the fly" derived element created on a report dynamic sourcing is not good enough. In that case using Web is the only option.

Developer use

• For using in Developer its convenient to use any grouping type if it was already created and assigned to a report.
• As for derived elements -if you need to assign existing derived element to report attribute, the report must be based on an intelligent cube. Otherwise you can work over it with Web.

Command manager support
Is it possible to create one of the grouping types over command manager?.

• Only custom groups may be created over command manager.

Nesting
What if you have already created the grouping object, can you reference it in the new one?
View / Report filter usability
What if you have already created the grouping object, can you reference it in the new one?