9 Replies Latest reply: Jan 30, 2013 12:34 PM by Dileep RSS

    Load Prune

    Dileep
      Hi All,

      I am getting following error when I try to load into a cube. One of the dimensions of this cube was mapped to three levels from facts table. Assume DIM1 has levels LVL1 , LVL2, LVL3. I load data at all three levels so I mapped same column in facts table to all thee levels. I suspect that is causing the issue. Is there any workaround for this problem?

      <ERROR>
      <![CDATA[
      XOQ-01744: The LOAD PRUNE command cannot be used with cube "TEST.TEST_CUBE".]]>>
      </ERROR>


      Build Script: TEST.TEST_CUBE USING (LOAD PRUNE, SOLVE)

      Olap Version: 11.2.0.3.4

      Thanks
      Dileep.
        • 1. Re: Load Prune
          Joan Puig
          Hi Dileep,

          I might be wrong, but I read somewhere in this forums that LOAD PRUNE was specific to older versions (10g?). I had the same issue as you, and I ended up using dbms_mview.refresh

          http://docs.oracle.com/cd/B10500_01/server.920/a96568/rarmviea.htm#94135

          Greetings,

          Joan
          • 2. Re: Load Prune
            DavidGreenfield
            You can use the LOAD PRUNE command on an 11g cube if the following all hold.

            (1) The cube is partitioned.
            (2) The partition dimension is loaded at one level only. (Technically there is a MappedDimension on the associated CubeDimensionalityMap.)
            (3) The cube has exactly one CubeMap.

            Given our previous discussion, Re: Loading  facts at multiple levels my guess is that you are hitting case (2).
            • 3. Re: Load Prune
              Dileep
              Thanks David.

              Yes. I am loading data for partition dimension at multiple levels(total three levels). To do this I added two more cube maps to the cube dimension.

              Is there anyway to solve this problem ? As I have lot of partitions and I am using LOAD and AGGREGATE(default cube script) loading data into the cube is taking very long time.
              • 4. Re: Load Prune
                DavidGreenfield
                How many partitions do you have in total in your cube and how many do you typically load in a single build? If you only load one or two, then I suggest that you try "LOAD SERIAL" instead of "LOAD PRUNE". If it is many more than that, then the additional cost of the serial load may be too high. Failing that you can explicitly control the partitions that are loaded using the FOR...BUILD construct.

                I don't know why there is a requirement to have only one cube map. I will try to find out for you.
                • 5. Re: Load Prune
                  Dileep
                  Thanks David. I have total 42 partitions. I load around 3 to 6 partitions during cube build. I tried LOAD SERIAL option but it seems it is trying to load all three levels even though there are facts for only one level which is causing it to reject records at other two levels. Here is simple output for one of the partitions.

                  COMPLETED     LOAD SERIAL     TEST_CUBE     CUBE     
                  COMPLETED     LOAD CUBE MAP     TEST_CUBE     CUBE      "<CubeLoad LOADED="0" REJECTED="66066"/>"
                  STARTED     LOAD CUBE MAP     TEST_CUBE     CUBE     
                  COMPLETED     LOAD CUBE MAP     TEST_CUBE     CUBE     "<CubeLoad LOADED="0" REJECTED="66066"/>"
                  STARTED     LOAD CUBE MAP     TEST_CUBE     CUBE     
                  COMPLETED     LOAD CUBE MAP     TEST_CUBE     CUBE     "<CubeLoad LOADED="66066" REJECTED="0"/>"
                  STARTED      LOAD CUBE MAP     TEST_CUBE     CUBE     
                  STARTED     LOAD SERIAL     TEST_CUBE     CUBE     

                  Here I have three levels mapped to a dimension of which my facts table has data for only one level. LOAD SERIAL says it rejected 66066 records which is causing confusion as we do not know is it because of facts not meant to be for that level or there might be an issue with facts itself.
                  • 6. Re: Load Prune
                    DavidGreenfield
                    Dileep,

                    When you run LOAD PARALLEL (which is the default), the queries will contain an IN LIST that specifies only the values in the partition being loaded. This means that the first two cube maps in your case will filter based on time values that are not in the fact table, and will hence load no rows. With LOAD SERIAL there is no IN LIST, so all rows come in at all levels. So I can understand why this is happening. The data should be in the cube, so you could consider this a mere logging anoyance.

                    If you know that the data is only coming in at one level, then you can specify the name of a specific cube map. Suppose, for example, that you have three cube maps named 'MAP1', 'MAP2', and 'MAP3'. If you want to load only from MAP3 then you can say
                    LOAD SERIAL FROM MAP3
                    As an alternative you could define three SQL VIEWS that restrict the fact table to the three levels being loaded. You would then point the three cube maps to these three views.
                    • 7. Re: Load Prune
                      Dileep
                      Thanks David. That make sense.

                      I would also like to pursue your other suggestion

                      "Failing that you can explicitly control the partitions that are loaded using the FOR...BUILD construct."

                      Do I need to know what are the partitions that I am going to load in advance for above thing to work? I guess in that case it is a static script. Is there anyway I can make this dynamic because I do not know in advance which partitions I am going to load sometimes I may need to load multiple partitions for the same client.
                      • 8. Re: Load Prune
                        Nasar-Oracle
                        Dileep,

                        You have to first figure out (programmatically) what partitions need to be loaded. You can also have something like a control table where you store all the partitions (lets say MONTHs) for which the cube needs to be loaded. Then dynamically generate DBMS_CUBE.BUILD statement and execute it.

                        Some sample statements are shown below:

                        /* Load only the selection of data identified by the WHERE clause */
                        BEGIN
                        DBMS_CUBE.BUILD(q'!
                        GLOBAL."TIME",
                        GLOBAL.CHANNEL,
                        GLOBAL.CUSTOMER,
                        GLOBAL.PRODUCT,
                        GLOBAL.UNITS_CUBE USING
                        *(LOAD NO SYNCH*
                        WHERE UNITS_FACT.MONTH_ID LIKE '2006%'
                        AND UNITS_FACT.SALES > 5000)!');
                        END;

                        ---------------------------------------------------------------------------------

                        /* Another example of loading cube data, with WHERE clause */
                        begin
                        dbms_cube.build(q'!
                        price_cost_cubew using ( FOR global.time
                             WHERE HIER_ANCESTOR(WITHIN global.time.fiscal LEVEL global.time.fiscal.fiscal_quarter)= '150'
                                                                BUILD(CLEAR, LOAD, SOLVE))!',
                             parallelism=>0,
                             add_dimensions=>false);
                        end;

                        ---------------------------------------------------------------------------------

                        /* Limit the build (using FOR clause) to the SALES measure in 2006.
                        All objects are built using the complete (C) method. */
                        BEGIN
                        DBMS_CUBE.BUILD(
                        script => '
                        GLOBAL."TIME",
                        GLOBAL.CHANNEL,
                        GLOBAL.CUSTOMER,
                        GLOBAL.PRODUCT,
                        GLOBAL.UNITS_CUBE USING
                        *(*
                        FOR MEASURES(GLOBAL.UNITS_CUBE.SALES)
                        BUILD(LOAD NO SYNCH WHERE GLOBAL.UNITS_FACT.MONTH_ID LIKE ''2006%'')
                        *)',*
                        method => 'C',
                        parallelism => 2);
                        END;


                        .

                        Edited by: Nasar on Jan 30, 2013 2:40 PM
                        • 9. Re: Load Prune
                          Dileep
                          Thanks Nasar. I will try that.