Dimensional Modeling Services

Kimball Dimensional Design Methodology

Dimensional modeling is a data modeling technique optimized for data retrieval from a data warehouse. By organizing data into fact tables containing measurable events and dimension tables containing descriptive attributes arranged in star or snowflake schemas, dimensional models deliver intuitive structures optimized for query performance and business intelligence.

AGM Network follows the Kimball methodology to design business process-focused models with conformed dimensions, slowly changing dimension handling (Type 1, 2, 3), role-playing dimensions, and bridge tables for many-to-many relationships. Our modeling approach includes business process analysis, grain definition, dimension identification, fact table design, conformed dimension architecture, surrogate key strategy, and aggregate design.

We create dimensional models for finance, sales, supply chain, and operational processes that deliver intuitive BI with fast query performance. Our expertise spans data warehouse design, business intelligence, and integration with modern platforms like BigQuery, Snowflake, and Azure Synapse.

Snowflake Schema Design

Normalized Dimensions: Dimensions split into sub-tables
Storage Efficiency: Reduced data redundancy
Hierarchical Structure: Natural hierarchy representation
Complex Joins: Multiple joins through dimension layers
Data Integrity: Normalized for consistency
Flexibility: Easier dimension updates
Use Cases: Large dimension tables, strict normalization
Hybrid Approach: Combine star and snowflake patterns

Fact Table Design

Transaction Facts: Individual business events
Periodic Snapshots: Point-in-time measurements
Accumulating Snapshots: Process milestones tracking
Factless Facts: Event occurrence without measures
Grain Definition: Level of detail specification
Additive Measures: SUM-compatible metrics
Semi-Additive: Time-constrained aggregations
Non-Additive: Ratios and percentages

Dimension Table Design

Surrogate Keys: System-generated unique identifiers
Natural Keys: Business key preservation
Descriptive Attributes: Who, what, where, when, why
Hierarchies: Drill-down paths for analysis
Role-Playing Dimensions: Multiple fact table roles
Junk Dimensions: Low-cardinality flag consolidation
Degenerate Dimensions: Keys stored in fact table
Outrigger Dimensions: Dimension-to-dimension joins

Slowly Changing Dimensions (SCD)

Type 0: Retain original value (fixed)
Type 1: Overwrite with current value
Type 2: Add new row with version tracking
Type 3: Add column for previous value
Type 4: Mini-dimension for rapid changes
Type 6: Hybrid combining Types 1, 2, 3
Effective Dating: Start/end date tracking
Current Flag: Active record indicator

Conformed Dimensions & Architecture

Shared Dimensions: Reused across multiple fact tables
Enterprise Data Bus: Dimensional matrix design
Consistent Attributes: Same meaning everywhere
Master Data: Single source of truth
Cross-Process Analysis: Drill-across queries
Bridge Tables: Many-to-many relationships
Aggregate Tables: Pre-computed summaries
Bus Architecture: Incremental data warehouse build