Dimensional Modeling Process Overview

Designing the Dimensional Model

Modeling Process Overview

The dimensional modeling process involves creating a clear and effective design for data warehouses. Key stages include:

  • Graphical design from bus matrix: It sets the foundation for the design work by visualizing the structure.

  • Scope of design: Define the boundaries of what the model will cover.

  • Identify grain of facts: Determining the level of detail at which data will be captured is fundamental. For example, will it capture data at the individual transaction level or aggregated over a period?

  • Identify dimensions: Dimensions provide the context to the facts; for instance, sales data may be broken down by time, geography, or product categories.

  • Reviewing and validation of models: Ensure the model fits the business requirements through feedback and iteration.

  • Evolution of models: Models are expected to go through multiple iterations based on user feedback and shifting business needs.

  • Testing against business requirements: Ensures the model meets the defined specifications and works as intended.
    Overall, this process may span three to four weeks, depending on complexity.

Preparation for Modeling

Before starting, several preparatory steps include:

  • Business Requirements Document: A critical document outlining user needs.

  • High-Level Bus Matrix: This matrix helps visualize the overall structure and relationships within the data.

  • Preliminary Data Profiling: To understand the existing data sources and their characteristics.

  • Tools: Use naming conventions and establish a high-level dimensional model.
    Session activities should result in:

  1. An initial high-level model diagram.

  2. A list of attributes and metrics needed.

  3. An issues list to address in the detailed design phase.

Dimensional Model Review and Validation

Involving multiple stakeholders is crucial during the validation phase:

  • IT, core users, and the business community should all provide input on the revised designs.

  • After reviewing, update the detailed dimensional design worksheet and issues list continuously until a final design is documented.

Identification of Design Participants

Successful modeling necessitates a diverse group of participants with distinct roles, including:

  • Data Modeler: Responsible for the design, serving as facilitator.

  • Power User: Provides business requirements and insights based on their expertise.

  • Business Analyst: Analyzes the business needs and source systems.

  • Source System Developers: Offer insight into the data sources.

  • ETL Architect and Developer: Ensures that data is extracted, transformed, and loaded appropriately.

  • Business Governance Committee: Ensures alignment on definitions and business rules.
    Each participant plays a role in ensuring the accuracy and functionality of the dimensional model.

Revisiting Requirements

It is vital for the modeling team to thoroughly comprehend business challenges and opportunities. They must translate business needs into a flexible model supporting varying analytical requests. The requirements documentation should include:

  • Proposed data elements

  • Sample questions for analysis

  • Desired report outputs
    Skimming this step may lead to inadequate design and functionality.

Modeling Tools and Techniques

  • Begin modeling using spreadsheets for flexibility and ease of adjustments. Later, implement advanced tools that offer metadata storage and export features. These tools aid DBAs in further engineering the model to a database format (e.g., tables, indexes).

Dimension Design

Success in dimension management includes:

  • Ensuring clarity in naming and definitions

  • Documenting naming conventions to maintain consistency across models

  • Establishing relationships for dimensional attributes and preparing for data profiling to understand data structure and quality effectively.

Detailed Dimensional Model Development

The detailed dimensional model enriches the high-level views, identifying necessary attributes and metrics while continuously validating against the business's evolving requirements. The process involves:

  • Identifying data source locations and their organization.

  • Selecting candidate data sources based on the bus matrix and requirements.

  • Conducting thorough data profiling to ensure data quality at ETL stages.

Documenting Designs

Documentation is crucial through all phases for stakeholders to understand and communicate effectively. This includes:

  • A detailed design spreadsheet capturing attributes and metrics for each dimension and fact table.

  • Rules defining how each fact is to be treated within the model (e.g., whether additive, semi-additive).

Update Bus Matrix and Resolve Modeling Issues

The bus matrix should be updated throughout the design process. This tool captures new elements, facilitates communication among teams, and resolves any issues encountered during modeling. Regularly revisiting and revising this document ensures coherence and clarity.

Final Steps: Review and Validation

The final phase consists of comprehensive reviews involving all stakeholders to validate the design's effectiveness, ensuring alignment with the originally documented requirements. Documentations should be finalized for future reference and physical implementation.