Forum Stats

  • 3,825,758 Users
  • 2,260,558 Discussions
  • 7,896,662 Comments

Discussions

Data Cube Creation Issue

Autumn G.
Autumn G. Member Posts: 8 Blue Ribbon
edited Sep 11, 2019 12:12PM in OLAP

Oracle Version : 12.1.0.2

When I tried to create a data cube using

DECLARE

     event_aw  varchar2(30);

         

BEGIN

     event_aw := dbms_cube.create_mview('DWH', 'DC_CUBE_2_MV',  'build=immediate');

END;

/

I got the following error:

ORA-20332: No dimensions found in materialized view "DWH.DC_CUBE_2_MV". At least one simple column is required in select list and GROUP BY

clause and all key columns must be unique. Aliasing may cause this unique dimension level key violation.

ORA-06512: at "SYS.DBMS_CUBE", line 10875

ORA-06512: at line 5

I already have a data cube created on the same table and the same dimensions (2 dimensions)

the new data cube I am trying to create is just having additional column than the existing one. But it failed.

I don't understand what the error above mean since there are dimensions in the materialized view and a cube already created when it was version of 11.2.0.4

Autumn G.

Answers

  • Ken Chin OLAP Option-Oracle
    Ken Chin OLAP Option-Oracle Member Posts: 133 Employee
    edited Jul 15, 2019 10:21AM

    Is the MV referenced, DC_CUBE_2_MV a valid and existing MV?  Please check by running:

    >ALTER DC_CUBE_2_MV compile

  • Autumn G.
    Autumn G. Member Posts: 8 Blue Ribbon
    edited Jul 15, 2019 11:21AM

    Hi Ken,

    Thanks for you reply. Yes, the MV is valid. I created it right before I tried to create this cube.

  • csperry-Oracle
    csperry-Oracle Member Posts: 9
    edited Jul 15, 2019 3:47PM

    There is a complex query among dimension and mview dictionary views that is not returning any rows and results in the ORA-20332.  I would need to review the dimensions and mview definitions to provide a more helpful answer.   See what you get by adding 'unknownKeyAsDim=TRUE' to the sam_parameter list.

  • Autumn G.
    Autumn G. Member Posts: 8 Blue Ribbon
    edited Jul 16, 2019 4:47PM

    Hi csperry-Oacle,

    Thanks for your reply.  I added the parameter, and I got the same thing.  below are statements about how dimensions, mviews and data cube created and what error we got.

    --  date dimension

    -- DROP dimension dwh.date_dim;

    create dimension dwh.date_dim

    level l_day  is (dwh.date_hierarchy.YYYYDDD)

    level l_week is (dwh.date_hierarchy.WEEK_NUM)

    level l_month is (dwh.date_hierarchy.YYYYMM)

    level l_quarter is (dwh.date_hierarchy.YYYYQ)

    level l_year is (dwh.date_hierarchy.YYYY)

        HIERARCHY date_month_rollup(

        l_day CHILD OF

    l_month CHILD OF

    l_quarter CHILD OF

    l_year    

    )

    HIERARCHY date_week_rollup(

        l_day CHILD OF

    l_week    

    )

    ATTRIBUTE l_day DETERMINES (DAY_DT)

    ATTRIBUTE l_week DETERMINES (WEEK_LAST_DAY, WEEK_FIRST_DAY)

    ATTRIBUTE l_month DETERMINES (MONTH_NAME_ABBR,MONTH_LAST_DAY, MONTH_FIRST_DAY)

    ATTRIBUTE l_quarter DETERMINES (QUARTER_LAST_DAY, QUARTER_FIRST_DAY)

    ;

    -- event dimensions

    --DROP DIMENSION DWH.event_dim;

    CREATE DIMENSION DWH.event_dim

    LEVEL l_event_cd_etype IS (DWH.event_hierarchy.event_cd

                              ,DWH.event_hierarchy.event_type)

    LEVEL l_event  IS (dwh.event_hierarchy.event_cd)

    LEVEL l_category IS (dwh.event_hierarchy.event_category_cd)

      HIERARCHY event_category_rollup(

      l_event_cd_etype CHILD OF

      l_category

      )

      HIERARCHY event_ecode_rollup(

      l_event_cd_etype CHILD OF

      l_event

      )

      ATTRIBUTE l_event_cd_etype determines (utilization_bit)

      ATTRIBUTE l_event determines(event_name, context_cd, action_cd, action_status_cd)

    ;

    CREATE MATERIALIZED VIEW DWH.EVENT_D_MV

    PCTFREE 0  TABLESPACE TS_DWH_DAT

    PARALLEL

    BUILD IMMEDIATE

    REFRESH FAST

      START WITH SYSDATE

      NEXT SYSDATE+1/24

      WITH PRIMARY KEY

    ENABLE QUERY REWRITE

    AS

    SELECT

      EVENT_CD

      ,  TRUNC(sys_extract_utc (EVENT_TSZ)) as period_start_dt

      , 'D' as PERIOD_RU_LEVEL

      , 'L' as ORG_RU_LEVEL

      , event_type

      , COUNT(*) as QUANTITY

      , COUNT(AMOUNT) AS AMOUNT_ITEM_COUNT

      , SUM(AMOUNT) as AMOUNT

      , p_org_id

      , l_loc_id

      , dbms_mview.pmarker(et.rowid) as partition_marker

      FROM DWH.EVENT_GG et

      GROUP BY p_org_id, l_loc_id, event_type,EVENT_CD

           ,TRUNC(sys_extract_utc (EVENT_TSZ)),dbms_mview.pmarker(et.rowid)

    /

     

    CREATE MATERIALIZED VIEW DWH.DC_CUBE_2_MV

    PCTFREE 0  TABLESPACE TS_DWH_RU_DAT

    PARALLEL

    BUILD IMMEDIATE

    REFRESH FAST

      START WITH SYSDATE

      NEXT SYSDATE+1/24

      WITH PRIMARY KEY

    ENABLE QUERY REWRITE

    AS

    SELECT

        eh.EVENT_CD

      , eh.event_type

      , dh.YYYYMM as period_num

      , e.p_org_id

      , COUNT(*) as period_item_CNT

      , COUNT(QUANTITY) AS QUANTITY_CNT

      , SUM(QUANTITY) AS QUANTITY

      , COUNT(AMOUNT_ITEM_COUNT) AS AMOUNT_ITEM_COUNT_CNT

      , SUM(AMOUNT_ITEM_COUNT) AS AMOUNT_ITEM_COUNT

      , COUNT(AMOUNT) AS AMOUNT_CNT

      , SUM(AMOUNT) as AMOUNT

      FROM DWH.EVENT_D_MV e

      JOIN DWH.date_hierarchy  dh ON e.period_start_dt = dh.day_dt

      JOIN DWH.event_hierarchy eh

        ON e.event_cd = eh.event_cd AND e.event_type=eh.event_type

    WHERE eh.utilization_bit = 1

      GROUP BY

        eh.EVENT_CD

      , eh.event_type

      , dh.YYYYMM

      , e.p_org_id

    /

       

    ALTER SESSION SET CURRENT_SCHEMA=DWH;

    DECLARE

         event_aw  varchar2(30);

             

    BEGIN

         event_aw := dbms_cube.create_mview('DWH', 'DC_CUBE_2_MV',

                    'build=immediateunknownKeyAsDim=TRUE');

    END;

    /

    SQL> select systimestamp from dual;

    SYSTIMESTAMP

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

    16-JUL-19 04.32.22.637244 PM -04:00

    SQL> DECLARE

      2       event_aw  varchar2(30);

      3

      4  BEGIN

      5       event_aw := dbms_cube.create_mview('DWH', 'DC_CUBE_2_MV',

      6                  'build=immediateunknownKeyAsDim=TRUE');

      7  END;

      8  /

    DECLARE

    *

    ERROR at line 1:

    ORA-20332: No dimensions found in materialized view "DWH.DC_CUBE_2_MV". At least one simple column is required in select list and GROUP BY clause and

    all key columns must be unique. Aliasing may cause this unique dimension level key violation.

    ORA-06512: at "SYS.DBMS_CUBE", line 10875

    ORA-06512: at line 5

  • csperry-Oracle
    csperry-Oracle Member Posts: 9
    edited Jul 19, 2019 10:28PM

    The exception is due to the ANSI join syntax in the "DC_CUBE_2_MV" query definition.  The data dictionary views for materialized views do not contain metadata required by dbms_cube.create_mview() when mview query contains ANSI style joins.  Recompose the mview query to use Oracle join syntax to avoid the ORA-20332 exception when calling dbms_cube.create_mview.

    For example, the query in DWH.DC_CUBE_2_MV can be replaced with one with out ANSI joins.

    SELECT

        eh.EVENT_CD

      , eh.event_type

      , dh.YYYYMM as period_num

      , e.p_org_id

      , COUNT(*) as period_item_CNT

      , COUNT(QUANTITY) AS QUANTITY_CNT

      , SUM(QUANTITY) AS QUANTITY

      , COUNT(AMOUNT_ITEM_COUNT) AS AMOUNT_ITEM_COUNT_CNT

      , SUM(AMOUNT_ITEM_COUNT) AS AMOUNT_ITEM_COUNT

      , COUNT(AMOUNT) AS AMOUNT_CNT

      , SUM(AMOUNT) as AMOUNT

      FROM

       DWH.EVENT_D_MV e,

       DWH.date_hierarchy  dh,

       DWH.event_hierarchy eh

    WHERE

          eh.utilization_bit = 1

    AND e.period_start_dt = dh.day_dt 

    AND e.event_cd = eh.event_cd

    AND e.event_type=eh.event_type

      GROUP BY

        eh.EVENT_CD

      , eh.event_type

      , dh.YYYYMM

      , e.p_org_id

    Hope this helps.

    Autumn G.Autumn G.
  • Autumn G.
    Autumn G. Member Posts: 8 Blue Ribbon
    edited Sep 11, 2019 12:12PM

    Thank you! your solution that using Oracle join instead of ANSI join does work. the issue is resolved