Overview
Store architecture
General flow
The target flow is:
Sources
->
Interfaces and staging
->
Kimball marts by business vertical
->
Denormalized reporting layer
->
Power BI
This architecture is intentionally hybrid. Marts keep a Kimball logic to stabilize business concepts. The reporting layer prepares wide, flattened, precomputed tables to simplify Power BI.
Layer roles
| Layer | Main role |
|---|---|
| Interfaces and staging | Read sources, select, rename, and normalize columns. |
| Marts | Stabilize reusable facts, dimensions, grains, and business definitions. |
| Reporting | Prepare final tables, often denormalized, expected by dashboards. |
| Power BI | Present data, filter, navigate, and handle user interaction. |
Guiding principle
Heavy business logic should be computed in SQL/dbt, in a versioned and testable layer. Power BI should remain a presentation layer.
SQL/dbt computes
Power BI presents
Page to read
The Marts and reporting layer page explains each layer's role, the difference between Kimball and OBT, and the metric-promotion rule.
Running the ETL
dbt has a built-in orchestrator that allows you to run the ETL. Only enabled models, using the +enabled: true syntax, will be run. As all models are disabled by default, only the models or dashboards you manually enable will be run.
Marts and reporting layer
The Store does not ask Power BI to be the analytical warehouse.