You are previewing The Analytical Puzzle: Profitable Data Warehousing, Business Intelligence and Analytics.
O'Reilly logo
The Analytical Puzzle: Profitable Data Warehousing, Business Intelligence and Analytics

Book Description

Do you enjoy completing puzzles? Perhaps one of the most challenging (yet rewarding) puzzles is delivering a successful data warehouse suitable for data mining and analytics. The Analytical Puzzle describes an unbiased, practical, and comprehensive approach to building a data warehouse which will lead to an increased level of business intelligence within your organization. New technologies continuously impact this approach and therefore this book explains how to leverage big data, cloud computing, data warehouse appliances, data mining, predictive analytics, data visualization and mobile devices.

Here are the main objectives for each of the book's 19 chapters:

  • Chapter 1: Develop a foundational knowledge of data warehousing, business intelligence and analytics

  • Chapter 2: Build the business case needed to sell your data warehousing project, and then produce a project plan that avoids common pitfalls

  • Chapter 3: Elicit and organize business intelligence and data warehousing business requirements

  • Chapter 4: Specify the technical architecture of the data warehousing system, including software and infrastructure components, technology stack, and non-functional requirements. Gain an understanding of cloud based data warehousing and data warehouse appliances

  • Chapter 5: Learn about data attributes including metrics and key performance indicators (KPIs), the raw material of data warehousing and business intelligence

  • Chapter 6: Learn about data modeling and how to apply design patterns for each part of the data warehouse

  • Chapter 7: Speak the dimensional modeling language of measures, dimensions, facts, cubes, stars, and snowflakes

  • Chapter 8: Organize a successful data governance program. Learn how to manage metadata for your data warehousing and business intelligence project

  • Chapter 9: Identify useful data sources and implement a data quality program

  • Chapter 10: Use database technology for your data warehousing project, and understand the impact of data warehouse appliances, big data, in memory databases, columnar databases and OnLine Analytical Processing (OLAP)

  • Chapter 11: Apply data integration and understand the role data mapping, data cleansing, data transformation, and loading data play in a successful data warehouse

  • Chapter 12: Use the business intelligence (BI) operations of slice, dice, drill down, roll up, and pivot to analyze and present data

  • Chapter 13: Learn about descriptive and predictive statistics, and calculate mean, median, mode, variance and standard deviation

  • Chapter 14: Harness analytical methods such as regression analysis, data mining, and statistics to make profitable decisions and anticipate the future

  • Chapter 15: Appreciate the components and design patterns that compose a successful analytic application

  • Chapter 16: Gain an understanding of the uses and benefits of scorecards and dashboards including support of mobile device users

  • Chapter 17: Gain insight into applications of business intelligence that could profit your organization, including risk management, finance, marketing, government, healthcare, science and sports

  • Chapter 18: Perform customer analytics to better understand and segment your customers

  • Chapter 19: Test, roll out, and sustain the data warehouse

Table of Contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. Contents at a Glance
  5. Table of Contents
  6. Acknowledgements
  7. Introduction
    1. Who Should Read this Book
    2. Prerequisites
    3. How to Benefit from this Book
    4. Chapter Expectations
    5. Learn More
    6. Case Study: 3M a Global Manufacturing Firm
    7. Companion Website and Templates
    8. About the Author
    9. Confessions of the Author
  8. Chapter 1 Data Warehousing Perspectives
    1. What are Business Intelligence (BI) and Analytics?
    2. Decisions Impact the Bottom Line
    3. Examples of Business Intelligence Results
    4. What is Enterprise Data Warehousing (EDW)?
    5. What a Data Warehouse Is and Is Not
    6. Beyond Data Warehousing
    7. Assessing Data Warehouse BI Maturity
    8. DW and BI are Management Disciplines
    9. Operational Data vs. Data Warehouse Data
    10. High Quality Data
    11. Decision Support Goals
    12. Data Warehousing Enterprise Architecture
    13. Data Warehousing Trends and Hot Topics
    14. Learn More
  9. Chapter 2 Business Case and Project Management
    1. Six Steps to Developing the Business Case
      1. BC Step 1.0 – Initiate Business Case
      2. BC Step 2.0 – Analyze Current Approach
      3. BC Step 3.0 – Analyze Alternative Approaches
      4. BC Step 4.0 – Determine Transition Costs
      5. BC Step 5.0 – Assemble Business Case
      6. BC Step 6.0 – Present Business Case
    2. Case Study: 3M, Global EDW Business Case
    3. Data Warehousing Project Management
      1. Defining Scope and Objectives
      2. Finding the Right Sponsor
      3. Producing the Project Roadmap and Plans
      4. Organizing the Team
      5. Training the Team
      6. Executing the Plan
      7. Finishing the Project
    4. Case Study: 3M, Enterprise Data Warehouse Continued
    5. Project Management Tips
      1. Avoiding Major Data Warehouse Missteps
    6. Learn More
  10. Chapter 3 Business Architecture and Requirements
    1. Business Architecture
    2. Data Warehousing Business Requirements
      1. Homework for Data Warehouse Requirements Elicitation
      2. Identify Business Intelligence User Groups
      3. Data Exploration
      4. Interview Business Intelligence Users
      5. Group Methods
      6. Documenting Requirements
      7. Functional Requirements
      8. Non-Functional Requirements
    3. Case Study: 3M, a Global Manufacturing Firm
    4. Data Warehousing Requirements Workshop
      1. Roles and Responsibilities
      2. Preparing for the Session
      3. Executive Sponsor and User Manager Roles
      4. Room Layout
      5. Group Methods and Techniques
      6. Outcomes of Effective BI Requirements Sessions
      7. Requirements Session Follow Up
    5. Learn More
  11. Chapter 4 Data Warehousing Technical Architecture
    1. Technical Architecture
      1. Functional and Non-functional Requirements
      2. Technical Architecture Principles
      3. Buy, Build, or Re-use Approach
      4. Technical Architecture Roadmaps
    2. Data Architecture
    3. Data Warehousing Technical Architecture
      1. Data Sources
      2. Data Integration
    4. Case Study: 3M, a Global Manufacturing Firm
    5. Data Storage
    6. Data Warehouse vs. Data Mart
    7. BI / Analytics
    8. Data Governance, Metadata and Data Quality
    9. Data warehousing Infrastructure
    10. Data Warehousing Technology Stack
    11. Data Warehouse in the Cloud
    12. Managing, Operating, and Securing the Data Warehouse
    13. Data Warehouse Architecture Tips
    14. Data Warehouse Architecture Traps
    15. Learn More
  12. Chapter 5 Data Attributes
    1. Raw Data
    2. Attributes
    3. Qualitative and Quantitative Attributes
    4. Naming Attributes
    5. Key Performance Indicators (KPIs)
    6. Learn More
  13. Chapter 6 Data Modeling
    1. Data Modeling Tools
    2. Data Modeling Levels
    3. Industry Data Models
    4. Section 6A – Entity Relationship Modeling
    5. Entities
    6. Attributes
    7. Relationships
      1. Cardinality
    8. Exclusive Supertype / Subtype
    9. Non-Exclusive Supertype / Subtype
    10. Derived Data
    11. Normalization
      1. First Normal Form
      2. Second Normal Form
      3. Third Normal Form
      4. Fourth Normal Form
      5. Fifth Normal Form
    12. Summary: The Entity Relationship Model
      1. Real World Data Models
    13. Learn More
    14. Section 6B – Data Warehouse Modeling
    15. Source Systems
    16. Landing Stage
    17. Delta Stage
    18. Atomic Stage
    19. Support Tables
    20. Atomic Data Warehouse
    21. Learn More
  14. Chapter 7 Dimensional Modeling
    1. Dimensional Data Modeling
    2. Star Schema
    3. Example Star Schema
    4. Facts – the Data Mart Measuring Stick
    5. Granularity
    6. Fact Width and Storage Utilization
    7. Mathematical Nature of Facts – Can it “Add up”?
    8. Event or Transaction Fact
    9. Snapshot Fact
    10. Cumulative Snapshot Fact
    11. Aggregated Fact
    12. “Factless Fact”
    13. Dimensions Put Data Mart Facts in Context
    14. Dimension Keys
    15. Data Modeling Slowly Changing Dimensions
      1. SCD Type 0 – Unchanging Dimensions
      2. SCD Type 1 – Overwrite
      3. SCD Type 2 – New Row
      4. SCD Type 3 – New Column
    16. Date and Time Dimension
    17. One Dimension – Multiple Roles
    18. Snowflake Schema
    19. Dimensional Hierarchies
    20. Bridge Tables Support Many to Many Relationships
    21. Degenerate Dimension
    22. Profile Dimension
    23. Junk Dimension
    24. Specifying a Fact
    25. Specifying Data Mart Attributes
    26. The Big Picture – Integrating the Data Mart
    27. Data Warehouse Bus
    28. The Bigger Picture – The Federated Data Mart
    29. Learn More
  15. Chapter 8 Data Governance and Metadata Management
    1. Five Steps to Data Governance (DG)
      1. DG Step 1.0 – Assess Data Governance Maturity
      2. DG Step 2.0 – Design Data Governance Structures
      3. DG Step 3.0 – Create Data Governance Strategy
      4. DG Step 4.0 – Create Data Governance Policies
      5. DG Step 5.0 – Monitor and Maintain Data Governance
    2. Metadata – “Data about data”
    3. Data Warehouse Metadata
    4. How Can Data Warehousing Metadata be Managed?
    5. Metadata Manager / Repository
    6. Learn More
  16. Chapter 9 Data Sources and Data Quality Management
    1. Understanding Data Sources
    2. Identifying Data Sources
      1. Data Source Questions
      2. Dimension Data Sources for the Data Mart
    3. Identifying Fact Data Sources for the Data Mart
      1. Detailed Data Source Understanding for Data Warehousing
    4. Obtain Existing Documentation
    5. Model and Define the Input
    6. Profile the Data Source
    7. Data Profiling and Data Quality
    8. Grey Data
    9. Six Steps to Data Quality Management (DQM)
    10. Learn More
  17. Chapter 10 Database Technology
    1. Relational Databases
    2. Big Data – Beyond Relational
    3. Federated Databases
    4. In Memory Databases
    5. Column Based Databases
    6. Data Warehouse Appliances
    7. OLAP Databases
      1. ROLAP Uses SQL for Business Intelligence
      2. MOLAP Business Intelligence Benefits
    8. Learn More
  18. Chapter 11 Data Integration
    1. Data Integration
    2. Data Mapping
    3. Extracting Data to Staging Area
    4. Applying Data Transformations
    5. Loading the Data
      1. Loading the Atomic Data Warehouse
      2. Loading the Data Mart
      3. Loading Data Mart Dimensions
      4. Loading Data Mart Facts
    6. ETL Tools
      1. Programmer Written ETL Applications
      2. Data Warehousing ETL Tools At Hand
      3. COTS Dedicated ETL Tools
      4. Open Source ETL
    7. Learn More
  19. Chapter 12 Business Intelligence Operations and Tools
    1. Section 12A – Business Intelligence Operations
      1. Slice and Dice
      2. Roll Up
      3. Drill Down
      4. Pivot
      5. BI Operations Tips and Traps
    2. Section 12B – Business Intelligence Tools
      1. Interactive Query and Analysis Tools
      2. Reporting Tools
      3. Data Visualization Tools
      4. Data Mining and Statistical Tools
      5. Evaluating BI Tools
    3. Learn More
  20. Chapter 13 Number Crunching Part 1: Statistics
    1. Collecting Data
    2. Descriptive Statistics
    3. Measures of Dispersion
    4. Inferential Statistics – Regression Models
    5. R and Octave Open Source Software
    6. Learn More
  21. Chapter 14 Number Crunching Part 2: Data Mining
    1. Predictive Data Mining
    2. Descriptive Data Mining
    3. Clusters
    4. Predictive Data Mining – Decision Trees
    5. Predictive Data Mining – Neural Nets
    6. Data Mining Tips and Traps
    7. Learn More
  22. Chapter 15 Number Crunching Part 3: An Analytic Pattern
    1. Analytical Application Example Architecture
  23. Chapter 16 Presenting Data: Scorecards and Dashboards
    1. Scorecards
      1. Obtaining Scorecard Data
      2. Scorecard Tips and Traps
    2. Dashboards
      1. Obtaining Dashboard Data
      2. Dashboard Tips and Traps
    3. Data Visualization Graphics Types
    4. Mobile Device Considerations
    5. Learn More
  24. Chapter 17 Business Intelligence Applications
    1. Financial BI Applications
    2. Supply Chain and Manufacturing BI Applications
    3. Operations BI Applications
    4. Performance Management BI Applications
    5. Risk Management BI Applications
    6. Government BI Applications
      1. Case Study: Richmond, VA Policy Department
  25. Chapter 18 Customer Analytics
    1. Single Customer View
    2. Common Customer Data Model
      1. Identifiers
      2. Demographics
      3. Psychographics
      4. Locations (Geographics)
      5. Products
      6. Interactions
      7. Transactions
      8. Measures
      9. Relationships / Hierarchies
      10. Single Customer View Tips and Traps
    3. Customer Segmentation
    4. Bands
    5. Clusters
    6. Customer Analytics Terms
    7. Analysis Types
    8. Learn More
  26. Chapter 19 Testing, Rolling Out, and Sustaining the Data Warehouse
    1. Section 19A – Testing the Data Warehouse
      1. Data Warehouse Testing Responsibilities
      2. Business Requirements and Testing
      3. Data Warehousing Test Plan
      4. Testing Environments and Infrastructure
      5. Unit Testing for the Data Warehouse
      6. QA Testers Perform Many Types of Tests
      7. Can the Data Warehouse Perform?
      8. Business Users Test Business Intelligence
      9. Business Intelligence Must Be Believed
    2. Section 19B – Business Intelligence Rollout
      1. Pre-deployment for Business Intelligence Rollout
      2. Deployment of Data Warehouses
      3. Training Business Intelligence and Data Warehouse Users
      4. Follow-up Support for the Data Warehouse
      5. Business Intelligence Follow-up Assessment
    3. Section 19C – Sustainable Business Intelligence
      1. BI / Data Warehousing People and Process Tips
      2. BI / Data Warehousing People and Process Traps
      3. Sustaining BI / Data Warehousing Tips
      4. Sustaining BI / Data Warehousing Traps
  27. Appendix A Glossary
  28. Appendix B Suggested Readings
    1. Data Warehouse
  29. Appendix C References
  30. Index