Here is an interesting stuff I came across recently while working on the PP-Planning module. The deal was to create more than one measures in a model so that one could go beyond the basic "value" in the default measure group. Following are the various options that I came to know from the books\articles, the community experts and little bit of experimenting. My sincere thanks to all of them for sharing the knowledge. The intention here is that if someone comes across similar issues, he can find all the information in a single place and I think this is the best way of passing it on 🙂
To start with in the current version there is only a single "value" field for measure created by default and I don’t know of any way to add a new measure or even change the name from value to something else. There are couple of workaround for it though. Out of the three option that I am aware of and that are listed below, two of them are supported and are "gentlemanly" workarounds and third one is something that is not supported and maybe removed from the future versions, and there is a good reason for it (in a minute).
Option One: Structure does the trick
Structure here means the dimension’s records, properties and the way they are used in the model. The idea is to create a dimension that would hold the "measures" and then use it in the input form (in excel) in the "columns" along with the other dimensions.
Say we have a dimension RoomMeasureDim which has two members\rows, ChairCount & PowerCount & another dimension Customer which holds some names, say ESP & Visa (for example sake). Using these two dimensions we create a generic model RoomModel. This model would have 4 dimensions; the above two and the default time & scenario dimensions. If one would create an input form using the RoomModel and "hide" the Value & the Scenario row (using excel), it would look something like the snapshot below (the gray band is due to the report formatting)…
So in short we are using the structure to give an impression of two measures to the user. If we talk in DB terms, there would be two rows created for each value per row in the RoomMeasureDim.
Option Two: It’s the Assumption
The trick is to create an assumption model and have the measure value in it. This assumption model when used along with the main model would bring along the measures present in the assumption model and expose them in the excel form in the value section.
Option Three: ExportXML\ImportXML (not supported)
This is an PPSCmd option that has not been published. The pseudo flow of this (command-line based) approach is as follows…
1. Run the ExportXML command and export the content into an XML file on the drive.
2. Edit the XML file and add the child-nodes (new measures) into the MeasureGroups node.
3. Delete the original model
4. Import the above updated XML file into PP using the ImportXML option.
Imp Note: The ImportXML is know to create new Ids internally for the objects when it is executed and this might create unknown problems.
Still exploring the interesting world of PP…will keep posted 🙂