Many-to-many relationship between dimensions in PerformancePoint Server
Technically there is no way to create a many-to-many dimension relationship in PerformancePoint Planning Business Modeler. Here is what a senior development lead at Microsoft says to do for incorporating a many-to-many relationship.
"PPS Planning does not currently support many-to-many dimension relationships. However, it can be implemented something like below (I haven’t tried it so there’s bound to be a few missing steps, so full execution is left as an exercise to the reader(s)).
1) Create a dimension to hold the many-to-many mappings (eg. Mapping/association). You add two “Member of Dimension” properties, one for each of the dimensions you want to map.
2) Populate your data into the Mapping dimension via Modeler or DI.
3) Add the two dimensions to your model but not the Mapping dimension.
4) Deploy your modelsite.
5) Using BIDS, create a measure group in the model and map it to the Mapping dimension table (D_Mapping or D_<subsite>_Mapping). The mapping dimension table becomes your many-to-many fact table.
6) Create the many-to-many relationship between the two dimensions along with the above measure group.
Why this works: As long as the model does not change structurally (eg. adding/removing dimensions, changing membersets) the ModelSite deploy will not overwrite the AS cube. This was done on purpose so that cube customizations can be supported (eg. aggregation tunings). If you ever need to make a structural change (or perform a full ModelSite deployment via PPSCmd) you will need to redo the AS customizations."
Another great spot to see more on this topic is PerformancePoint forum. Click here.
