A lot can go wrong when a data warehouse is built without a solid architecture. For example, the data reported from an organization's warehouse may not be integrated across the organization, so business users get confusing and conflicting results. And that’s just the beginning. It's critical that a data warehouse support and reinforce the objectives of the entire enterprise, not just a single group.

The choice of architectures ranges from the "integration hub data warehouse" to "independent data marts", and different approaches — including top-down, bottom-up, and hybrid methodologies. This course explains the differences and helps you sort through the choices to determine the best fit for your organization.

We'll examine include the size and scope of the data warehousing program, expected timing and frequency of deliverables, anticipated return-on-investment, staff size and skill, available tools and technology. You'll learn how to assess your resources and requirements, and then make informed decisions about the best data warehousing architectures and methods for your organization.

We will also address how the following components fit into the architectural mix:

  • Operational Data Stores (ODS), data warehouses, and data marts
  • Closed-loop systems such as budgeting and forecasting systems
  • ERP data warehousing products

What you will learn:

  • Basic architectural concepts for business intelligence and data warehousing
  • Industry terminology
  • Data Integration Framework (DIF)
  • Hub-and-spoke, federated, and independent architectures
  • Top-down, bottom-up, and hybrid data warehousing methodologies
  • Dependencies between data warehousing architecture and development methodology
  • How to assess the cost and value implications of various architectures
  • How to assess the time-to-delivery implications of various methodologies
  • Project management implications of various approaches
  • How to determine the best-fit architecture and methodology for your data warehousing program
  • Best practices
  • Industry trends

Who Should Attend:

This course is intended for:

  • Business Intelligence or Data warehousing program managers
  • Business Intelligence or Data warehousing project managers
  • Data architects
  • Anyone who participates in making architecture and methodology decisions for data warehousing and business intelligence
  • Anyone who needs to understand the differences between various data warehousing architectures and methodologies


Two day classroom instruction.

Onsite training has the option of:

  • Two day classroom instruction, or
  • Three day classroom instruction and workshop

Prerequisite Education or Experience

Concepts and Fundamentals for Data Warehousing & Business Intelligence or equivalent knowledge or experience.

Course Outline:

Section 0: Introductions

  • Instructor & class introductions

Section 1: The Architectures

  • The Four Architectures
    • Information Architecture
    • Data Architecture
    • Technology Architecture
    • Product Architecture

  • Data Integration Framework (DIF)
    • Architecture
    • Processes & Data Stores
    • Standards
    • Tools
    • Resources & Skills

Section 2: DIF Processes

  • Data Preparation
    • Data Sourcing
    • Data Cleansing
    • Data Quality
    • Data Transformation
    • Data Loading

  • Data Franchising
    • Data filtering
    • Data Summarization & Aggregation
    • Data Transformation
    • Data Loading
  • Information Access & Analytics
    • Information Access & Reporting
    • Analytics & Performance Management

  • Metadata Management
    • Inter-tool interfaces
    • Audit & What-If Capability

  • Data Management
    • Data Modeling
    • Data Profiling
    • Database Management

  • Workshop Session (below)

Section 3: Data Store Components

  • Data Modeling Basics
    • Conceptual, Logical & Physical Models
    • Entity-Relationship & Dimensional Modeling

  • Data Structure Concepts
    • Facts, Dimensions, Reference
    • Types of Keys

  • Data Structure Options
    • Star
    • Snowflake
    • Normalized (3NF)
    • Denormalized
    • Others
    • Why do these structures matter?

  • Metadata
    • Technical
    • Business
    • Process
    • Why does metadata matter?

  • Workshop Session (below)

Section 4: DIF Data Stores

  • DIF Data Stores
    • Data Sources
    • Data Warehouse
    • Data Marts
    • Cubes
    • Data Shadow Systems
    • Operational Data Stores (ODS)
    • Data Staging
    • Best Practices & Best Fit Considerations

  • DIF Architectural Options
    • Data Warehouse vs. Data Mart
    • Stand-alone, Federated & Hub and Spoke
    • “Closed loop”
    • Comparison of Architectural Options

  • Workshop Session (below)

Section 5: DIF Tools & Technology

  • Extract, Transform & Loading (ETL)
  • Enterprise Information Integration (EII)
  • Enterprise Application Integration (EAI)
  • Data Profiling
  • Data Quality & Cleansing
  • Metadata Management
  • What about unstructured data?
  • Searching for information

Section 6: DIF Standards

  • Project management
  • Software development
  • Technology and products
  • Architecture
  • Data

Section 7: Conclusions

  • Highlights
  • References & Resources

Workshop Sessions:

If three day classroom and workshop option is selected.

Workshops occur after sections 2, 3 & 4 to put in your company’s context

  • Assess Your Current Situation
  • Examine Factors Impacting Decisions
  • Review Architectural Options In Your Context
    • Strengths And Weaknesses
    • Resource, Timing And Cost Considerations

  • Preliminary recommendations for your organization