2 Replies Latest reply: Sep 26, 2011 4:08 AM by DavidGreenfield RSS

    DBMS CUBE BUILD

    851147
      Hi All

      I have been maintaining my cube till now using the analytical workspace manager.

      I now want to give it a try using the DBMS_CUBE.BUILD sql script.

      Is the following script right ?

      BEGIN
      DBMS_CUBE.BUILD
      (
      'MY_SCHEMA.MY_CUBE USING (CLEAR VALUES,LOAD SYNCH,SOLVE)',
      'C', -- refresh method
      false, -- refresh after errors
      32, -- parallelism
      true, -- atomic refresh
      true, -- automatic order
      false, -- add dimensions
           'CUBE_DATA_REFRESH' -- identify job
      );
      END;

      Also if I specify 'C' as my refresh method and do not specify CLEAR VALUES will it still clear the values that are already existing in the cube ?

      Edited by: CelestialCitizen on Sep 12, 2011 9:39 AM

      Edited by: CelestialCitizen on Sep 12, 2011 9:52 AM
        • 1. Re: DBMS CUBE BUILD
          Thomas-cts
          Hej,

          I would recommend generating the load script with AWM and save it as file - this way you can include it in a PL/SQL package and run as OEM job for example.
          If you have likelihood for updated/changed dimensions, I would always include them in the load script.
          Also you avoid typos, which may cause your job to fail...

          Manage your cube - maintain cube and process all steps as usual, but don`t submit the job, just save it as file (.sql)
          Open that file in SQL Developer for example and copy the syntax and paste it in your job package.

          If you need further assistance or an example maybe, drop me a line.

          best regards,
          Thomas

          Edited by: Th**** on 14-Sep-2011 07:57
          • 2. Re: DBMS CUBE BUILD
            DavidGreenfield
            There are four variants of the CLEAR command.

            <li>CLEAR VALUES. This will clear everything from your cube, including both leaf (i.e. loaded) and aggregate cells. This will happen regardless of the refresh method you choose.

            <li>CLEAR LEAVES. This will clear the leaf (i.e. loaded) values from your cube, but not the aggregated values. This will happen regardless of the refresh method you choose.

            <li>CLEAR AGGREGATES. This will clear the aggregated values from your cube, but will not touch the loaded values. This will happen regardless of the refresh method you choose.

            <li>CLEAR. This will behave differently depending on the refresh method you choose. If you specify 'C', for complete, then it will behave like CLEAR VALUES. If you specify any other method (e.g. 'S' for fast solve or '?' for force), then it will behave like CLEAR LEAVES.

            If you do not include any CLEAR command in your cube script, then no values will be cleared. This will happen regardless of the refresh method you choose. Specifically, then, the following script would not remove data from the cube even if the corresponding fact tables are empty.
            BEGIN
             DBMS_CUBE.BUILD
             (
              'MY_SCHEMA.MY_CUBE USING (LOAD SYNCH,SOLVE)',
              'C', -- refresh method
              false, -- refresh after errors
              32, -- parallelism
              true, -- atomic refresh
              true, -- automatic order
              false, -- add dimensions
              'CUBE_DATA_REFRESH' -- identify job
             );
            END;
            The SYNCH and NO SYNCH options on the LOAD command only mean anything for dimension loads.