9 Replies Latest reply: Dec 3, 2012 12:43 AM by Shankar S.-Oracle RSS

    Load data at multiple levels

      Hi All,

      I have a dimension with three levels for which I want to load data into cube(all three levels). I tried to do this using AWM but when I map same fact column to all three levels AWM automatically undoing my changes and keeping mapping at only leaf level when I click on "Apply" button. I am not planning to do any aggregation across this dimension so I choose Nonadditive as aggregation operator.

      I need this dimension hierarchy for partitioning the cube and for other purposes so I am not planning to make it a flat dimension.

      olap version:
      AWM Version :

        • 1. Re: Load data at multiple levels
          For your Cube mapping in AWM, are you using 'Table Mapping View' or 'Graphical Mapping View'?

          'Table Mapping View' sometimes work better.
          • 2. Re: Load data at multiple levels
            I have been using the Graphical Mapping view but I tested with table mapping and it works the same way(As soon as I click on apply mapping goes away)
            • 3. Re: Load data at multiple levels
              One more thing. Try defining the hierarchy as "Ragged".

              In AWM, click on the hierarchy and then in the right window click on 'Implementation Details' tab, and select *"Allow Ragged Data"*.

              Then try to do your cube mapping again.
              • 4. Re: Load data at multiple levels
                By default following are checked.

                Allow level skipping data
                Allow ragged data
                • 5. Re: Load data at multiple levels
                  As one last thing, try unchecking "Allow level skipping data" and then try cube mapping again.

                  If that still does not work, then open up SR.
                  • 6. Re: Load data at multiple levels
                    Shankar S.-Oracle
                    In order to allow Mapping of multiple levels of dimension to same column in fact table, you're required to ensure that you have mapped the dimension in such a way that the dimension is built using the natural keys in the source (relational) table/view.

                    Cube mapping needs mapping same col to 3 levels.

                    Before Cube mapping, ensure that the related dimension is mapped to NOT generate Surrogate Keys (i.e uses Natural Keys while building dimension).

                    Ensure that the related setting.. "Generate surrogate keys in AW" or some such ... is unchecked.
                    A reln key = 101 should become node: 101 in dimension.. It should NOT become STORE_101 or even AREA_101 etc..

                    All relational keys should be unique at a specific level (across all stores, say and also across all areas). Since they are being mapped to Dimension, all keys should be unique for whole dimension... i.e. key 101 cannot be used for level STORE and also reused for level AREA or some other level. Keys need to be unique for whole dimension (whether using Star table containing all levels or snowflake tables for all levels).

                    You can use ranges in the relational schema to ensure that keys are unique for whole dimension.
                    1mil to 5 mil range for stores, 8mil to 9mil for Areas etc... or 10000 to 50000 for L1, 500001 to 60000 for L2, 60001 to 62000 for L3 etc..

                    Further help... you can even check in the AWM dependent process for specifics of the error.. Sometimes the java error msg scrolling past in the dos cmd window is instructive.
                    Also check the docs for Using Natural key ... AWM Help may contain some useful info relating to this.

                    • 7. Re: Load data at multiple levels
                      I use natural keys and all relational keys are unique in my dimension across all levels. I do not use surrogate keys and I unchecked that option.
                      • 8. Re: Load data at multiple levels
                        Shankar S.-Oracle
                        Ok. Opening up an SR may be the best way forward.
                        • 9. Re: Load data at multiple levels
                          Shankar S.-Oracle
                          In some versions of Oracle database (10g and 11gR1, i think), the ability to map to multiple levels required an un-compressed Cube.
                          I think that restriction went away with 11gR2 but maybe worth trying if that helps.
                          You can make the cube uncompressed and re-try the mapping.

                          There could also be a way to manually edit the cube AWXML to allow mapping/loading at multiple levels but i'm not aware of how to do that.