3 Replies Latest reply: Sep 17, 2012 5:35 AM by DavidGreenfield RSS

    Ragged hierarchy does not aggregate

    628915
      Hi,

      I have a dimension with ragged hierarchy of three levels:

      level 1-> level 2 -> level 3

      Some of the level 2 data do not have children (level 3). I mapped the dimension to cube with both level 2 and level 3 as required. Data is loaded into the cube but not those level 2 that have children.

      Any suggestions are appreciated.

      June
        • 1. Re: Ragged hierarchy does not aggregate
          Nasar-Oracle
          (1). Are there any rejected records in CUBE_REJECTED_RECORDS table?

          (2). Does source sql-view for the cube contain data for those level-2 members that are parents?

          If you are doing cube aggregation over this ragged dimension, then cube data will be loaded for leaf-level members (whether the leaf-level member is at level-2 or level-3) then parent members data will be calculated during aggregation process.

          .
          • 2. Re: Ragged hierarchy does not aggregate
            628915
            Thank you for your response. See my comments at the end of your questions.

            (1). Are there any rejected records in CUBE_REJECTED_RECORDS table? - The cube were loaded successfully. No erros or rejected records.

            (2). Does source sql-view for the cube contain data for those level-2 members that are parents? - No, the source data does not have data for level 2 members that are parents.

            "If you are doing cube aggregation over this ragged dimension, then cube data will be loaded for leaf-level members (whether the leaf-level member is at level-2 or level-3) then parent members data will be calculated during aggregation process." This is exactly what I did. I deleted all the aggregated data. what's left are level 2 (non-parents) and level 3 data only.
            • 3. Re: Ragged hierarchy does not aggregate
              DavidGreenfield
              I see two possible cases here.

              (1) The source data may be loaded for the level 2 members without children, but the subsequent SOLVE step gets rid of them again.

              (2) The source data is not loaded for the level 2 members in the first place.

              You say that there are no rejected records, so problem (2) doesn't seem likely. But you can check that easily enough using the following script. This will clear the cube and load the leaf data, but will not aggregate. Obviously you should change "my_cube" to the name of your cube and set the parallelism appropriately based on your system and the amount of data you have.
              exec dbms_cube.build('my_cube using (clear, load)', add_dimensions=>false, parallelism=>2)
              If there is still no data for the level 2 members after this, then you are having problem number (2). If the data is there, then it is problem (1). I will hold off from giving possible causes of (1) and (2) until we know which it is.