OWB试验手册3.docx
《OWB试验手册3.docx》由会员分享，可在线阅读，更多相关《OWB试验手册3.docx（63页珍藏版）》请在冰点文库上搜索。
OWB试验手册3
设计目标数据仓库
一、DefiningaTargetWarehouseModule
Tobegindesigningthemodelofyourtargetwarehouse,youcanstartbydefiningatargetmodule.Targetmodulescontainthemetadatadefinitionofthetargetobjectsyoudesign.Everytargetmodulemustbemappedtoatargetuserschema.
Therefore,beforeyoucreateaWarehouseBuildertargetmodule,youneedtocreateatargetschemathatwillphysicallystoreyourtargetobjectsondeployment.Then,youmakesurethatthetargetmodulereferencesthistargetschemabyassigninganappropriatelocation.
Tosetupyourtargetwarehousemodule,youperformthefollowingsteps:
CreateaTargetUser
CreateaTargetModule
1、CreateaTargetUser
WhenyoulogintoWarehouseBuilderDesignCenterasrepositoryowner,WarehouseBuilderdisplaystheGlobalExplorerinthelower-rightcorneroftheconsole.YoucandefinethemetadatasecuritystrategiesaccordingtoyourimplementationrequirementsinthisGlobalExplorerpanel.
Youwillnowcreateatargetuserbyperformingthefollowingsteps:
Note:
Alternatively,youcanalsousetheRepositoryAssistanttocreatethistargetuser.
1.
IntheGlobalExplorerpanel,expandtheSecuritynode.Right-clicktheUsersnodeandselectNew.
Note:
Youmustsaveorrevertyourchangesbeforecreatinganewuser.Tosavepreviouschanges,selectSaveAllfromtheDesignmenuorclick
onthetoolbar.
TheRegisterWarehouseBuilderUsersWizardislaunched.ClickNextontheWelcomepage.
2.
OntheSelectDBusertoregisterpage,youcanselectfromtheavailablelistofdatabaseusersorcreateanewonethatwillbeautomaticallyregisteredasWarehouseBuildertargetuser.
ClickonCreateDBUsertocreateanewtargetuser.
3.
Tobeabletocreateanewtargetuser,youneedtohaveSYSDBAprivileges.IntheCreateDatabaseUserdialogbox,entersysasusernameandsysorasthepassword.
EnterEXPENSE_WH/EXPENSE_WHastheusername/passwordforthenewDBuser.Acceptthedefaultsforthetablespaceproperties.ClickOK.
ObserveEXPENSE_WHhasbeenaddedtotheSelectedUserslist.ClickNext.
4.
OntheCheckuserastargetschemapage,ensurethatUsedastargetschemaisselectedfortheEXPENSE_WHuser.ClickNext.
TheTargetUserPassworddialogboxlaunches.Re-enterthesamepassword,expense_whthatyouspecifiedintheCreateDatabaseUserdialogboxfornewDBuser.ClickOK.
OntheSummarypage,examinethedetailsandclickFinish.TheRegisterUsersProgresswindowwillshowthetaskprogress.
5.
ExaminetheUsersnode.YoufindtheEXPENSE_WHuseraddedtothelistofexistingtargetusers.
Also,intheConnectionExplorerpanel,expandtheOracleLocationsnodeandnotethatanewlocation,EXPENSE_WH_LOCATION,hasbeenadded.
Note:
Locationsdefineinformationaboutthedatabaseschemaortargettoolwhereyouwillbedeployingobjects.Locationsarespecifictoatypeofmodule,suchasOracleDatabase,SAP,orflatfile.
Youhavecreatedanewtargetschema,EXPENSE_WH,whichwillphysicallystoreyourtargetwarehouseobjects.
二、CreateaTargetModule
Younowcreateatargetmodulethatpointstothenewtargetschema.
1.
IntheOWB_DEMOproject,expandtheDatabasesnode,right-clicktheOraclenode,andselectNew.
TheCreateModuleWizardislaunched.ClickNextontheWelcomepage.
2.
OntheNameanddescriptionpage,enterEXPENSE_WHasthenameofthemodule.AcceptDevelopmentasthemodulestatusandensurethatWarehouseTargetisselectedasthemoduletype.ClickNext.
3.
OntheConnectioninformationpage,fromtheLocationdrop-downlist,selectEXPENSE_WH_LOCATIONasthelocationofthemodule.Observethatalltherelevantconnectiondetailsarealreadyspecified.ThisisbecauseWarehouseBuilderderivesthelocationinformationdirectlyfromtheuseryoucreated.ClickNext.
Note:
BeforeyouselectedEXPENSE_WH_LOCATIONfromtheLocationdrop-downlist,EXPENSE_WH_LOCATION1wasselectedbydefault.Thisisbecausewheneveryoutrytocreateamodule,WarehouseBuilderautomaticallyassignsalocationwith_LOCATIONsuffixedtothemodulename.
Youhaveselectedthelocation,EXPENSE_WH_LOCATION,whichwascreatedwhenyoucreatedtheEXPENSE_WHtargetuser.
OntheSummarypage,examinethedetailsandclickFinish.
4.
ObservethattheEXPENSE_WHtargetmodulehasbeenaddedundertheOraclenodeoftheOWB_DEMOproject.ExpandEXPENSE_WHandexamineitscontents.
Untilnow,youhavecreatedanemptytargetmoduleEXPENSE_WHthatmapstoanemptytargetschemaEXPENSE_WH.Now,youstartdesigningyourtargetobjectsinthistargetmodule.
三、CreatingExternalTables
Inthepreviouslesson,youcreatedaflatfilemoduleinwhichyouimportedandsampledtwoflatfiles.Tomoveandtransformdatafromflatfilesintorelationalordimensionaltargets,youcanuseeitheraflatfileoperatororanexternaltable.Externaltablesenableyoutorepresentdatafromfilesourcesinrelationaltableformat.
ExternaltablesallowyoutodirectlyqueryflatfilesfromtheOracledatabaseandalsoquery,join,transform,andconstrainflatfiles.
Usingexternaltablestoaccesssourcedatafromnonrelationalsources,offersanumberofadvantages:
SQLSupportandHeterogeneousJoins:
Whenyouuseexternaltables,youcanqueryandtransformdatacontainedintheflatfilesdirectly;SQLcodeisgeneratedbyWarehouseBuilder.Inaddition,heterogeneousjoinsbetweenflatfilesandrelationaltablesareenabled.
EnablingParallelSELECTStatements:
Youcangetparallelaccesstothefilethroughthetable.Forexample,SELECTstatementscanberuninparalleltherebyoptimizingperformance.
EliminatingFlat-FileStagingTables:
Whenyouuseaflatfileoperator,WarehouseBuildergeneratesSQL*Loadercodeandyouneedarelationalstagingtabletoloadthedata.Externaltableseliminatetheneedforastagingtablethusreducingtheprocessingtimetoloadflat-filedataandtheneedforextrastoragespace.
InWarehouseBuilder,youcandefineexternaltablesontopofflat-filestructures,deployexternaltablestothedatabase,anduseexternaltablesinmappings.
Now,youcreatetwoexternaltablesthatwillpointtothetwopreviouslysampledflatfiles.
1.
IntheProjectExplorerpanel,expandOWB_DEMO.ExpandDatabases>Oracle>EXPENSE_WH.Right-clickExternalTablesandselectNew.
TheCreateExternalTableWizardislaunched.ClickNextonthewelcomepage.Thewizardguidesyouthroughthecreationofanexternaltable.
2.
OntheNameandDescriptionpage,enterEXPENSE_CATEGORIESasthenameoftheexternaltable.ClickNext.
3.
OntheFileSelectionpage,selectEXPENSE_CATEGORIES_CSVandclickNext.Thisfile,whichyousampledinthepreviouslesson,containsthemetadatafortheexternaltable.
4.
OntheLocationspage,youselectthedefaultlocationthatisusedbytheexternaltable.SelectSOURCE_LOCATIONfromthelocationdrop-downbox.ClickNext.
OntheSummarypage,reviewthedetailsandclickFinish.Thewizardcreatestheexternaltable.InProjectExplorer,youseeEXPENSE_CATEGORIESaddedtotheExternalTablesnode.
Now,yourepeatthesamestepstocreateanexternaltablefortheexport_csvfile.
5.
Right-clickExternalTablesandclickNew.ClickNexttoskipthewelcomepageoftheCreateExternalTableWizard.OntheNameandDescriptionpage,enterEXPENSE_DATAasthenameoftheexternaltable.ClickNext.
6.
OntheFileSelectionpage,selectEXPORT_CSVandclickNext.
7.
OntheLocationspage,youselectthesamedefaultlocationthatyouselectedforthepreviousexternaltable.SelectSOURCE_LOCATIONfromthelocationdrop-downbox.ClickNext.OntheSummarypage,reviewthedetailsandclickFinish.Thewizardcreatestheexternaltable.
Observe,thatbothexternaltablesareaddedtothetargetmodule.
8.
Youneedtoconfigurethephysicalfilesystemdetailsforthetwoexternaltables.Right-clickEXPENSE_CATEGORIESandselectConfigure.
IntheConfigurationPropertiesdialogbox,right-clickDataFilesandselectCreate.Acceptthedefaultname,NEW_DATAFILE_1.
9.
OntherightsideintheConfigurationPropertiesdialogbox,selectSOURCE_LOCATIONfortheDataFileLocationproperty.FortheDataFileNameproperty,enterexpense_categories.csv.ClickOK.
10.
RepeatthesamestepstoconfiguretheEXPENSE_DATAexternaltable.Theconfigurationshouldlookasshowninthescreenshot.
文件名：
export.csv
ClickOK.
Youhavefinishedcreatingtwoexternaltablespointingtothetwosampledflatfiles.
三、DesigningDimensions
Dimensionsaretheprimaryorganizationalunitofdatainastarschema.ExamplesofsomecommonlyuseddimensionsareCustomer,Product,andTime.
Adimensionconsistsofasetoflevelsandasetofhierarchiesdefinedusingtheselevels.Usersoftenanalyzedatabydrillingdownonknowndimensionhierarchies.Thequeryperformanceisimprovedbecausethequeryoptimizermakessmartdecisionsaboutchoosingwhatdatatouse.
Tocreateadimension,youmustdefinethefollowing:
DimensionAttributes
Levels
LevelAttributes
Hierarchies
Inthissection,youperformthefollowing:
ExaminethePredefinedPRODUCTSDimensionintheDataObjectEditor
CreateREL_TIMEDimensionUsingtheTIMEDimensionWizard
CreateREL_CATEGORYDimensionUsingtheWizard
ExaminethePredefinedPRODUCTSDimensionintheDataObjectEditor
Thistopicintroducesyoutoabasicexampleofadimension.Beforecreatingdimensions,youexamineanexistingonetounderstandthebasicdesignofadimension.
LetusexaminethepredefinedPRODUCTSdimensionforitsdimen
升级会员 