Data Cube Creation Issue

Autumn G.

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

Ken Chin OLAP Option-Oracle

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.

Hi Ken,

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

csperry-Oracle

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.

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

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.

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