Analysis: The simplest way is to define one shared dimension, then add this shared dimension multiple times into cube model, which is the SSAS solution. However, cubing services can NOT be done this way; you can only use a shared dimension one time for each cube model. This still could save sometime, such as add date dimension for all 9 cube mode (if we define each fact table as one cube model). The problem is how to handle role playing dimensions. The only way left is to copy and paste shared dimensions.
Solution: The following steps make it possible to copy and paste shared one or multiple dimensions, which will dramatically save time to develop shared dimensions
- Export OLAP metadata into a xml file using design studio
- open this xml file using xml notepad (download free from MS), or using simple notepad
- Copies the shared dimension you want and paste it one or multiple times, then change the dimension name of each pasted dimension.
- save this xml file
- Import this xml file using design studio with overwriting
- Add new shared dimension into the cube model you want and make join to fact table.