5 Replies Latest reply: Oct 3, 2012 10:42 AM by Dileep RSS

    Loading  facts at multiple levels

    Dileep
      Hi

      I have a cube with six dimensions. For one of those six dimensions I get data at all levels so what I want to do is load data at all levels and choose no aggregation for that particular dimension. I need levels in that dimension because I have other cubes in same AW for which I get only leaf level data for all dimensions. How can I load data at all levels for a given dimension?

      olap version: 11.2.0.3

      Thanks
      Dileep.
        • 1. Re: Loading  facts at multiple levels
          DavidGreenfield
          In AWM you should be able to drag the dimension key column from the fact table to multiple levels of the hierarchy.

          At the XML level you should see elements in the cube map like this:
          <CubeDimensionalityMap
            Name="MY_DIM"
            Dimensionality="MY_DIM"
            MappedDimension="MY_DIM.MY_HIER.LEAF_LEVEL"
            Expression="MY_FACT.MY_DIM_COLUMN">
          </CubeDimensionalityMap>
          The MappedDimension attribute instructs the server to load data at the LEAF_LEVEL only. To get it to load at all levels you can simply delete the MappedDimension line and recreate the cube. If you then look in the AWM mapping screen you should see MY_FACT.MY_DIM_COLUMN mapped to all levels within MY_DIM.MY_HIER.

          There are a few cases where this will not work, so be warned! Here are some that I can think of.

          (1) If you have an MV on the cube
          (2) You are partitioning by the dimension
          (3) The expression for the CubeDimensionalityMap comes from the dimension table.

          Case (3) sometimes happens because users map to a dimension table column instead of to a fact table column wwhen they are different sides of the table join. For example if the table join condition is "MY_FACT.MY_DIM_COLUMN = MY_DIM_TABLE.KEY_COLUMN" then some people map to the expression MY_DIM_TABLE.KEY_COLUMN instead of to MY_FACT.MY_DIM_COLUMN. If this is true, then you can just change the expression to point to the fact column. A legitimate case of (3) happens where the fact table contains data at a lower level (e.g. DAY) than what is loaded into the AW (e.g. MONTH). A solution there is to create a VIEW that does the correct join and then map the cube to the VIEW.
          • 2. Re: Loading  facts at multiple levels
            Dileep
            Thanks David.

            I tried to drag the dimension key column from the fact table to multiple levels of the hierarchy using AWM. When I tried that I get following error:

            Error popu-up with

            -1


            If I click on more details:

            java.lang.ArrayIndexOutOfBoundsException: -1
            at java.util.ArrayList.get(Unknown Source)
            at oracle.olap.awm.dataobject.olapi.UCubeMapDO.update(UCubeMapDO.java:1058)
            at oracle.olap.awm.dataobject.dialog.PropertyViewer.doApplyAction(PropertyViewer.java:927)
            at oracle.olap.awm.dataobject.dialog.PropertyViewer$1ApplyThread.run(PropertyViewer.java:681)
            • 3. Re: Loading  facts at multiple levels
              Dileep
              Oracle AWM Version: 11.2.0.3.0A
              • 4. Re: Loading  facts at multiple levels
                DavidGreenfield
                I am saddened, but not totally surprised, that AWM did not do the right thing here. The AWM mapping UI is not a 1-1 reflection of the cube mapping as defined in the XML, so these kinds of errors can happen with less common set ups. Can you try hand-editing the XML and recreating the cube?
                • 5. Re: Loading  facts at multiple levels
                  Dileep
                  Sure. I will try that.

                  As always, Thanks for your help...