Wednesday, May 30, 2012

How to copy and paste shared dimensions for IBM cubing services

Problem: Date dimension is most used in any data warehousing project. Usually, each fact table uses multiple date role playing dimensions. It takes very big effort to define date dimensions, and and make sure that these defined dimensions are exact same.  Unfortunately, IBM cubing services does NOT provide functionality to copy and paste dimension, whether a dimension is shared or regular dimension.

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

  1. Export OLAP metadata into a xml file using design studio
  2. open this  xml file using xml notepad (download free from MS), or using simple notepad
  3. Copies the shared dimension you want and paste it one or multiple times, then change the dimension name of each pasted dimension.
  4. save this xml file
  5. Import this xml file using design studio with overwriting
  6. Add new shared dimension into the cube model you want and make join to fact table.

No comments:

Post a Comment