Business Requirement to Enterprise Data Architecture

OBJECTIVE:

• Understand the role of dimensional modeling in modern analytical data platforms

TOPICS:

• oltp vs. olap systems

• operational databases vs. analytical databases

• enterprise data warehouse

• data mart and lakehouse

• Introduction to the Kimball methodology

• Dimensional modeling vs. normalized modeling

• Star schema vs. snowflake schema

• Analytical workloads and reporting requirements

• Overview of modern data warehouse architecture

PRACTICAL ACTIVITY:

• Examine an operational order-processing database and identify the information required for analytical reporting

ASSESSMENT:

• Identify the differences between an operational model and an analytical model.

OBJECTIVE:

• convert business requirements into a dimensional modeling design.

TOPICS:

• business process identification

• business requirements and analytical questions

• identifying business events and measures

• identifying dimensions and descriptive attributes

• business process matrix

• dimensional bus architecture

• identifying shared dimensions

• translating business requirements into facts and dimensions

PRACTICAL ACTIVITY:

• analyze a retail business case and prepare a business process matrix covering sales, returns, inventory and payments.

ASSESSMENT:

• create a preliminary dimensional design from a business requirements document.

OBJECTIVE:

• design fact tables with the appropriate grain

• measures and dimensional relationships.

TOPICS:

• fact table fundamentals

• defining and documenting fact table grain

• transaction fact tables

• periodic snapshot fact tables

• accumulating snapshot fact tables

• factless fact tables

• additive, semi-additive and non-additive measures

• foreign keys and dimensional relationships

• multiple facts and different grains

• common fact table design mistakes

PRACTICAL ACTIVITY:

• Design sales transaction, monthly inventory snapshot and order fulfillment fact tables.

ASSESSMENT:

• Document the grain and measures for three different business processes.

OBJECTIVE:

• design dimensions that provide consistent and meaningful analytical context.

TOPICS:

• dimension table fundamentals

• surrogate keys vs. natural keys

• dimension attributes and hierarchies

• conformed dimensions

• role-playing dimensions

• degenerate dimensions

• junk dimensions

• mini-dimensions

• date and time dimensions

• handling unknown and not-applicable dimension members.

PRACTICAL ACTIVITY:

• design customer, product, date, location and sales-channel dimensions.

OBJECTIVE:

• Build complete dimensional schemas and evaluate alternative designs.

TOPICS:

• star schema architecture

• snowflake schema architecture

• fact-to-dimension relationships

• one-to-many dimensional relationships

• dimension normalization and denormalization

• shared dimensions across business processes

• multiple fact tables

• multi-star and constellation schemas

• schema design trade-offs

• designing schemas for BI consumption.

PRACTICAL ACTIVITY:

• Convert a business requirements model into a star schema and compare it with a snowflake alternative.

ASSESSMENT:

• Produce a logical star schema diagram and justify its design decisions.

OBJECTIVE:

• Implement historical tracking of changes in dimensional attributes.

TOPICS:

• why dimensional attributes change

• SCD type 0: Retain original value

• SCD type 1: overwrite

• SCD type 2: historical versioning

• SCD type 3: previous value

• effective dates and expiration dates

• current record indicators

• surrogate key management

• late-arriving dimension records

• historical fact association

• SQL implementation of SCD Type 1 and Type 2

PRACTICAL ACTIVITY:

• implement customer and product dimension changes using SQL.

ASSESSMENT:

• design an SCD Type 2 dimension and write SQL to process new and changed records.

OBJECTIVE:

• address complex business requirements using advanced dimensional modeling patterns.

TOPICS:

• multiple fact tables with shared dimensions

• header and line-level transaction facts

• bridge tables for many-to-many relationships

• multivalued dimensions

• allocating measures across bridge relationships

• hierarchical dimensions

• ragged and variable-depth hierarchies

• parent-child relationships

• snapshot facts with changing dimensional relationships

• handling multiple currencies and units of measure

PRACTICAL ACTIVITY:

• design a banking or investment model with customers, accounts, transactions and joint account holders.

ASSESSMENT:

• solve a many-to-many dimensional modeling scenario using a bridge table.

OBJECTIVE:

• Model time-dependent business processes and complex measures.

TOPICS:

• date dimension design

• multiple date roles in a fact table

• business calendars and fiscal calendars

• event-based and periodic measurements

• accumulating snapshots and milestone dates

• inventory and account balance snapshots

• semi-additive balances

• fact table partitioning considerations

• late-arriving facts and dimensions

• handling corrections and restatements

PRACTICAL ACTIVITY:

• model an order lifecycle with order, shipment, delivery and payment milestones.

ASSESSMENT:

• design an accumulating snapshot fact table and demonstrate its analytical use.

OBJECTIVE:

• Translate logical dimensional designs into implementable database structures.

TOPICS:

• logical vs. physical dimensional models

• naming standards and data types

• primary keys and foreign keys

• creating fact and dimension tables using SQL

• surrogate key generation

• loading dimensions and facts

• referential integrity and data quality

• incremental loading concepts

• SQL joins between fact and dimension tables

• query patterns for analytical reporting.

PRACTICAL ACTIVITY:

• Implement a sales star schema using SQL and populate sample data.

ASSESSMENT:

• Create the physical schema and execute analytical queries against it.

OBJECTIVE:

• Understand how dimensional models are implemented in modern cloud data platforms.

TOPICS:

• dimensional modeling in Snowflake

• dimensional modeling in Azure and Microsoft Fabric

• dimensional modeling in Databricks and Delta Lake

• data warehouse vs. lakehouse implementation

• data quality and reconciliation

• performance considerations for analytical workloads

• partitioning, clustering and indexing concepts

• BI semantic models and dimensional relationships.

PRACTICAL ACTIVITY:

• review the implementation of a star schema in a cloud data warehouse and write analytical SQL queries.

ASSESSMENT:

• identify implementation considerations for deploying a dimensional model on a selected platform.

OBJECTIVE:

• Validate dimensional models and improve their suitability for reporting and analytics.

TOPICS:

• reviewing the business process matrix

• grain consistency and dimensional conformance

• fact and dimension validation

• referential integrity checks

• handling duplicate and missing dimension members

• aggregation correctness

• query performance analysis

• avoiding double counting

• model usability for BI developers

• common dimensional modeling anti-patterns.

PRACTICAL ACTIVITY:

• Review a deliberately flawed dimensional model, identify design issues and correct them.

ASSESSMENT:

• Complete a dimensional model review checklist and demonstrate the corrected design.

OBJECTIVE:

• Apply the complete dimensional modeling lifecycle to a realistic business scenario.

TOPICS:

• business requirements analysis

• business process identification

• fact grain definition

• dimension and measure identification

• star schema design

• SCD strategy selection

• physical model creation

• analytical SQL and reporting validation

• model documentation and presentation

PRACTICAL ACTIVITY:

• design a dimensional data warehouse for a retail, banking or logistics business case.

FINAL ASSESSMENT:

• submit and present a complete dimensional model, including business process matrix, schema diagram, table specifications and sample analytical queries.