REM
REM File: cwm2mrvw.sql
REM
REM The CwM2$ views which present the metadata information
REM in a way that is expected for the OLAP Services OLAP API 
REM metadata reader
REM Each view name is always prefixed with 'all$olap2_mr'
REM

REM  06/24/08 glyon      bug 7204558: eliminate references to sys.user$
REM  11/06/01 mstasiew   mstasiew_txn100603
REM  11/15/01 mstasiew   mstasiew_txn100734
REM
REM Hierarchical Dimension Views for CwM2 OLAP API MetadataReader
REM

REM all$olap2_mr_hierdims
create or replace view olapsys.all$olap2_mr_hierdims as
select distinct d.owner owner,
       d.dimension_name dimension_name,
       nvl(d.plural_name, d.dimension_name) plural_name,
       nvl(d.display_name, d.dimension_name) display_name,
       nvl(d.description, d.dimension_name) description,
       d.default_display_hierarchy default_display_hierarchy,
       NVL(EDU_DES.DESCRIPTOR_VALUE, 'Other') descriptor_value
from olapsys.all$olap2udimensions d,
     olapsys.all$olap2udim_hierarchies h,
    (SELECT EDU.ENTITY_OWNER, EDU.ENTITY_NAME, DES.DESCRIPTOR_VALUE
    FROM olapsys.all$olap2uentity_desc_uses EDU, ALL_OLAP_DESCRIPTORS DES
    WHERE EDU.DESCRIPTOR_ID = DES.DESCRIPTOR_ID
    AND DES.DESCRIPTOR_TYPE = 'Dimension Type' ) EDU_DES
 WHERE D.OWNER = EDU_DES.ENTITY_OWNER (+)
 AND D.DIMENSION_NAME = EDU_DES.ENTITY_NAME (+)
 and d.invalid = 'N' and
 d.owner = h.owner and
 d.dimension_name = h.dimension_name
with read only
/

REM
REM List Dimension Views for CwM2 OLAP API MetadataReader
REM
create or replace view olapsys.all$olap2ulistdim_key_col_uses as 
    select distinct u.username owner,
            d.name dimension_name,
 dim.pluralname plural_name,
 dim.displayname display_name,
 dim.description description,
 decode(o.status, 5, 'Y', 'N') invalid,
            tu.username table_owner,
            tn.name table_name,
            c.name column_name,
            k.keypos# position
      from dba_users u,
           sys.obj$ d,
           sys.dimlevel$ l,
           sys.dimlevelkey$ k,
           sys.col$ c,
           sys.obj$ tn,
           dba_users tu,
           cwm$dimension dim,
           sys.obj$ o,
           sys.dim$ sd
      where u.user_id = d.owner# and
            d.type# = 43 and
            (cwm$util.dimension_tables_visible(d.obj#) = 'Y'
             OR EXISTS 
                (select null from v$enabledprivs
                 where priv_number in (-47, -215, -216, -217))) and
            o.obj# = sd.obj# and
            sd.obj# = dim.irid (+) and
            d.obj# = dim.irid and
            d.obj# = l.dimobj# and
            l.dimobj# = k.dimobj# and
            l.levelid# = k.levelid# and
            k.detailobj# = c.obj# and
            k.col# = c.col# and
            tn.obj# = c.obj# and
            tu.user_id = tn.owner# and
            (not exists (select * from cwm$hierarchy h
            where dim.irid = h.dimension_irid))
UNION ALL
select
 distinct  d.owner owner,
       d.name dimension_name,
 d.pluralname plural_name,
 d.displayname display_name,
 d.description description,
 d.invalid invalid,
       u.username table_owner,
       o.name table_name,
       c.name column_name,
       dhlm.position position
from olapsys.cwm2$dimension d,
     olapsys.cwm2$hierarchy h,
     olapsys.cwm2$level l,
     olapsys.cwm2$hierlevelrel hlr,
     olapsys.cwm2$dimhierlvlmap dhlm,
     dba_users u,
     sys.obj$ o,
     sys.col$ c
where h.dimension_irid = d.irid and
      h.hidden = 'Y' and
      h.irid = hlr.hierarchy_irid and
      l.irid = hlr.childlevel_irid and
      dhlm.dimhierlvl_irid = hlr.irid and
      dhlm.object_id = o.obj# and
      dhlm.column_id = c.col# and
      o.obj# = c.obj# and
      o.owner# = u.user_id and
       d.invalid = 'N' and
       (cwm2$security.dimension_tables_visible(d.irid) = 'Y'
       OR EXISTS (select null from v$enabledprivs
                  where priv_number in (-47, -215, -216, -217)))
with read only
/


REM all$olap2_mr_listdims
create or replace view olapsys.all$olap2_mr_listdims as
select distinct d.owner owner,
       d.dimension_name dimension_name,
       nvl(d.plural_name, d.dimension_name) plural_name,
       nvl(d.display_name, d.dimension_name) display_name,
       nvl(d.description, d.dimension_name) description,
       NVL(EDU_DES.DESCRIPTOR_VALUE, 'Other') descriptor_value,
       d.table_owner table_owner,
       d.table_name table_name,
       d.column_name column_name
from olapsys.all$olap2ulistdim_key_col_uses d,
(SELECT EDU.ENTITY_OWNER, EDU.ENTITY_NAME, DES.DESCRIPTOR_VALUE
    FROM olapsys.all$olap2uentity_desc_uses EDU, ALL_OLAP_DESCRIPTORS DES
    WHERE EDU.DESCRIPTOR_ID = DES.DESCRIPTOR_ID
    AND DES.DESCRIPTOR_TYPE = 'Dimension Type' ) EDU_DES
where
 d.invalid = 'N' and
 D.OWNER = EDU_DES.ENTITY_OWNER (+)
 AND D.DIMENSION_NAME = EDU_DES.ENTITY_NAME (+)
with read only
/



REM
REM Attribute Mappings for CwM2 OLAP API MetadataReader
REM
rem all$olap2_mr_attrmaps
create or replace view olapsys.all$olap2_mr_attrmaps as
select lam.owner owner,
 lam.dimension_name dimension_name,
 lam.hierarchy_name hierarchy_name,
 lam.level_name level_name,
 lam.attribute_name attribute_name,
 lam.table_owner table_owner,
 lam.	table_name table_name,
 lam.column_name column_name,
 (case when lam.dtype = 'NUMBER' then 0
       when lam.dtype = 'DOUBLE' then 5
       when lam.dtype = 'FLOAT' then 4
       when lam.dtype = 'DATE' then 7
       else 1 end) 
         data_type
from 
     olapsys.all$olap2udim_level_attr_maps lam
with read only
/


CREATE OR REPLACE VIEW olapsys.all$olapmr_dim_levels_keymaps AS SELECT /*+ORDERED*/
  u.username                             owner,
  d.name                             dimension_name,
  h.hiername                         hierarchy_name,
  dl.levelname                       level_name,
  NVL(lev.displayname,dl.levelname)  display_name,
  NVL(lev.description,dl.levelname)  description,
  hl.pos#                            pos,
  tu.username table_owner,
  t.name table_name,
  c.name column_name,
  k.keypos# column_position
FROM 
  sys.obj$          d, 
  dba_users         u, 
  sys.hier$         h, 
  sys.hierlevel$    hl, 
  sys.dimlevel$     dl,
  sys.dimlevelkey$  k, 
  sys.obj$          t, 
  dba_users         tu, 
  cwm$level         lev,
  sys.col$          c
WHERE 
  d.type#      = 43 AND              /* DIMENSION */
 (   cwm$util.dimension_tables_visible(d.obj#) = 'Y'
     OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */
       (SELECT null FROM v$enabledprivs 
        WHERE priv_number IN (-47,-215,-216,-217)))
AND
  u.user_id      = d.owner# AND
  d.obj#       = h.dimobj# AND
  d.obj#       = hl.dimobj# AND
  h.hierid#    = hl.hierid# AND
  hl.dimobj#   = dl.dimobj# AND
  hl.levelid#  = dl.levelid# AND
  dl.dimobj#   = k.dimobj# AND
  dl.levelid#  = k.levelid# AND
  k.detailobj# = t.obj# AND
  t.owner#     = tu.user_id AND
  dl.dimobj#   = lev.dimension_irid (+) AND
  dl.levelname = lev.physicalname (+)
AND k.detailobj# = c.obj#
AND k.col# = c.col#
/



CREATE OR REPLACE VIEW olapsys.all$olapmr2_dim_levels_keymaps AS
select
       d.owner owner,
       d.name dimension_name,
       h.name hierarchy_name,
       l.name level_name,
       NVL(L.DISPLAYNAME, L.NAME) display_name,
       NVL(L.DESCRIPTION, L.NAME) description,
       hlr.leveldepth pos,
       u.username table_owner,
       o.name table_name,
       c.name column_name,
       dhlm.position column_position
from olapsys.cwm2$dimension d,
     olapsys.cwm2$hierarchy h,
     olapsys.cwm2$level l,
     olapsys.cwm2$hierlevelrel hlr,
     olapsys.cwm2$dimhierlvlmap dhlm,
     dba_users u,
     sys.obj$ o,
     sys.col$ c
where h.dimension_irid = d.irid and
      h.irid = hlr.hierarchy_irid and
      l.irid = hlr.childlevel_irid and
      dhlm.dimhierlvl_irid = hlr.irid and
      dhlm.object_id = o.obj# and
      dhlm.column_id = c.col# and
      o.obj# = c.obj# and
      o.owner# = u.user_id and
       d.invalid = 'N' and
       (cwm2$security.dimension_tables_visible(d.irid) = 'Y'
       OR EXISTS (select null from v$enabledprivs
                  where priv_number in (-47, -215, -216, -217)))
with read only
/




create or replace view olapsys.all$olap2_mr_lkupkeymaps as
select owner owner,
       dimension_name dimension_name,
       hierarchy_name hierarchy_name,
       level_name level_name,
       display_name display_name,
       description description,
       pos hierarchy_position,
       table_owner table_owner,
       table_name table_name,
       column_name column_name,
       column_position column_position 
from olapsys.all$olapmr2_dim_levels_keymaps
union all
select owner owner,
       dimension_name dimension_name,
       hierarchy_name hierarchy_name,
       level_name level_name,
       display_name display_name,
       description description,
       pos hierarchy_position,
       table_owner table_owner,
       table_name table_name,
       column_name column_name,
       column_position column_position 
from olapsys.all$olapmr_dim_levels_keymaps
with read only
/







create or replace view olapsys.all$olap2_mr_9i2ruFctTblKyMaps as
select flu.owner owner,
           flu.cube_name cube_name,
           flu.dim_hier_combo_id dim_hier_combo_id,
           flu.dimension_owner dimension_owner,
           flu.dimension_name dimension_name,
           flu.hierarchy_name hierarchy_name,
           flu.fact_table_owner fact_table_owner,
           flu.fact_table_name fact_table_name,
          flu.column_name column_name,
          ftg.column_name gid_column_name
   from olapsys.all$olap2ufact_level_uses flu,
        olapsys.all$olap2_mr_lkupkeymaps lkm,
        olapsys.all$olap2ufact_table_gid ftg
   where flu.dim_hier_combo_id <> 0 and
     flu.dimension_keymap_type = 'RU' and
     flu.dimension_owner = lkm.owner and
     flu.dimension_name = lkm.dimension_name and
     flu.hierarchy_name = lkm.hierarchy_name and
     flu.level_name = lkm.level_name and
     flu.owner = ftg.owner
     and flu.cube_name = ftg.cube_name
     and flu.dimension_owner = ftg.dimension_owner
     and flu.dimension_name = ftg.dimension_name
     and flu.hierarchy_name = ftg.hierarchy_name
     and flu.dim_hier_combo_id = ftg.dim_hier_combo_id
     and flu.fact_table_owner = ftg.fact_table_owner
     and flu.fact_table_name = ftg.fact_table_name
  order by flu.owner asc, flu.cube_name asc, flu.dim_hier_combo_id asc,
    flu.dimension_owner asc, flu.dimension_name asc, flu.hierarchy_name asc,
    lkm.hierarchy_position desc
/


create or replace view olapsys.all$olap2_mr_9i2FactTblKeyMaps as
select flu.owner owner,
       flu.cube_name cube_name,
       flu.dim_hier_combo_id dim_hier_combo_id,
       flu.dimension_owner dimension_owner,
       flu.dimension_name dimension_name,
       flu.hierarchy_name hierarchy_name,
       flu.fact_table_owner fact_table_owner,
       flu.fact_table_name fact_table_name,
       flu.column_name column_name,
       ftg.column_name gid_column_name,
      (CASE WHEN dimension_keymap_type = 'ET' THEN 1
       WHEN dimension_keymap_type = 'LL' THEN 2
       ELSE 0 END) dimension_keymap_type
from olapsys.all$olap2ufact_level_uses flu,
     olapsys.all$olap2ufact_table_gid ftg
where flu.dim_hier_combo_id <> 0
and flu.dimension_keymap_type <> 'RU'
and flu.owner = ftg.owner (+)
and flu.cube_name = ftg.cube_name (+)
and flu.dimension_owner = ftg.dimension_owner (+)
and flu.dimension_name = ftg.dimension_name (+)
and flu.hierarchy_name = ftg.hierarchy_name (+)
and flu.dim_hier_combo_id = ftg.dim_hier_combo_id (+)
and flu.fact_table_owner = ftg.fact_table_owner (+)
and flu.fact_table_name = ftg.fact_table_name (+)
order by flu.owner, flu.cube_name, flu.dim_hier_combo_id
/

create or replace view olapsys.all$olap2_mr_9i1FactTblKeyMaps as
select distinct owner owner,
       cube_name cube_name,
       dimension_owner dimension_owner,
       dimension_name dimension_name,
       fact_table_owner fact_table_owner,
       fact_table_name fact_table_name,
       column_name column_name,
      (CASE WHEN dimension_keymap_type = 'ET' THEN 1
       WHEN dimension_keymap_type = 'LL' THEN 2
       ELSE 0 END) dimension_keymap_type,
       position column_position
from olapsys.all$olap2ufact_level_uses
where dim_hier_combo_id = 0    
order by owner, cube_name
/

create or replace view olapsys.all$olap2_mr_9i1FactTblFctMaps as
select cmm.owner owner,
 cmm.cube_name cube_name,
 cmm.measure_name measure_name,
 cmm.fact_table_owner fact_table_owner,
 cmm.fact_table_name fact_table_name,
 cmm.column_name column_name,
 (case when c.data_type = 'NUMBER' then 0
       when c.data_type = 'DATE' then 7
       else 1 end) 
         data_type
from olapsys.all$olap2ucube_measure_maps cmm,
     all_olap_columns c
where cmm.dim_hier_combo_id = 0
 and cmm.fact_table_owner = c.owner
 and cmm.fact_table_name = c.table_name
 and cmm.column_name = c.column_name
order by cmm.owner, cmm.cube_name
/






create or replace view olapsys.all$olap2_mr_9i2FactTblFctMaps as
select cmm.owner owner,
 cmm.cube_name cube_name,
 cmm.measure_name measure_name,
 cmm.dim_hier_combo_id dim_hier_combo_id,
 cmm.fact_table_owner fact_table_owner,
 cmm.fact_table_name fact_table_name,
 cmm.column_name column_name,
 (case when c.data_type = 'NUMBER' then 0
       when c.data_type = 'DATE' then 7
       else 1 end) 
         data_type
from olapsys.all$olap2ucube_measure_maps cmm,
     all_olap_columns c
where cmm.dim_hier_combo_id <> 0
 and cmm.fact_table_owner = c.owner
 and cmm.fact_table_name = c.table_name
 and cmm.column_name = c.column_name
order by cmm.owner, cmm.cube_name, cmm.dim_hier_combo_id 
/

create or replace view olapsys.all$olap2_mr_dimhiers as
select
 h.owner owner,
 h.dimension_name dimension_name,
 h.hierarchy_name hierarchy_name,
 h.display_name display_name,
 h.description description,
 h.solved_code solved_code,
 'Y' is_default 
from olapsys.all$olap2udimensions d,
     olapsys.all$olap2udim_hierarchies h
where d.owner = h.owner and d.dimension_name = h.dimension_name
and d.default_display_hierarchy = h.hierarchy_name
union
select
 h.owner owner,
 h.dimension_name dimension_name,
 h.hierarchy_name hierarchy_name,
 h.display_name display_name,
 h.description description,
 h.solved_code solved_code,
 'N' is_default
from olapsys.all$olap2udim_hierarchies h
where not exists (select * from olapsys.all$olap2udimensions d
where d.owner = h.owner and d.dimension_name = h.dimension_name
and d.default_display_hierarchy = h.hierarchy_name)
/



rem MeasureDimensionView

create or replace view olapsys.all$olap2_mr_measdimview as
select
 'M_' || owner || '.' || cube_name || '.' || measure_name measid,
 display_name display_name,
 description description
from olapsys.all$olap2ucube_measures
with read only
/