Oracle數(shù)據(jù)倉庫解決方案

1、Oracle Data Warehouse SolutionOracle ( China )Presales ConsultantXu Xin數(shù)據(jù)倉庫庫幾大功功能Query/ReportDrillup/DrillDownCompareExceptionForcast,WhatifData Mining數(shù)據(jù)倉庫庫三大流流程數(shù)據(jù)建模模Data Model Design數(shù)據(jù)抽取取Data Extract, Transform,Transport數(shù)據(jù)表現(xiàn)現(xiàn)Rolap,MolapData Delivery,Drillup/Drilldown,Data Rotation,DataMiningROLAP/

2、MOLAP -爭論的焦焦點在哪哪里定義:ROLAP= 基于于關(guān)系的的 OLAPMOLAP= 基于于多維的的 OLAPROLAP供供應商說說 :你能用關(guān)關(guān)系技術(shù)術(shù)做所有有的事情情?MOLAPROLAP供供應商說說 :你用多維維數(shù)據(jù)庫庫能做得得更好ROLAP供供應商僅僅僅能做做ROLAP.MOLAP供供應商僅僅僅能做做MOLAP.ORACLE能能提供供 ROLAP和MOLAPROLAP/ MOLAP -爭論的焦焦點在哪哪里ROLAP和MOLAP- Oracle的解決方方案OracleROLAPData MartSuite-建模，數(shù)數(shù)據(jù)抽取取的工具具Discoverer - 基于于關(guān)系數(shù)數(shù)據(jù)庫的的分

3、析工工具多維數(shù)據(jù)據(jù)的視圖圖,挖挖掘和旋旋轉(zhuǎn)OracleMOLAPExpress 系列列產(chǎn)品提提供MOLAP的方方案多維數(shù)據(jù)據(jù)的視圖圖,挖挖掘和旋旋轉(zhuǎn),計計算和和存取、預測Oracle能能提供供 ROLAP 和MOLAPExpress 系列列產(chǎn)品能能做MOLAP和和 ROLAPOFA, OSAApplicationsExpressPower Analysis DiscovererQuery, Reporting,AnalysisDecisionsComponents of aDataWarehouse/MartOLTPOLTPExternal DataOperationsDataStorageD

4、ata MartSuiteAddValue To TheOracleWarehouseData MartSuite2.0 -ComponentsGreat8-Oracle8,release 8.0.4Dashing -DataMart Designer 2.1Better-Data MartBuilder2.0Debonair- Discoverer3.1Awesome -Web Application Server3.0.1Ravishing-Reports Server3.0Compelling -CookbookDesignand ManagementAnyDataAnyAccessAn

5、ySourceMetadataConstructionMethodologyDrag &dropvisualmetaphorDataModelingIdentifydata sourcesIdentifysourcesubsetModelStar SchemaProcessModelingBuildPlansDimensiontablesTime DimensionFact tablePopulatedatabaseBusinessModelingDefineend-userlayerOracle8iforDataWarehousingContinuous InnovationOracle7.

6、3Hash JoinBitmapIndexesParallel-AwareOptimizerPartitionViewsInstanceAffinity:FunctionShippingParallelUnionAllAsynchronousRead-AheadHistogramsAnti-JoinPartitionedTablesand IndexesPartitionPruningParallelIndexScansParallelInsert, Update,DeleteParallelBitmapStarQueryParallelANALYZEParallelConstraint En

7、ablingServerManagedBackup/RecoveryPoint-in-Time RecoveryOracle8.0Summary ManagementNewPartitioning SchemesResourceManagerProgressMonitorAdaptiveParallelQueryServer-basedAnalyticFunctionsTransportableTablespacesDirectLoader APIFunctional IndexesPartition-wiseJoinsSecurityEnhancementsandmore.Oracle8iD

8、ata ModelingOracleDataMart DesignerTableorders(constraintc_o_datecheck(O_entry_d betweento_date(?1-jan-1997?慸d-mon-yyyy?andto_date(?1-dec-1997?慸d-mon-yyyy?)disable,o_idnumber,o_d_idnumber,o_w_idnumber,o_c_idnumber,o_entry_ddate,o_carrier_idnumber,o_ol_cntnumber,o_all_localnumber)tablespaceordinitran

9、s3pctfree5 storage(initial20Knext 110Mpctincrease0freelistgroups19freelists9);Source: OLTPProductdescriptionsizeflavorpackagePromotiondescriptiondealdiscountmediaMarketdescriptiondistrictregiondemographicsTimedescriptionweekdayholidayfiscalFact TableTarget: DataMartProcess ModelingOracleDataMart Bui

10、lderOrders - ExtractSQL QueryProductProduct_LineProduct_GroupShipShipperContactCus_ LocOrdersContact_LocProductLookupMarketLookupPromotionLookupTimeLookupDirect PathLoaderOracle Data Mart BuilderMetadataIntegrationProductdescriptionsizeflavorpackagePromotiondescriptiondealdiscountmediaMarketdescript

11、iondistrictregiondemographicsTimedescriptionweekdayholidayfiscalFact TableOrders - ExtractSQL QueryProductLookupMarketLookupPromotionLookupTimeLookupDirect PathLoaderOracleDataMartBuilderOracleDiscovererOracleDataMartDesignerMetadata決策支持持系統(tǒng)的的范圍IT 人員為用戶開發(fā)業(yè)務用戶獨立分析專業(yè)分析人員標準報表即席查詢分析復雜分析Oracle決決策支支持工具具集標準

12、報表即席查詢分析復雜分析上季度各地區(qū)銷售 情況如何？什么原因造成東南亞 地區(qū)的業(yè)務下降?如果同樣的因素影響到 其它地區(qū)，將會對全球的 業(yè)務造成什么影響?OracleExpressOracle DiscovererOracleReportsOracle決決策支支持工具具集標準報表表 -OracleReports集中式報報表生成成存儲輸出發(fā)布布高質(zhì)量量的報表表即席查詢詢分析-Discoverer即席查詢詢,數(shù)數(shù)據(jù)鉆入入，旋轉(zhuǎn)轉(zhuǎn)業(yè)界領(lǐng)先先的易用用性和性性能復雜分析析 -ExpressOn-line AnalyticalProcessing(OLAP)高級計算算財務,時時間序序列,建建模, 預測測,回

13、回歸分析析，假設設分析OracleReportsWhat is it?Formats anddistributesdatabaseinformationKeyadvantagesUnlimiteddataformattingDistributeshigh fidelity reportsvia theWebApplicationserverbasedreporting engineOracleExpressOracleDiscovererOracleReportsProductionReportingAdHocQuery,AnalysisAdvancedAnalysisOracleDiscov

14、erAddValue To TheOracleWarehouseOracleDiscovererWhat is it:Adhocqueryand analysis of relationaldataKeyadvantages:Award-winning easeofuseIndustry-leading query performanceOracleExpressOracleDiscovererOracleReportsProductionReportingAdHocQuery,AnalysisAdvancedAnalysisDiscoverer is:10timesfasterthanCog

15、nos9 times fasterthanBusinessObjectsMarket-LeadingPerformanceTime to query 100,000rows:CognosBusinessObjectsDiscovererOracleDiscoverer3.1User EditionBusiness Abstraction Layer End User Layer(TM) Data Warehouse / Data Mart (or OLTP)User EditionAdministrationEditionOracleDiscoverer3.1User EditionSuper

16、iorEase of UseEase of UsePerformanceWarehouseExplorationExceptionalPerformanceNavigationalPowerNavigational PowerOpennessOpennessMost TestedUIontheMarketResizeableWizardInterfaceIndustrySpecificQuickTourDramaticReductionofthe Learning Curveandmore.Ease of UseEndUserLayerEase of UseHidestheComplexity

17、oftheUnderlyingDatabaseDatabasecl_addr1|,|cl_addr2|,|cl_addr3|, |cl_twn|, |cl_zipcodesum(nvl(qty*unit_price,0)Jones Smith Name Customer Client Address Revenue Eastern Region UserBatchReportingOpennessToDo.Schedulelong runningqueriesforlaterEase of Use9:00Open workbooks 12:00Refreshworkbooks 3:00 Sch

18、edule Monthlyreports5:00 Schedule Quarterlyreports5:30 Generate newreportformanagement meetingtomorrowSolutiontoLong RunningQueriesReactive Query GovernorStops queries after a pre-set timeUses the resources without showing results!PerformancePredictive Query GovernorPredicts the time a query takes b

19、efore executionDoes not waste valuable resources!ManagingLargeData VolumesSelectLocation, Period,Product,sum(videos_lent)Fromvideo_detailsGroupbyLocation,Period,ProductDetailRowsPerformanceSlow Table ScansMachine IntensiveAggregationsSummary RedirectionSelectLocation, Period,Product,sum(videos_lent)

20、Fromvideo_detailsGroupbyLocation,Period,ProductDetailRowsSummary TablePerformanceSelectLocation, Period,Product,SalesFromvideo_summaryAutomatic!Summary RedirectionDetailRowsSummary TablePerformanceServerClientNetworkResultsBase:CubicClient CachePerformanceEfficientStorageAlgorithmMultidimensional ac

21、cessRapidrotation,drillRegionNorthParisProductYearDetailRowsExternalDataDrillEverywhereDrillDownDrillAcrossDrilltoDetailDrillOutNavigational PowerWebPublishingNavigational PowerIntegrationReportsExpressOpennessMAPIOracleDiscoverer3.1AdministrationEditionSuperiorEase of UseEase of UseSummary Manageme

22、ntServer BasedAdministrationServerBasedAdministrationAdvancedSummaryManagementServerBasedAbstractionLayerServerBasedAdministrationEase of UsePRODUCTIONEUL(RDBMS)Sales.Marketing匜inance匛ducation匔onsultancyUsersAdministratorRead/WriteRead OnlyEasy to Create,Maintainand SetupScaleableLeverageSecurityOpe

23、n -ODBCDB2SybaseInformixSQLServerOracle?.ODBCEnhancementsOpennessOracleEnd User LayerEUL GatewayDes/2000ETT ToolsApplicationsQuery ToolsBuildSummariesfromQueriesQueryperformancestatisticsSummary managementrecommendationsSummary Management38DefinitionsDataWarehouseAmulti-subjectinformationstoreDesign

24、edspecificallyfor decision supportDataMartAsubject or application-specificdata warehouseTypes:Dependent,IndependentDWvs.DataMartWarehouseDataMartScopeCorporateLine-of-BusinessSubjectsmulti-subjectsubject-specificData SourcesmanyfewSize100GB -1TB+100GBImplementationmonths-yearsmonthsWarehouseData Mar

25、tOracleExpressOracleWarehouseOracleExpressWhat is it:Advancedanalysisofmultidimensional andrelational dataKeyadvantages:Broadestrangeofanalytical functionsScaleableperformanceIntegrated development environmentOracleExpressOracleDiscovererOracleReportsProductionReportingAdHocQuery,AnalysisAdvancedAna

26、lysisOracleExpressKeyDifferentiatorsAdvancedanalysisBuilt-inforecastingMulti-user what-ifAutomaticsimultaneous modelsProgrammableserverScaleableperformanceAnalysisofexistingrelational databasesMultiplecubes,hierarchiesper schemaIntegrated development environmentBroadestrangeofcapabilities可擴展的的多維數(shù)數(shù)據(jù)庫

27、服服務器,內(nèi)置強大大的分析析功能和決決策支持持能力PersonalExpress,Express Server用于銷售售分析,市場分析析,財務分析析的OLAP應用產(chǎn)品品SalesAnalyzer,Financial Analyzer面向?qū)ο笙蟮腛LAP應用開發(fā)發(fā)工具Express Analyzer,Express ObjectsExpressServerExpressApplicationsExpressToolsOracleOLAP- ExpressProductsExpress一個專門門為查詢詢和分析析商業(yè)情情報數(shù)據(jù)據(jù)而設計計的服務務器Express?對于一些些分析查查詢和計計算,Expr

28、ess比關(guān)系型型技術(shù)快快10倍，100倍，甚至至1000倍JerryHeldSeniorVP, ServerTechnologies, OracleCorporation查詢速度度如此快快，使Express具有很強強的競爭爭能力.多維數(shù)據(jù)據(jù)模型轄區(qū)經(jīng)理理/行長財務經(jīng)理理任意分析析PRODTIME資金經(jīng)理理為數(shù)據(jù)提提供快速速，靈活活的訪問問方法金融產(chǎn)品品時間頭寸轄區(qū)對時間，日期的的智能處處理，系統(tǒng)相關(guān)關(guān)數(shù)據(jù)自自行匯總總。根據(jù)數(shù)據(jù)據(jù)建立層層次關(guān)系系數(shù)據(jù)自行行匯總在維上建建立層次次關(guān)系KEYS相同的數(shù)數(shù)學模型型華東區(qū)域華中月份產(chǎn)品西南Jan膠卷鏡片攝像機銷售量DIMENSIONSFebMar多維數(shù)據(jù)

29、據(jù)庫服務務器的特特性立方體，頁，片片的數(shù)據(jù)據(jù)維=主鍵只存放一一次，重重復使用用強制性保保證數(shù)據(jù)據(jù)完整性性節(jié)省磁盤盤空間極容易地地結(jié)合數(shù)數(shù)據(jù)和公公式支持共享享的商業(yè)業(yè)邏輯保證數(shù)據(jù)據(jù)的一致致性每一個單單元都被被索引因此有很很高的存存取效率率TimeproductGeography數(shù)據(jù)存放放PhiladelphiaNewYorkBostonQ1Q2Q3Q4TentsCanoesRacquetsSportswearFootwearSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSa

30、lesSalesSales數(shù)據(jù)頁數(shù)據(jù)存取取SalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesSales關(guān)系數(shù)據(jù)據(jù)庫多多維維數(shù)據(jù)庫庫CostCostCostCostCostCostCostCostCostCostCostCostCostCostCostCostSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMkt

31、ProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProdSalesCostUnitsDateMktProd只要讀取取一頁要讀取兩兩頁數(shù)據(jù)存取取方法DistrictQuarterProductNewYorkBostonPhiladelphiaQ1Q2Q3Q4TentsCanoesRacquetsSportswearFootwear偏移地址址存取更強有力力的分析析能力更好的執(zhí)執(zhí)行效率率很多的已已建好的的函數(shù)FinancialFunctionsDepreciation折舊率GrowthRate增長率NetPresentValue現(xiàn)值Internal

32、Rate of ReturnNumeric/TimeSeries FunctionsAverage平均值Cumulative Sums累積和Lag/Lead落后/領(lǐng)先Variance變動率MovingAverage/Total移動平均均值Smallest/Largest最大/最小StandardDeviation水平偏差差Total總和OtherFunctionsForecastingRegression. .你也能創(chuàng)創(chuàng)建你自自己的函函數(shù)Express LanguageApplicationProgrammingInterfacesBasicOperationsCharacterManipul

33、ation FunctionsCommunicationsCountingandTestingFunctionsComputerResourceManagementData ManipulationDictionary OperationsDimensionInformationFinancialFunctionsForecastingandRegressionMiscellaneous Information andOptionsModelsNumeric FunctionsandCommandsNumeric OptionsPagingOptionsProgramsSecurityandS

34、copingSystemCommandsand InformationUsingExternalDataWorking withWindowsOracleConfidential持續(xù)的查查詢過程程查詢示例例：從費城的的最近一一個季度度 的產(chǎn)產(chǎn)品中選選擇銷售售額大于于150K的產(chǎn)品，并且從從蠖鈄鈄?大的產(chǎn)品品。費城季度產(chǎn)品紐約波士頓Q1Q2Q3Q4運動鞋運動衣滑板手套球拍地區(qū)持續(xù)的查查詢過程程查詢示例例：限制季度到Q4季度產(chǎn)品紐約波士頓Q1Q2Q3Q4運動鞋運動衣滑板手套球拍地區(qū)費城持續(xù)的查查詢過程程查詢示例例：限制季季度到到Q4限制地區(qū)到費城季度產(chǎn)品紐約波士頓Q1Q2Q3Q4運動鞋運動衣滑板手

35、套球拍地區(qū)費城持續(xù)的查查詢過程程查詢示例例：限制季季度到到Q4限制地區(qū)到到費城限制產(chǎn)品的銷銷售額大于150,000季度產(chǎn)品紐約波士頓Q1Q2Q3Q4運動鞋運動衣滑板手套球拍地區(qū)費城持續(xù)的查查詢過程程查詢示例例：限制季季度到到Q4限制地區(qū)到到費城限制產(chǎn)產(chǎn)品的銷銷售額大大于150,000限制產(chǎn)品的利利潤額前前兩位位季度產(chǎn)品紐約波士頓Q1Q2Q3Q4運動鞋運動衣滑板手套球拍地區(qū)費城許多實體體/公用維BUDGETTimeMarketProductTimeFORECASTScenarioMarketPRICETimeProductTimeSALESMarketProductProduct派生的實實體存放結(jié)果果在變量量中或者由一一個計算算公式來來決定x=價格銷售數(shù)量銷售金額派生的實實體存放結(jié)果果在變量量中或者由一一個計算算公式來來決定這些計算算公式可可由其它它公式得得來.x=價格銷售數(shù)量銷售金額多個邏輯輯的最終終用戶視視圖產(chǎn)品經(jīng)理理視圖地區(qū)經(jīng)理理視圖財務經(jīng)理理視圖總經(jīng)理視視圖TimeQ1Q2Q3Q4ProductTentsCanoesRacquetsSportswearFootwearPhiladelphiaNew YorkBostonDistrict多維分析析舉例查詢上季季度在費費城地區(qū)區(qū) ，營營利最多