Marts and reporting layer
Marts and reporting layer
The Store does not ask Power BI to be the analytical warehouse.
The separation is:
Marts
= governed analytical truth
Reporting layer
= serving tables for Power BI
Both layers can be built in SQL/dbt, but they do not have the same role.
Marts: analytical truth
A mart is the layer where business concepts are stabilized.
It defines information once, with a clear grain, stable keys, and reusable dimensions. A mart table should be able to serve several use cases, pages, or dashboards.
A mart typically contains:
facts at an explicit grain
conformed dimensions
stable keys
clean business definitions
controlled historization
reusable indicators
Examples:
fact_resultats_examens
dim_ecole
dim_date
dim_eleve
dim_matiere
fact_absences_personnel
dim_ecole
dim_date
dim_personnel
dim_corps_emploi
fact_sondages
dim_ecole
dim_campagne
dim_theme
dim_population
A mart answers questions such as:
What is the official grain of this data?
Which school dimension should be used?
How is an absence defined?
What is the stable definition of the success rate?
Should this metric be reusable by several reports?
A mart should not contain a table only because it is convenient for one Power BI page. It should carry definitions that deserve to be governed.
Reporting: the Power BI serving layer
The reporting layer prepares final tables consumed by dashboards.
It can be intentionally denormalized. A reporting table can repeat labels, embed thresholds, precompute variations, and expose directly the columns required by a Power BI page.
It typically contains:
wide tables
one-big-tables
pre-aggregated outputs
calculated values
variations
statuses
labels
thresholds
ranks
scores
composite indicators
sort or display columns
The reporting layer answers questions such as:
Which columns does this Power BI page need?
Which calculations must be ready before the report opens?
Which labels, statuses, or thresholds must be displayed?
Which table makes the Power BI model simpler?
The goal is not to do pure Kimball inside Power BI. The goal is to give Power BI tables that are easy to use, precomputed, and stable from the report's point of view.
Difference between both layers
| Question | Mart | Reporting |
|---|---|---|
| Role | Govern analytical truth. | Serve one or several Power BI reports. |
| Model | Kimball: facts, dimensions, explicit grain. | OBT, wide tables, pre-aggregated outputs. |
| Reuse | Designed for several use cases. | Designed for presentation needs. |
| Grain | Strict and documented. | Adapted to the visual or report need. |
| Calculations | Canonical business definitions. | Presentation calculations, statuses, variations, thresholds. |
| Consumer | Other dbt models, other dashboards, analyses. | Power BI. |
| Risk to avoid | Too much local logic or presentation columns. | Becoming a second business truth. |
Kimball and OBT are not opposed here
In this architecture, Kimball and OBT are not competing choices.
They happen at two different moments in the flow:
Kimball upstream
-> governance, consistency, reuse
OBT downstream
-> Power BI simplicity, performance, readability
A reporting OBT is acceptable when it is fed by clean marts and does not silently redefine business concepts.
Promotion rule
Information can start in the reporting layer when it serves a local need.
It should be promoted to a mart when it becomes reused, stable, or structural.
Information used by one dashboard
-> stays in reporting
Information used by several dashboards
-> candidate for a mart
Cross-domain, strategic, or governed information
-> must be in a mart
This rule prevents the reporting layer from becoming a second business-truth layer.
Promotion examples
These metrics can start in one report, but should be promoted if they become common:
normalized absenteeism rate
school climate index
HR risk score
adjusted success rate
resources-to-results ratio
institutional performance status
The question is not only: "Is this metric useful?"
The right question is:
Should this metric become a shared Store definition?
Filter tables by grain
For Power BI, the reporting layer can expose filter tables adapted to the grain of the domain or report.
Examples:
filter_ecole_annee
filter_sondage_theme_population
filter_resultat_matiere_niveau
filter_rh_ecole_corps_emploi
These tables synchronize filters across pages, control valid combinations, and avoid repeating the same filter columns everywhere.
The principle is to create filter spines by grain rather than one universal filter table that mixes every domain.
Expected role of Power BI
Power BI should mainly:
display
filter
navigate
format
handle user interaction
Power BI should not carry heavy business logic when that logic can be computed in SQL/dbt.
This limits:
diverging definitions across reports
logic hidden in DAX measures
calculations that are hard to test
dependency on the Power BI semantic model
Doctrine
The doctrine can be summarized as:
Marts are the canonical analytical layer, organized with Kimball modeling by business vertical. The Power BI layer does not carry heavy business logic. It consumes denormalized reporting tables, precomputed in SQL, and connected to filter tables by grain. Metrics that become reusable or structural are progressively promoted to canonical marts.