1 Reply Latest reply: Mar 24, 2013 7:35 AM by Nasar-Oracle RSS

    how to create a cube with only part of dimention data

    998795
      Hi,
      I am a newer for Oracle OLAP. i has a very simple fact table with only 3 columns: sname, scity and score. and i created a dimention based on sname and another one based on scity
      and then i created a cube based on the two dimentions. now i has several value on scity, but i only want one value reported on it. Suppose i want to only have value 'CITY1' on the cube when i matain the cube. How should i do? with limit command ? i seam it doesn't work.:(
        • 1. Re: how to create a cube with only part of dimention data
          Nasar-Oracle
          Take a look at the "Mapping Cubes" section
          http://docs.oracle.com/cd/E11882_01/olap.112/e17123/cubes.htm#BGBEEECH

          You can either do JOIN conditions in AWM cube mappings OR apply a filter in your DBMS_CUBE.BUILD ... USING ... FOR <DIM> syntax
          http://docs.oracle.com/cd/E11882_01/appdev.112/e16760/d_cube.htm#CHDJBHEH

          If you search you will find postings on this forum, with lots of examples for DBMS_CUBE.BUILD... USING syntax

          Note that it uses OLAP EXPRESSION syntax
          http://docs.oracle.com/cd/E11882_01/olap.112/e23381/toc.htm

          Understanding DBMS_CUBE.BUILD and OLAP_EXPRESSION syntax will help you a lot.
          .

          Some examples are:

          EXAMPLE 1
          -----------------------
          /* Clear cube data for a Product */
          BEGIN
          DBMS_CUBE.BUILD(Q'!
          "PRICE_COST_CUBE" USING(
          FOR
          "TIME" LEVELS ("TIME"."MONTH"),
          "PRODUCT" WHERE "PRODUCT".DIM_KEY = '48'
          BUILD (SET PRICE_COST_CUBE.PRICE = NULL ))!',
          PARALLELISM=>0,
          ADD_DIMENSIONS=>FALSE);
          END;


          EXAMPLE 2
          -----------------------
          /* 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;




          EXAMPLE 3
          --------------------------
          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;


          EXAMPLE 4
          ----------------------------
          /* Limit the build (using FOR clause) to the SALES measure in 2006. */

          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;