Dimensional Model Design Cycle

Dimensional Model Design Cycle

1. Identify Model Components

   • Requirements
     • Document/Study Enterprise Business Processes
     • Select Business Procss to Model
     • Identify High level entities and measures for conformance
     • Identify Data Sources
     • Select requirements gathering approach (Source Driven Or User Driven)
     • Requirements Gathering
     • Requirements Analysis
   • Grain
     • Identify fact table granularity
     • Identify Multiple Separate Grains for a Single Business Process
     • Identify the fact table types (Transaction, Periodic, and Accumulating)
     • Check Grain for atomicity
     • Identify preliminary candidates for dimensions and facts from the grain.
   • Dimensions
     • Determine all dimensions
     • Identify degenerate and conformed dimensions
     • Identify dimensional attributes (granularity) andattribute hierarchies
     • Identify date and time granularity
     • Identify slowly changing dimensions
   • Facts
     • Identify facts
     • Identify conformed facts
     • Identify Fact types (additive, semi additive, non-additive, derived, textual, pseudo or fact-less facts) and default aggregate rules
     • Year-to-date facts
     • Event fact tables
     • Composite key design
     • Fact table sizing and growth

2. Identify business process requirements

   • Best unit of analysis is the business process in which the organization has the most interest
   • Determine when, where, who what, why and how your business interests
   • Identify high level entities and measures for conformance
   • Identify data sources
   • Select requirements gathering approach
   • Requirements gathering
   • Requirements analysis

3. Identify the grain

   • Fact table granularity
   • Multiple, separate grains

4. Identify the dimensions

   • dimension grain
     • granularity of the dimension
   • Less than 15 dimensions
   • Degenerate dimensions
     • Without attributes
   • Conformed dimensions
     • Tables shared in differents models
   • Dimensional attributes and hierarchies
   • Date and time granularity
   • Slowly changing dimensions
   • Fast changing dimensions
   • Cases for snowflaking

5. Identify the facts

   • Conformed facts
   • Fact types
     • Additive
     • Semi-additive
     • Non-additive
     • Derived
     • Textual facts
     • Pseudo
     • Factless fact

6. Verify the model

   • Test the dimensional model to see if it meets the business requirements
   • User verification against business requirements

7. Physical design considerations

   • Aggregations
     • Process of calculating summary data from detail base level table records
   • Aggregate navigation
     • Software that intercepts sql requests and transforms them to use the best available aggregates
   • Indexing
     • Database doesn't need to scan the entire table to look for a row with

8. Meta data management

   • For each model, provide a name, definition, and purpose.
   • should also include hierarchy, change rules, load frequency, and the attributes, facts, and measures associated with the dimension