I am trying to map a cube and one of the dimensions has ragged hierarchy (products) with levels 1, 2, 3 and 4 with 4 being the most granular. The product can exist at any of these levels. According to this blog http://oracleolap.blogspot.com/2008/01/olap-workshop-4-managing-different.html, the key of the fact table should be mapped to all the level columns of the cube dimension in the cube mapping editor. When I try to do that and save, the mapping for all levels gets automatically cleared except for level 4. Has that changed since this blog was written? Can some one please comment.
This is an AWM bug
BUG 16438778 - MAPPING NOT SAVED IN CUBE WHEN MAPPING TO MULTIPLE LEVELS IN DIMENSION
There is no publicly available fix as far as I know, but thankfully there is a workaround. Suppose that your cube is named MY_CUBE and that your dimension is named PRODUCT. The following PL/SQL should resolve the issue. You will need to detach the AW from AWM before running this. (Best to save the cube to an XML template first, then close AWM.)
<Metadata Version = "1.1">
<Cube Name = "MY_CUBE">
<CubeMap Name = "MAP1">
<CubeDimensionalityMap Name = "PRODUCT" MappedDimension = ""/>
If this returns an error, then compare the XML fragment above to the XML template for the cube. You should see something similar in the template, but it will have MappedDimension="PRODUCT.LEVEL_4" (or equivalent). The key point is that you need to set the MappedDimension to null (using an empty string in XML).
Thanks David. For some reason, importing the XML through the sql script with workspace detached did not work for me, however I was able to fix the issue using these steps:
1. Save the cube as XML template.
2. Modify the XML to make MappedDimension="" as you described above.
3. Delete the cube and re import from the modified XML template.
One thing I should point out is as that if I try to save the mapping from AWM after this change, it removes the mapping again even if the mapping change was for another dimension. So basically this has to be the last step of mapping the dimension.