You’ll find in the following post some general considerations to have in mind when designing cubes about how to dispatch indicators into groups to suite the end-user needs and the consequences of these choices.
Lately, a colleague of mine at Avanade was embarrassed because his customer wanted to visually group and mix heterogeneous kind of indicators into same folders so that he’ll later find them easily into his pivot table into Excel.
As a good practices follower he designed his data warehouse and cube by respecting multidimensional modeling, commonly approved, best practices and putted all the measures that share the same grain into a single fact table and into the same measure group…. which, as he discovered it later, is not necessarily the organization that the end-user have in mind for his data when it comes to build analysis reports.
My colleague first thought was to rely on the “display folder” and “associated measure group” properties to fill the end-user need. The drawback is that what is defined in a measure group stays in the measure group and a fact table cannot lead to more than one measure group.
So as it comes that he was unable to easily answer the requirements, he asked me for advises on how to handle the situation. Here is what we can say…
First let us recap what we have in hands, we have two kinds of indicators (I’m letting the KPIs away in this article):
- They are originated from a single fact table, they are organized into measure groups and cannot be dispatched into several measure groups as it appears that a fact table can only have one measure group associated (I’ll do not speak here about distinct count measures that can in fact be isolated as it is a very specific case).
- Inside the given Measure Group, Measures can be organized by Display Folders by setting the “display folder” property.
- The same display folder name can be used for Measures from different measure groups, but the result should be something like that
Display Folder 1
- Mesure 1
Display Folder 1
- Mesure 2
- Mesure 2
Calculated Measures or Calculations:
- They are defined in the MDX script of the cube
- They can be associated to an existing Measure Group
- They can be further organized by display folders
Note that if there is no associated Measure Group specified, the pivot table of Excel group them all into a “virtual” Measure Group named “Values”.
Let’s add some more considerations:
- A Measure Group is technically speaking linked to a single fact table and functionally speaking to a specific grain. The multidimensional modeling theory teaches us to group same grain’s indicators into the same fact table and then the same Measure Group.
- Depending on their business definition, calculations should follow the same pattern and be dispatched accordingly to ease the navigation. Indeed, a very handy feature of the Excel pivot table is his ability to filter the cube metadata view from the task pane according to a given measure group. In fact, it only shows what’s relating to it based the metadata presented by the dimension usage matrix found in the cube designer. This great feature will work with calculations only if they are well dispatched into original measure groups.
One may suggest creating a calculation for each and every measure. The problem with this solution from the modeling theory point of view is that you break the rule that suggest to group all indicator having the same grain in a single fact table/measure group and from a more technical and practical perspective you will be facing performance issues sooner or later. And trust me, it’s always too soon J
So my advice here would be, again, every time it’s allowed by the end-user needs, to group indicators of the same grain in a single table (therefore a single measure group) to take advantage of the end-user restitution tool which is in our case Excel. Using display folders in that case is not a problem. This one should always be favored in the first place because it’s the way the tool works the best.
But, if you really need to dispatch measures into several measure groups, clone the fact table either physically or simply its definition in the data source view and then create logical measure group accordingly. This way you’ll save the grain unity. Subsequently, you’ll have to plan more space to hold those data, maybe in the relational database if you clone the table, always in the cube because of the new partitions created with new measure groups. The maintenance should also be a little bit more complicated/expensive as the same definition should in some case be updated twice or more.
All things considered, the calculation solution developed above should always be avoided; the natural way should always be considered first; the last solution is acceptable if everyone’s comfortable with the side effects.