Prologue: since my question here How to create a report with several cubes grouped in lines? many changes were made. I had a report where I needed to display multiple different cubes grouped in rows instead of having them side by side in columns. Helmut Heimann pointed me Gabriele Gallo's How To: Choose whether to define an information as an Entity or Infocube. Design and report needs didn't go along. In the same post Alessio Biondi showed me the option that solved the problem: "align: vertically". Lately, learning new things and trying them out on that report, I found that my search wasn't done yet. Luckily he also pointed me to the BOARD Data Picker Functionality.
The idea is to combine the two approaches of database design. The first being, one cube per measure. The second being, storing all the measures in one cube and having an entity that tells which row is what.
The gist is to keep one cube per measure and add a "kpi-entity" to all of the cubes. On that entity, all of the cubes have the same value. In this example case the code is "value". Using rules and the data picker functionality I refer to the other cubes.
This design has several advantages over using only one of the two approaches. The most important for me is the smaller redesign that was necessary. It consisted of: 1) adding a "measure" entity with a "value" row; 2) adapting the cube datareaders to set their new "measure" dimension value to "value"; 3) creating a new rule using the datapicker in order to refer to other cubes.
It has to said that it also has a downsides: 1) it is slow; 2) the rules created here work in rows. If the "measure" dimension is to be used in columns, it won't work. After I started playing around with the final layout I came up with more problems - all related to using rules. I cannot make out yet if those are bugs or not - I'll update later.
The rest of the post demonstrates doing the same with a demo database I used for Charts in dataviews. In 8 easy steps
1) create an entity (database \ entities)
2) add some members
Be sure to set the value of "Max Item Nr" to something significant (10 or 20, however many cubes and calculations you are planning to have there).
3) adapt the cube versions (database \ cubes)
Click on a cube to change the structure.
Click on structure to see the new "Measure" dimension.
After having added the "Measure" dimension, set the structure to non-sparse (dense).
Repeat this step for all of the cubes you want to refer to later.
4) Create a new rule (database \ rules)
5) Set the rules' values to the values of the cubes you modified in step 3. This is done using the datapicker.
This is how it works:
So we'll need to know the physical names of the cubes! Back to database \ cubes.
And that's what the rules look like.
6) adapt the cubes' datareaders. (database \ data reader)
Notice: I'm being lazy here and didn't adapt the source file. I took just any other column that would always be filled and then changed the value via ETL.
Being lazy does not come for free.
So here I put the value of the "Measure" dimension to the constant "value".
7) Execute the data reader
8) Use the results in a dataview
And now you have the added benefit of being able to show certain measures/KPIs/rows based on an entity filter like this:
Comments, feedback, suggestions welcome
To those of you using the approach of having one cube for several measures: what's your opinion?