Oracle 数据库维的建立
SQL> select table_name from dict where table_name like '%DBA%DIM%';
TABLE_NAME
------------------------------
DBA_DIMENSIONS
DBA_DIM_LEVELS
DBA_DIM_LEVEL_KEY
DBA_DIM_ATTRIBUTES
DBA_DIM_HIERARCHIES
DBA_DIM_CHILD_OF
DBA_DIM_JOIN_KEY
select * from USER_DIMENSIONS;
select dbms_metadata.get_ddl('DIMENSION','TIMES_DIM','SH') FROM DUAL;CREATE DIMENSION "SH"."TIMES_DIM"
LEVEL "DAY" IS ("TIMES"."TIME_ID")
LEVEL "MONTH" IS ("TIMES"."CALENDAR_MONTH_DESC")
LEVEL "QUARTER" IS ("TIMES"."CALENDAR_QUARTER_DESC")
LEVEL "YEAR" IS ("TIMES"."CALENDAR_YEAR")
LEVEL "FIS_WEEK" IS ("TIMES"."WEEK_ENDING_DAY")
LEVEL "FIS_MONTH" IS ("TIMES"."FISCAL_MONTH_DESC")
LEVEL "FIS_QUARTER" IS ("TIMES"."FISCAL_QUARTER_DESC")
LEVEL "FIS_YEAR" IS ("TIMES"."FISCAL_YEAR")
HIERARCHY "CAL_ROLLUP" ("DAY" CHILD OF "MONTH" CHILD OF "QUARTER" CHILD OF "YEAR")
HIERARCHY "FIS_ROLLUP" ("DAY" CHILD OF "FIS_WEEK" CHILD OF "FIS_MONTH"
CHILD OF "FIS_QUARTER" CHILD OF "FIS_YEAR")
ATTRIBUTE "DAY" DETERMINES "TIMES"."DAY_NUMBER_IN_WEEK"
ATTRIBUTE "DAY" DETERMINES "TIMES"."CALENDAR_WEEK_NUMBER"
ATTRIBUTE "DAY" DETERMINES "TIMES"."DAY_NUMBER_IN_MONTH"
ATTRIBUTE "DAY" DETERMINES "TIMES"."DAY_NAME"
ATTRIBUTE "MONTH" DETERMINES "TIMES"."CALENDAR_MONTH_DESC"
ATTRIBUTE "MONTH" DETERMINES "TIMES"."END_OF_CAL_MONTH"
ATTRIBUTE "MONTH" DETERMINES "TIMES"."DAYS_IN_CAL_MONTH"
ATTRIBUTE "MONTH" DETERMINES "TIMES"."CALENDAR_MONTH_NAME"
ATTRIBUTE "MONTH" DETERMINES "TIMES"."CALENDAR_MONTH_NUMBER"
ATTRIBUTE "QUARTER" DETERMINES "TIMES"."CALENDAR_QUARTER_DESC"
ATTRIBUTE "QUARTER" DETERMINES "TIMES"."END_OF_CAL_QUARTER"
ATTRIBUTE "QUARTER" DETERMINES "TIMES"."DAYS_IN_CAL_QUARTER"
ATTRIBUTE "QUARTER" DETERMINES "TIMES"."CALENDAR_QUARTER_NUMBER"
ATTRIBUTE "YEAR" DETERMINES "TIMES"."CALENDAR_YEAR"
ATTRIBUTE "YEAR" DETERMINES "TIMES"."END_OF_CAL_YEAR"
ATTRIBUTE "YEAR" DETERMINES "TIMES"."DAYS_IN_CAL_YEAR"
ATTRIBUTE "FIS_WEEK" DETERMINES "TIMES"."WEEK_ENDING_DAY"
ATTRIBUTE "FIS_WEEK" DETERMINES "TIMES"."FISCAL_WEEK_NUMBER"
ATTRIBUTE "FIS_MONTH" DETERMINES "TIMES"."FISCAL_MONTH_DESC"
ATTRIBUTE "FIS_MONTH" DETERMINES "TIMES"."END_OF_FIS_MONTH"
ATTRIBUTE "FIS_MONTH" DETERMINES "TIMES"."DAYS_IN_FIS_MONTH"
ATTRIBUTE "FIS_MONTH" DETERMINES "TIMES"."FISCAL_MONTH_NAME"
ATTRIBUTE "FIS_MONTH" DETERMINES "TIMES"."FISCAL_MONTH_NUMBER"
ATTRIBUTE "FIS_QUARTER" DETERMINES "TIMES"."FISCAL_QUARTER_DESC"
ATTRIBUTE "FIS_QUARTER" DETERMINES "TIMES"."END_OF_FIS_QUARTER"
ATTRIBUTE "FIS_QUARTER" DETERMINES "TIMES"."DAYS_IN_FIS_QUARTER"
ATTRIBUTE "FIS_QUARTER" DETERMINES "TIMES"."FISCAL_QUARTER_NUMBER"
ATTRIBUTE "FIS_YEAR" DETERMINES "TIMES"."FISCAL_YEAR"
ATTRIBUTE "FIS_YEAR" DETERMINES "TIMES"."END_OF_FIS_YEAR"
ATTRIBUTE "FIS_YEAR" DETERMINES "TIMES"."DAYS_IN_FIS_YEAR";验证维
col ENTITY_OWNER for a5
col DESCRIPTOR_ID for 999
col ENTITY_NAME for a10
col CHILD_ENTITY_NAME for a23
col SECONDARY_CHILD_ENTITY_NAME for a30
select * from dba_olap_entity_desc_uses where entity_name = 'TIMES_DIM';
col DESCRIPTOR_ID for 999
col DESCRIPTOR_VALUE for a23
col DESCRIPTOR_TYPE for a40
select DESCRIPTOR_ID,DESCRIPTOR_VALUE,DESCRIPTOR_TYPE from dba_olap_descriptors;--------------------建立自己的维-----------------
CREATE DIMENSION "SH"."TIMES_DIM2"
LEVEL "MONTH" IS ("TIMES"."CALENDAR_MONTH_DESC")
LEVEL "YEAR" IS ("TIMES"."CALENDAR_YEAR")
HIERARCHY "CAL_ROLLUP2" ("MONTH" CHILD OF "YEAR")
ATTRIBUTE "MONTH" DETERMINES "TIMES"."CALENDAR_MONTH_DESC"
ATTRIBUTE "YEAR" DETERMINES "TIMES"."CALENDAR_YEAR";
@%oracle_home%\rdbms\admin\utldim.sql
-----------------------------------------------其他的维-----------------------
set long 10000
select dbms_metadata.get_ddl('DIMENSION','CUSTOMERS_DIM','SH') FROM DUAL;
CREATE DIMENSION "SH"."CUSTOMERS_DIM"
LEVEL "CUSTOMER" IS ("CUSTOMERS"."CUST_ID")
LEVEL "CITY" IS ("CUSTOMERS"."CUST_CITY_ID")
LEVEL "STATE" IS ("CUSTOMERS"."CUST_STATE_PROVINCE_ID")
LEVEL "COUNTRY" IS ("COUNTRIES"."COUNTRY_ID")
LEVEL "SUBREGION" IS ("COUNTRIES"."COUNTRY_SUBREGION_ID")
LEVEL "REGION" IS ("COUNTRIES"."COUNTRY_REGION_ID")
LEVEL "GEOG_TOTAL" IS ("COUNTRIES"."COUNTRY_TOTAL_ID")
LEVEL "CUST_TOTAL" IS ("CUSTOMERS"."CUST_TOTAL_ID")
HIERARCHY "CUST_ROLLUP" ("CUSTOMER" CHILD OF "CITY" CHILD OF "STATE" CHILD OF "CUST_TOTAL")
HIERARCHY "GEOG_ROLLUP" ("CUSTOMER" CHILD OF "CITY" CHILD OF "STATE" CHILD OF "COUNTRY" CHILD OF "SUBREGION" CHILD OF "REGION" CHILD OF "GEOG_TOTAL" JOIN KEY ("CUSTOMERS"."COUNTRY_ID") REFERENCES "COUNTRY")
ATTRIBUTE "CUSTOMER" LEVEL "CUSTOMER" DETERMINES "CUSTOMERS"."CUST_FIRST_NAME"
ATTRIBUTE "CUSTOMER" LEVEL "CUSTOMER" DETERMINES "CUSTOMERS"."CUST_EMAIL"
ATTRIBUTE "CUSTOMER" LEVEL "CUSTOMER" DETERMINES "CUSTOMERS"."CUST_MAIN_PHONE_NUMBER"
ATTRIBUTE "CUSTOMER" LEVEL "CUSTOMER" DETERMINES "CUSTOMERS"."CUST_POSTAL_CODE"
ATTRIBUTE "CUSTOMER" LEVEL "CUSTOMER" DETERMINES "CUSTOMERS"."CUST_STREET_ADDRESS"
ATTRIBUTE "CUSTOMER" LEVEL "CUSTOMER" DETERMINES "CUSTOMERS"."CUST_CREDIT_LIMIT"
ATTRIBUTE "CUSTOMER" LEVEL "CUSTOMER" DETERMINES "CUSTOMERS"."CUST_INCOME_LEVEL"
ATTRIBUTE "CUSTOMER" LEVEL "CUSTOMER" DETERMINES "CUSTOMERS"."CUST_YEAR_OF_BIRTH"
ATTRIBUTE "CUSTOMER" LEVEL "CUSTOMER" DETERMINES "CUSTOMERS"."CUST_MARITAL_STATUS"
ATTRIBUTE "CUSTOMER" LEVEL "CUSTOMER" DETERMINES "CUSTOMERS"."CUST_GENDER"
ATTRIBUTE "CUSTOMER" LEVEL "CUSTOMER" DETERMINES "CUSTOMERS"."CUST_LAST_NAME"
ATTRIBUTE "CITY" LEVEL "CITY" DETERMINES "CUSTOMERS"."CUST_CITY"
ATTRIBUTE "STATE" LEVEL "STATE" DETERMINES "CUSTOMERS"."CUST_STATE_PROVINCE"
ATTRIBUTE "COUNTRY" LEVEL "COUNTRY" DETERMINES "COUNTRIES"."COUNTRY_NAME"
ATTRIBUTE "COUNTRY" LEVEL "COUNTRY" DETERMINES "COUNTRIES"."COUNTRY_ISO_CODE"
ATTRIBUTE "SUBREGION" LEVEL "SUBREGION" DETERMINES "COUNTRIES"."COUNTRY_SUBREGION"
ATTRIBUTE "REGION" LEVEL "REGION" DETERMINES "COUNTRIES"."COUNTRY_REGION"
ATTRIBUTE "GEOG_TOTAL" LEVEL "GEOG_TOTAL" DETERMINES "COUNTRIES"."COUNTRY_TOTAL"
ATTRIBUTE "CUST_TOTAL" LEVEL "CUST_TOTAL" DETERMINES "CUSTOMERS"."CUST_TOTAL" ;