book

Data Warehousing: Architecture and Implementation

Name: Data Warehousing: Architecture and Implementation
ISBN: 0130809020

by Mark Humphries, Michael W. Hawkins, Michelle C. Dy

December 1998

Beginner to intermediate

360 pages

6h 56m

English

Pearson

Read now

Unlock full access

Copyright
Preface
1. Introduction
1. The Enterprise IT Architecture
The Past: Evolution of Enterprise ArchitecturesThe Present: The IT Professional's ResponsibilityMeet Business RequirementsTake Advantage of Technology AdvancementsBusiness PerspectiveOperationalDecisionalInformationalVirtual CorporationTechnology PerspectiveOperational NeedsLegacy SystemsOLTP ApplicationsActive DatabasesOperational Data StoresFlash Monitoring and ReportingWorkflow Management and GroupwareDecisional NeedsData WarehouseDecision Support ApplicationsInformational NeedsInformational Web Services and ScriptsVirtual Corporation NeedsTransactional Web Services and ScriptsOther ComponentsArchitecture Migration ScenariosLegacy IntegrationThe NeedThe Recommended ApproachOperational MonitoringThe NeedThe Recommended ApproachProcess ImplementationThe NeedThe Recommended ApproachDecision SupportThe NeedThe Recommended ApproachHyperdata DistributionThe NeedThe Recommended ApproachVirtual CorporationThe NeedThe Recommended ApproachMigration Strategy: How Do We Move Forward?Review the Current Enterprise ArchitectureIdentify Information Architecture RequirementsDevelop a Migration Plan Based on RequirementsThe Natural Migration PathThe Customized Migration PathMonitor and Update the Migration PlanIn Summary
2. Data Warehouse Concepts
Gradual Changes in Computing FocusEarly Computing Focused on Operational RequirementsDecisional Requirements Cannot Be Fully AnticipatedOperational Systems Fail to Provide Decisional InformationDecisional Systems Have Evolved to Meet Decisional RequirementsThe Data Warehouse DefinedIntegratedSubject OrientedDatabasesRequired for Decision-MakingEach Unit of Data Is Relevant to a Point in TimeA Data Warehouse Contains Both Atomic and Summarized DataThe Dynamic, Ad Hoc ReportThe Purposes of a Data WarehouseTo Provide Business Users with Access to DataTo Provide One Version of the TruthTo Record the Past AccuratelyTo Slice and Dice Through DataTo Separate Analytical and Operational ProcessingTo Support the Reengineering of Decisional ProcessesA Word About Data MartsMisconception: Data Warehouses and Data Marts Cannot CoexistMisconception: Data Marts Can Be Built Independently of One AnotherA Word About Operational Data StoresDefinition of Operational Data StoresFlash Monitoring and Reporting ToolsRelationship of Operational Data Stores to Data WarehouseData Warehouse Cost-Benefit Analysis / Return on InvestmentBenefitsCostsROI ConsiderationsIn Summary
2. People
3. The Project Sponsor
How Will a Data Warehouse Affect our Decision-Making Processes?How Does a Data Warehouse Improve My Financial Processes? Marketing? Operations?Financial ProcessesMarketingOperationsWhen Is a Data Warehouse Project Justified?Lack of Information SharingDifferent Groups Produce Conflicting ReportsTedious Report Creation ProcessReports Are Not Dynamic, and Do Not Support an Ad Hoc Usage StyleReports That Require Historical Data Are Difficult to ProduceWhat Expenses Are Involved?HardwareSoftwareServicesInternal StaffSummary of Typical CostsWhat Are the Risks?OrganizationalWrong Project SponsorEnd-User Community Not InvolvedSenior Management Expectations Not ManagedEnd-User Community Expectations Not ManagedPolitical IssuesLogistical OverheadTechnologicalTechnical Architecture and NetworkingProject ManagementDefining Project Scope InappropriatelyUnderestimating Project Time FrameUnderestimating Project OverheadLosing FocusNot Looking Beyond the First Data Warehouse RolloutData Warehouse DesignRisk-Mitigating ApproachesIs My Organization Ready for a Data Warehouse?Decision-Makers Feel the Need for ChangeUsers Clamor for Integrated Decisional DataThe Operational Systems Are Fairly StableStaff Can Be Assigned to the ProjectThere Is Adequate FundingHow Do I Measure the Results?In Summary
4. The CIO
How Do I Support the Data Warehouse?Regular Warehouse LoadApplicationsWarehouse DB OptimizationUser Assistance or Help DeskTrainingPreparation for Subsequent RolloutsHow Will My Data Warehouse Evolve?Who Should Be Involved in a Data Warehouse Project?Steering CommitteeUser Reference GroupWarehouse DriverWarehouse Project ManagerBusiness Analyst(s)Warehouse Data ArchitectMetadata AdministratorWarehouse DBASource System Database Administrators (DBAs) and System Administrators (SAs)Conversion and Extraction Programmer(s)Technical and Network ArchitectTrainerWhat Is the Team Structure Like?What New Skills Will My People Need?IT ProfessionalsEnd UsersHow Does Data Warehousing Fit into My IT Architecture?How Many Vendors Do I Need to Talk to?Vendor CategoriesEnterprise OptionsWhat Should I Look for in a Data Warehouse Vendor?Solution FrameworkProject and Integration Consultancy ServicesFront-End/OLAP/Decision Support/Data Access and Retrieval ToolsMiddleware/Data Extraction and Transformation ToolsRelational Database Management SystemsHardware or Operating System PlatformsHow Does Data Warehousing Affect My Existing Systems?Improvement Areas in Operational SystemsData Warehousing and Its Impact on Other Enterprise InitiativesHow Does Data Warehousing Tie In with BPR?How Does Data Warehousing Tie In with Intranets?When Is a Data Warehouse Not Appropriate?When the Operational Systems Are Not ReadyWhen the Need Is Operational IntegrationHow Do I Manage or Control a Data Warehouse Initiative?In Summary
5. The Project Manager
How Do I Roll Out a Data Warehouse Initiative?Start with a Data Warehouse Planning ActivityImplement a Proof-of-Concept PilotExtend Functionality IterativelyHow Important Is the Hardware Platform?What Technologies Are Involved?Do I Still Use Relational Databases for Data Warehousing?Two Rival ApproachesWarehousing ArchitecturesTrade-Offs: MDDB vs. RDBMSHow Long Does a Data Warehousing Project Last?How Is a Data Warehouse Different from Other IT Projects?A Data Warehouse Project is Not a Package Implementation ProjectA Data Warehouse Never Stops Evolving; It Changes with the BusinessData Warehouses Are HugeProject Progress and Effort Are Highly Dependent on Accessibility and Quality of Source System DataWhat Are the Critical Success Factors of a Data Warehousing Project?In Summary
3. Process

6. Warehousing Strategy
Strategy ComponentsDetermine Organizational ContextConduct Preliminary Survey of RequirementsInterview Categories and Sample QuestionsInterviewing TipsConduct Preliminary Source System AuditIdentify External Data Sources (If Applicable)Define Warehouse Roolouts (Phased Implementation)Define Preliminary Data Warehouse ArchitectureEvaluate Development and Production Environment and ToolsIn Summary
7. Warehouse Management and Support Processes
Define Issue Tracking and Resolution ProcessPerform Capacity PlanningDefine Warehouse Purging RulesDefine Security MeasuresDefine Backup and Recovery StrategySet Up Collection of Warehouse Usage StatisticsIn Summary
8. Data Warehouse Planning
Assemble and Orient TeamConduct Decisional Requirements AnalysisDecisional Requirements Analysis Is Working Top-DownConducting Warehouse Planning without a Warehouse StrategyConduct Decisional Source System AuditData Sources Can Be Internal or ExternalDBAs and IT Support Staff Are the Best Resource PersonsDesign Logical and Physical Warehouse SchemaProduce Source-to-Target Field MappingMany-to-Many MappingsHistorical Data and Evolving Data StructuresSelect Development and Production Environment and ToolsCreate Prototype for This RolloutCreate Implementation Plan of This RolloutWarehouse Planning Tips and CaveatsFollow the Data TrailLimitations Imposed by Currently Available DataImprovements to Source SystemsIn Summary
9. Data Warehouse Implementation
Acquire and Set Up Development EnvironmentObtain Copies of Operational TablesFinalize Physical Warehouse Schema DesignBuild or Configure Extraction and Transformation SubsystemsExtraction SubsystemTransformation SubsystemBuild or Configure Data Quality SubsystemCauses of Data ErrorsData Quality Improvement ApproachData Quality Assessment and ImprovementsCorrecting Data Errors at the SourceOther ConsiderationsBuild Warehouse Load SubsystemBasic Features of a Load SubsystemLoading Dirty DataThe Need for Load OptimizationTest LoadsSet Up Data Warehouse SchemaSet Up Warehouse MetadataSet Up Data Access and Retrieval ToolsAcquire and Install Data Access and Retrieval ToolsBuild Predefined Reports and QueriesSet Up Role or Group ProfilesSet Up User Profiles and Map These to Role ProfilesPerform the Production Warehouse LoadConduct User TrainingScope of User TrainingWho Should Attend the Training?Different Users Have Different Training NeedsTraining as a Prerequisite to TestingConduct User Testing and AcceptanceConduct Warehouse TrialsAcceptanceIn Summary
4. Technology
10. Hardware and Operating Systems
Parallel Hardware TechnologyHardware Selection CriteriaIn summary
11. Warehousing Software
Middleware and Connectivity ToolsExtraction ToolsTool SelectionExtraction MethodsTransformation ToolsData Quality ToolsData LoadersDatabase Management SystemsRelational Database Management Systems (RDBMS)Multidimensional Databases (MDDBs)Convergence of RDBMSes and MDDBsMetadata RepositoryData Access and Retrieval ToolsOnline Analytical Processing (OLAP) ToolsReporting ToolsExecutive Information Systems (EIS)Data MiningException Reporting and Alert SystemsWeb-Enabled ProductsData Modeling ToolsWarehouse Management ToolsSource SystemsIn Summary
12. Warehouse Schema Design
OLTP Systems Use Normalized Data StructuresUsers Find Normalized Data Structures Difficult to UnderstandNormalized Data Structures Require Knowledge of SQLNormalized Data Structures Are Not Optimized to Support Decisional QueriesDimensional Modeling for Decisional SystemsTwo Types of Tables: Facts and DimensionsFact TablesDimension TablesFacts and Dimensions in ReportsA Schema Is a Fact Table Plus Its Related Dimension TablesFacts Are Fully Normalized, Dimensions Are DenormalizedDimensional Hierarchies and Hierarchical DrillingThe Time DimensionThe Granularity of the Fact TableThe Fact Table Key Concatenates Dimension KeysAggregates or SummariesComputation of Aggregates is Based on Base-Level SchemasAggregates Have Fewer Records than Do Base-Level SchemasDimensional AttributesMultiple Star SchemasCore and Custom TablesIn Summary
13. Warehouse Metadata
Metadata Are a Form of AbstrationWhy Are Metadata Important?Metadata Establish the Context of the Warehouse DataMetadata Facilitate the Analysis ProcessMetadata Are a Form of Audit Trail for Data TransformationMetadata Improve or Maintain Data QualityMetadata TypesAdministrative MetadataEnd-User MetadataOptimization MetadataVersioningMetadata as the Basis for Automating Warehousing TasksIn Summary
14. Warehousing Applications
The Early AdoptersTypes of Warehousing ApplicationsSales and MarketingFinancial Analysis and ManagementGeneral ReportingCustomer Care and ServiceSpecialized Applications of Warehousing TechnologyCall Center IntegrationCredit Bureau SystemsIn Summary
5. Where to Now?
15. Warehouse Maintenance and Evolution
Regular Warehous LoadsWarehouse Statistics CollectionWarehouse User ProfilesSecurity and Access ProfilesData QualityData GrowthUpdates to Warehouse SubsystemsSource System EvolutionUse of New or Additional External DataDatabase Optimization and TuningData Warehouse StaffingWarehouse Staff and User TrainingUser TrainingWarehouse Staff TrainingSubsequent Warehouse RolloutsChargeback SchemesDisaster RecoveryIn Summary
16. Warehousing Trends
Continued Growth of the Data Warehouse IndustryIncreased Adoption of Warehousing Technology by More IndustriesIncreased Maturity of Data Mining TechnologiesEmergence and Use of Metadata Interchange StandardsIncreased Availability of Web-Enabled SolutionsPopularity of Windows NT for Data Mart ProjectsAvailability of Warehousing Modules for Application PackagesMore Mergers and Acquisitions Among Warehouse PlayersIn Summary
6. Appendices
A. R/OLAPXL® User's Manual
Welcome to R/OLAPXL!Sample Database and ReportsODBC Drivers RequiredThe R/OLAPXL ServerManual ContentsConventions Used in This ManualOther DocumentationSystem RequirementsInstallationInstallation ProcedureSuccessful InstallationUninstalling R/OLAPXLTo uninstall the R/OLAPXL Client Software:TutorialStarting Up R/OLAPXLSelecting a Data SourceOpening a R/OLAPXL ReportRunning the R/OLAPXL ReportPrinting a R/OLAPXL ReportModifying a R/OLAPXL ReportUser's GuideBasic ConceptsCreating a R/OLAPXL ReportSaving R/OLAPXL ReportsClosing a R/OLAPXL ReportWorking with R/OLAPXL ColumnsAdding a R/OLAPXL ColumnMoving a R/OLAPXL ColumnDeleting R/OLAPXL ColumnsUsing Derived ColumnsUsing FunctionsDrilling Up and Down Using R/OLAPXL ColumnsAd Hoc DrillsHierarchical DrillsDrilling Up HierarchicallyDrilling Down HierarchicallyDrilling To HierarchicallyConstraining Your R/OLAPXL WorksheetConstraint Sets in R/OLAPXLSetting R/OLAPXL OptionsRerun Report After DrillingStart CellSuppress Repeating ValuesCascading User Formulas in the Report BodyRunning a R/OLAPXL ReportUsing SubtotalsSubtotals and Column SuppressingSubtotals and SortingSorting R/OLAPXL ReportsSorting and Column SuppressingSorting and SubtotalsUsing Pivot Tables with your R/OLAPXL ReportsPivot Table Data SourcesCommands for R/OLAPXL Pivot Table ReportsNew or View R/OLAPXL Pivot ReportView R/OLAPXL Pivot Report SourceRefresh R/OLAPXL Pivot ReportOther Pivot Table CommandsViewing R/OLAPXL SQL StatementsThe R/OLAPXL ToolbarsThe Standard ToolbarThe Pivot ToolbarMacro ProgrammingSimple Parameterized ReportsWarehouse Schema Knowledge Is RequiredCreating Parameterized ReportsAPI_SetConstraint FunctionAPI_SetConstraintDirect FunctionAPI_Value FunctionR/OLAPXL Sample Retail DatabaseSales FactProduct DimensionTime DimensionPromotion DimensionStore DimensionR/OLAPXL MessagesError MessagesInformational MessagesWarning MessagesPrompts
B. Warehouse Designer® User's Manual
Welcome to Warehouse Designer!Manual ContentsConventions Used in this ManualSystem RequirementsInstallation ProcedureSuccessful InstallationUninstalling Warehouse DesignerBasic ConseptsDimensional Modeling ConceptsBusiness ConceptsThe Warehouse ExplorerThe Warehouse Designer ToolbarsThe [Warehouse Explorer] ToolbarThe Data Forms ToolbarApplicationsDimensionsSchemasCustom DimensionsCustom SchemasAggregate DimensionsAggregate Schemas
C. Online Data Warehousing Resources
Date Warehousing AssociationsOnline ResourcesOnline Magazines and PeriodicalsData Warehousing List ServerNewsgroups
D. Tool and Vendor Inventory
Extraction, Transportation and Transformation TechnologiesData Quality ToolsData Access and Retrieval ToolsData Modeling ToolsDatabase Management ToolsMetadata Management ToolsExternal Data Sources
E. Software License Agreement

Content preview from Data Warehousing: Architecture and Implementation

Appendix B. Warehouse Designer® User's Manual

Welcome to Warehouse Designer!

WAREHOUSE DESIGNER is a dimensional data warehouse modeling tool that enables users to define data warehouse objects through a graphical user interface. It generates the necessary Structured Query Language Data Definition Language (SQL DDL) scripts for creating a data warehouse or data mart based on definitions specified by the user.

WAREHOUSE DESIGNER is fully compatible with R/OLAPXL®, a Relational Online Analytical Processing tool (also provided with this book) which runs on top of Microsoft Excel for Windows 95. WAREHOUSE DESIGNER automatically creates the warehouse metadata that R/OLAPXL needs to access the data warehouse or data mart.

This Demo Installation is provided ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 0130809020Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Data Warehousing: Architecture and Implementation

by Mark Humphries, Michael W. Hawkins, Michelle C. Dy

Appendix B. Warehouse Designer® User's Manual

Welcome to Warehouse Designer!

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.