Les17-Managing-the-Data-Warehouse-数据仓库英文oracle99版-课件.ppt

1、Copyright Oracle Corporation,1999.All rights reserved.Managing the Data WarehouseCopyright Oracle Corporation,1999.All rights reserved.OverviewProject Management(Methodology,Maintaining Metadata)DefiningDW Concepts&TerminologyPlanningfor a SuccessfulWarehouseAnalyzingUser QueryNeedsChoosing aComputi

2、ngArchitectureModeling the DataWarehousePlanningWarehouseStorageETT(Building theWarehouse)Meeting aBusinessNeedSupportingEnd UserAccessManaging the Data WarehouseCopyright Oracle Corporation,1999.All rights reserved.ObjectivesAfter completing this lesson,you should be able todo the following:Develop

3、 a plan for managing the transition from development to implementationIdentify challenges pertaining to the growth of the data warehouseDescribe backup and archive mechanismsIdentify data warehouse performance issuesCopyright Oracle Corporation,1999.All rights reserved.Managing the Transition to Pro

4、ductionPromoting support for changePilot versus large-scale implementationDocumentationTestingTrainingPostimplementation supportMaintaining the warehouseCopyright Oracle Corporation,1999.All rights reserved.Promoting Support for ChangeTo SupportNot to SupportManagementCompetitivenessFear of changeBu

5、siness benefitRisk avoidanceDevelopersNew skillsOutdated skillsLeading edge End UsersFaster flexible systemDisruption Improved toolsChangeIncreased workloadCopyright Oracle Corporation,1999.All rights reserved.Methods for Promoting SupportAwarenessFeedbackInformationSkillsEducationDirectionControlCo

6、pyright Oracle Corporation,1999.All rights reserved.Choosing Between Pilot and Large-Scale ImplementationPilotLarge-Scale ImplementationCopyright Oracle Corporation,1999.All rights reserved.The Warehouse PilotDemonstrates benefits to:ManagementUsersIT staffRelevant to the business Low technical risk

7、Small and feasibleAnticipates increased useFocused on an initial business issueRemains in contextCopyright Oracle Corporation,1999.All rights reserved.Piloting the WarehouseDesignersProve model,data,and access toolsUsersProve ease of use of toolCheck data and query performanceIdentify training requi

8、rementsDevelopersResolve ETT and metadata issuesDetermine users data and training requirementsTest security and access levels,monitor performanceCopyright Oracle Corporation,1999.All rights reserved.DocumentationProduces textual deliverables:GlossaryUser and technical documentationOnline helpMetadat

9、a reference guideWarehouse management referenceNew features guideCopyright Oracle Corporation,1999.All rights reserved.Testing the WarehouseTest every stage.Use a realistic test database and environment.Copyright Oracle Corporation,1999.All rights reserved.TrainingUsersMetadataDSS toolsAd hoc querie

10、sGetting helpRegistration of enhancement requestsInformation systems developers:Analysis techniquesHardware technicalitiesNetworkingImplementing,building,and supporting DSSCopyright Oracle Corporation,1999.All rights reserved.Evaluate and review the implementationMonitor the warehouse:Respond to pro

11、blemsConduct performance tuningRoll out metadata,queries,reports,filters,and conditions Implement securityIncorporate new usersDistribute data marts and catalogsTransfer ownership from ITPostimplementation SupportCopyright Oracle Corporation,1999.All rights reserved.Managing GrowthExpanding user num

12、bersInitial 3 Months6 Months12 Months24 Months050100150200250300Initial 3 Months6 Months12 Months24 MonthsCopyright Oracle Corporation,1999.All rights reserved.Types of GrowthIncreasing number of usersBroader usageGrowth of data volumesCopyright Oracle Corporation,1999.All rights reserved.Expansion

13、and AdjustmentEvaluate continually:ChangesNew incrementsUnnecessary componentsStrategiesEnsure open environmentDocument development processes for the future:PlanningCost analysisProblem assessment and correctionPerformance assessmentCopyright Oracle Corporation,1999.All rights reserved.Controlling E

14、xpansion Control by Ensuring the continuity of staffDocumenting processes,solutions,and metricsEstablishing working test and production architecture for further incrementsCreating a strategy for maintaining changes to dataCopyright Oracle Corporation,1999.All rights reserved.Sizing StorageConsider d

15、ifferent methods.Determine the best for your needs.Know the business requirements.Do not underestimate requirements.Plan for growth.Consider space for unwanted data.Copyright Oracle Corporation,1999.All rights reserved.Estimating StorageFact volumesFact lifetimeTechnology availabilityTechnology purc

16、haseStoring pre-summarized dataMirroring or other techniques requiring disk storageCopyright Oracle Corporation,1999.All rights reserved.Objects That Need SpaceODSIndexes and metadata Summary data Redo logsRollback informationSort areasTemporary spaceWorkspace for backup and recoveryCopyright Oracle

17、 Corporation,1999.All rights reserved.Test Load SamplingAnalyze statistically significant data samplesUse test loads for different periodsReflect day-to-day operationsInclude seasonal data and worst case scenariosCalculate number of transactionsEmploy average sales price approachCopyright Oracle Cor

18、poration,1999.All rights reserved.Average Sales PriceAssume transaction-level grain.Total company revenueAvg sale price per line itemNumber of line items per yearNumber of base fact recordsKey fieldsFact fieldsBase fact table size$20 billion$5$20 billion/$5=4 billion4 billion x 3 yrs=12 billion4(x 4

19、 bytes)4(x 4 bytes)12 billion x 8 fields x 4 bytes=385 GBCopyright Oracle Corporation,1999.All rights reserved.Average Sales PriceUse other methods:It is difficult to obtain an accurate averageYou can achieve inaccurate calculationsDo not use this approach on its ownCopyright Oracle Corporation,1999

20、.All rights reserved.Other Techniques and ConsiderationsQueuing modelsRule of thumbTotal database size is three to four times the size of the base fact tablesConsider:SparsenessDimensionsIndexesSummariesSort operational spaceCopyright Oracle Corporation,1999.All rights reserved.Space ManagementMonit

21、orAvoid fragmentationTest load dataPlan for growthKnow business patternsNever let space become an issueCopyright Oracle Corporation,1999.All rights reserved.Managing Backup and RecoveryBusiness requirements for availabilityFast recovery essentialStrategy:DefinedTestedProvenEvolvingCopyright Oracle C

22、orporation,1999.All rights reserved.Backup StrategyIs based on the business requirements and the cost benefitInvolves large volumes of data:All objects except temporary tablespacesIncremental Includes first-time load and refreshesCopyright Oracle Corporation,1999.All rights reserved.Defining the Str

23、ategyMission-critical systemsSLAs:Defined downtimeAcceptable MTBFEfficient backup and recoveryEvaluation of different technologiesCopyright Oracle Corporation,1999.All rights reserved.Planning for BackupPlan at the design stageUse hot backups for VLDBsBack up necessary components:Fact and dimension

24、dataWarehouse schemaMetadata schemaMetadataExport/Import utility:Disk space TimeCopyright Oracle Corporation,1999.All rights reserved.Backup ToolsOracle7 Enterprise Backup UtilityOracle8 Recovery ManagerUtilities:Import and ExportOperating systemThird partyCopyright Oracle Corporation,1999.All right

25、s reserved.Parallel Backup and RecoveryParallel BackupRuns simultaneously from any nodeOff-lineOnlineParallel RecoveryRuns simultaneously from redo logsCopyright Oracle Corporation,1999.All rights reserved.System FailuresProcessDatabase instanceMediaNatural disasterFailures are costlyCopyright Oracl

26、e Corporation,1999.All rights reserved.Disaster Recovery RequirementsReplacement or standby machineTape and disk capacityCommunication links to users and dataCopies of softwareDatabase backupAdministration and operations staffDocumentationCopyright Oracle Corporation,1999.All rights reserved.Disaste

27、r Recovery PlanningEstablish the strategyPrepare the strategyMaintain the strategyAudit the strategyTest recovery plan regularlyGain approval from usersCopyright Oracle Corporation,1999.All rights reserved.Archiving DataDetermine data life expectancy.Identify archive frequency.Use read-only tablespa

28、ces.Plan and design into early specifications.Copyright Oracle Corporation,1999.All rights reserved.Purging DataReduce data volumes:Create summaries.Remove unwanted base data.Choose the most effective method.Copyright Oracle Corporation,1999.All rights reserved.Improving Query EfficiencyImprove data

29、base designUse indexesUse governorsUse prepared and tested queriesRun large jobs out of hoursOracle 8i Resource Manager can guarantee resource availability to specific groups Use data martsCopyright Oracle Corporation,1999.All rights reserved.Network PerformanceProvide sufficient bandwidth Provide o

30、ptimal configuration for access Identify middleware requirements Know refresh volumesConsider interaction with job scheduling softwareUse client-side processing Deploy data marts Analyze trafficCopyright Oracle Corporation,1999.All rights reserved.Review and ReviseMonitor the warehouse:UsageAccessAc

31、curate grainDetail dataPeriodicityCopyright Oracle Corporation,1999.All rights reserved.Secret of SuccessThink big start smallCopyright Oracle Corporation,1999.All rights reserved.SummaryThe successful warehouse:Is driven by the businessFocuses on objectivesAdds value to the businessCan be understood and usedDelivers good dataPerforms wellBelongs to the users