Oracle英文版培訓(xùn)課件之Data Warehouse：Les10

DimensionsObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:CreatedimensionsValidatedimensiondataViewdimensiondefinitioninformationWhatAreDimensions?DatadictionarystructuresthatdefinehierarchiesbetweenpairsofcolumnsetsUsedwithmaterializedviewsforqueryrewriteSupersetofreferentialconstraints:Normalizeddimensions:Eachchild-siderowjoinswithoneandonlyoneparent-siderow.Denormalizeddimensions:Child-sidecolumnsuniquelydeterminetheparent-sidecolumns.Neverenforced,butcanbevalidatedEnableadditionalqueryrewriteswithouttheuseofconstraintsDimensionsandHierarchiesMONTH_NAMEWeekSeasonALLLevelkeyAdditionalattributeHierarchyCALENDAR_ROLLUPMONTHLevelkeyattributeYearQuarterSales

dateMonthTIME_DIMdimensionFullNotesPageDimensionTable:ExampleSQL>SELECT*FROMtime;TIME_IDMONTHMONTH_NAMQUARTERYEAR

01-JAN-02M1_02January02Q1_02200202-JAN-02M1_02January02Q1_02200203-JAN-02M1_02January02Q1_02200204-MAR-02M2_02March02Q1_022002...30-DEC-02M12_02December02Q4_02200231-DEC-02M12_02December02Q4_022002...Tableusedinexamplesinthelesson:BenefitsofDimensionsSELECTyear,sum(amount_sold)ASsumamt

FROMtimet,saless

WHEREt.time_id=s.time_idGROUPBYyear;SELECTyear,sum(sumamt)

FROMsumsalesmv,(SELECTDISTINCTmonth,yearFROMtimes)vWHEREv.month=mv.month

GROUPBYyear;CREATEMATERIALIZEDVIEWsumsalesASSELECTmonth,sum(amount_sold)ASsumamt

FROMtimet,saless

WHEREt.time_id=s.time_idGROUPBYmonth;MaterializedviewcreatedUserexecutesqueryQueryrewrittenDefiningDimensionsandHierarchiesCREATEDIMENSIONtime_dim

LEVELsdateIStime.sdate

LEVELmonthIStime.month

LEVELqtrIStime.quarter

LEVELyrIStime.year

HIERARCHYcalendar_rollup(

sdateCHILDOFmonthCHILDOFqtrCHILDOFyr)ATTRIBUTEmonthDETERMINEStime.month_name;yrqtrmonthsdateSpecifyingtheSKIPWHENNULLClauseCREATEDIMENSIONSALE_DATA_DIM

LEVELCUSTOMERISSALE_DATA.CUST_ID

LEVELCITYISSALE_DATA.CITY

LEVELSTATEISSALE_DATA.STATESKIPWHENNULLLEVELCOUNTRYISSALE_DATA.COUNTRY

HIERARCHYGEOG_ROLLUP(CUSTOMERCHILDOFCITYCHILDOFSTATECHILDOFCOUNTRY)CUST_IDCITYSTATECOUNTRYWashington,D.C.USA20145NostateFullNotesPageCreatingDimensionsUsing

EnterpriseManagercreate_dimension.gifDimensionsBasedonMultipleTablesCREATEDIMENSIONcustomers_dimLEVELcustomerIScustomers.cust_idLEVELcityIScustomers.cust_cityLEVELstateIScustomers.cust_state_provinceLEVELcountryIScountries.country_idLEVELsubregionIScountries.country_subregionLEVELregionIScountries.country_regionHIERARCHYgeo_rollup(customerCHILDOFcityCHILDOFstateCHILDOFcountryCHILDOFsubregionCHILDOFregionJOINKEY(customers.country_id)REFERENCEScountry)ATTRIBUTEcustomerDETERMINES(cust_first_name,cust_last_name)ATTRIBUTEcountryDETERMINEScountries.country_name;DimensionswithMultipleHierarchiesCALhierarchyWEEKhierarchyYEARQUARTERMONTHDAYYEARWEEKDAYDimensionsandPrivilegesCREATEDIMENSIONCREATEANYDIMENSIONSELECTobjectprivilegeoneachreferencedobjectALTERANYDIMENSIONDROPANYDIMENSIONDimensionRestrictionsTwolevelscannothavethesamecolumnset.Columnsofahierarchylevelcannotbeassociatedwithmorethanonedimension.Ahierarchylevelcannotbeachildofitself.Alllevelkeyattributesforonelevelmustbelongtothesametable.TheJOINclauseisrequiredifcolumnsofdifferentlevelscomefromdifferenttables.Alladditionalattributesforonelevelkeymustbelongtothesametable.Itisnotpossibletocreatedimensionsbasedonviews.UsingVALIDATE_DIMENSIONtoVerifyRelationshipsinaDimensionDBMS_DIMENSION.VALIDATE_DIMENSION('SH.TIMES_DIM',TRUE,TRUE,'vertimes_dim');Checkthatlevelsarenon-nullCheckonlynewrowsinthetablesDimensionownerandname

User-suppliedidentifierVerifyingRelationshipsinaDimensionDBMS_DIMENSION.VALIDATE_DIMENSION('SH.CHANNELS_DIM',false,true,'verchan_dim');SELECT*FROMsh.channelsWHERErowidIN(SELECTbad_rowidFROMdimension_exceptionsWHEREstatement_id='verchan_dim');CHANNEL_IDCHANNEL_DESCCHANNEL_CLASS

SDirectSalesDirectSSecurityDirect@utldim.sqlViewingDimensionsUsing

EnterpriseManagerview_dimension.gifViewingDimensionsintheDataDictionaryDBA_DIMENSIONSDBA_DIM_LEVELSDBA_DIM_LEVEL_KEYDBA_DIM_ATTRIBUTESDBA_DIM_HIERARCHIESDBA_DIM_CHILD_OFDBA_DIM_JOIN_KEYViewingtheDefinitionofaDimensionSQL>SETSERVEROUTPUTONFORMATWRAPPEDSQL>EXECUTEDBMS_DIMENSION.DESCRIBE_DIMENSION('sh.channels_dim');DIMENSIONSH.CHANNELS_DIMLEVELCHANNELISSH.CHANNELS.CHANNEL_IDLEVELCHANNEL_CLASSISSH.CHANNELS.CHANNEL_CLASS_IDLEVELCHANNEL_TOTALISSH.CHANNELS.CHANNEL_TOTAL_IDHIERARCHYCHANNEL_ROLLUP(CHANNELCHILDOFCHANNEL_CLASSCHILDOFCHANNEL_TOTAL)ATTRIBUTECHANNELLEVELCHANNELDETERMINESSH.CHANNELS.CHANNEL_DESCATTRIBUTECHANNEL_CLASSLEVELCHANNEL_CLASSDETERMINESSH.CHANNELS.CHANNEL_CLASSATTRIBUTECHANNEL_TOTALLEVELCHANNEL_TOTALDETERMINESSH.CHANNELS.CHANNEL_TOTALDimensionInvalidationInvalidationisdoneautomaticallywhenrelatedobjectsarealtered.Verifyadimension’sstatusthroughEnterpriseManagerortheINVALIDcolumnofDBA_DIMENSIONS.UsetheALTERDIMENSION…COMPILEcommandtorevalidateadimension.Dropadimensionasfollows:DoesnotinvalidatematerializedviewsthatuserelationshipsspecifiedinthedimensionDroppingDimensionsDROPDIMENSIONchannels_dim;CreatethematerializedviewusingtheattributeLEVELclause:Selectivelydropacolumn:SelectivelyDropping

DimensionComponentsALTERDIMENSIONproduct_dimDROPATTRIBUTEattLEVELprod_idCOLUMNprod_status;CREATEDIMENSIONproduct_dimLEVELprod_idIS(d_id)ATTRIBUTEattLEVELprod_idDETERMINESprod_name,prod_status;ConstraintsorDimensions?Fornormalizeddimensions,use:PK/