Introduction
Picture this: your finance team runs a regional revenue report and gets $142M. Your sales team runs what should be the same report and gets $138M. Both teams are right — they're just pulling from different versions of the "Region" dimension. Finance uses a four-region hierarchy defined in the ERP. Sales uses a six-territory hierarchy managed in Salesforce. Neither version is wrong, exactly, but the fact that they can't reconcile on a shared number means both become untrustworthy. Leadership stops believing the data. Analysts spend days building reconciliation spreadsheets. The BI team gets called in to explain a discrepancy that shouldn't exist.
This is the Master Data Management (MDM) problem in Power BI at enterprise scale. It's not about ETL bugs or misconfigured measures — it's a structural issue. When every team builds their own dimensions — Customer, Product, Geography, Date, Employee — you end up with as many versions of truth as you have teams. The solution is conformed dimensions: shared, authoritative dimension tables that every dataset across the enterprise references rather than reimplements. Getting there requires more than a data modeling decision; it demands a deliberate architecture for building, governing, and distributing those shared dimensions.
By the end of this lesson, you'll be able to design and implement a full shared dimension architecture in Power BI, from sourcing and transforming master data through dataflows, to publishing a certified shared semantic layer that other datasets can connect to via live connections, to handling the tricky edge cases like slowly changing dimensions, ragged hierarchies, and security intersections.
What you'll learn:
- How to architect a Power BI MDM solution using dataflows as the transformation layer for shared dimensions
- How to build a certified shared dataset that serves as the enterprise semantic layer for conformed hierarchies
- How to connect dependent datasets to shared dimensions using cross-workspace live connections
- How to handle slowly changing dimensions (SCD Type 2) and ragged hierarchies within this architecture
- How to govern, monitor, and evolve shared dimensions without breaking downstream consumers
Prerequisites
You should be comfortable with Power Query M code and DAX at an intermediate level. You should understand star schema fundamentals — if you want a refresher, Designing a Star Schema Data Model in Power BI Desktop for Enterprise Reporting covers the modeling theory in depth. You should also have hands-on experience publishing datasets to the Power BI Service and working with workspaces. Familiarity with Power BI Dataflows: Centralized ETL for the Enterprise is strongly recommended since dataflows are the backbone of the transformation layer we'll build here.
Understanding the Architecture Before Writing a Single Line of Code
The single most common mistake practitioners make with Power BI MDM is jumping straight to building a dataset and calling it "the shared one." Six months later it's been cloned six times, each clone has diverged, and you're back to the original problem. Before building anything, internalize the three-layer architecture that makes this work.
Layer 1 — Master Data Source (upstream systems)
This is where your authoritative source records live: an ERP for Customer and Vendor hierarchies, an HR system for Employee org charts, a product information management (PIM) system for the Product catalog, a geography reference database. These are your sources of truth at the data level, and they're messy — inconsistent naming, surrogate keys that clash across systems, missing hierarchy levels, and attributes that haven't been updated since 2019.
Layer 2 — Shared Dimension Dataflows (transformation and conforming)
This is where you clean, conform, and standardize. You build Power BI Dataflows that pull from those upstream sources, apply consistent naming conventions, create surrogate keys that are stable across systems, build hierarchy columns, and output clean dimension tables. These dataflows run on a schedule and are owned by a central data team. This layer produces the "conformed" version of each dimension.
Layer 3 — Shared Certified Dataset (semantic layer)
This is a Power BI dataset — a single semantic model — that imports from those dataflows and exposes the dimensions with their relationships, hierarchies, display formats, and sort orders already configured. This dataset is certified, endorsed, and published to a dedicated "Core Data" workspace. Domain teams connect their own datasets to it via cross-workspace live connections rather than rebuilding dimensions themselves.
Key insight: The power of this architecture is separation of concerns. Dataflows own transformation. The shared dataset owns semantic definition (naming, formatting, relationships, hierarchies). Domain datasets own business logic and fact tables. Each layer can evolve without disrupting the others — as long as the contracts between layers are honored.
Building the Shared Dimension Dataflows
Let's build this concretely. We'll use a retail enterprise as our scenario: one that has Customer, Product, Store (Geography), and Date dimensions that need to be shared across Finance, Sales, Merchandising, and Operations datasets.
Setting Up the Dataflow Workspace
Start by creating a dedicated workspace for your master data dataflows — call it something unambiguous like "Core MDM Dataflows". This workspace should have tight access controls: only the central data engineering team gets Contributor or above. Everyone else gets Viewer access so they can reference the dataflows but not modify them.
In the Power BI Service, navigate to Workspaces, create the new workspace, and under Workspace Settings ensure you've configured a Premium capacity or Premium Per User (PPU) — computed entities (which we'll need for SCD logic) require Premium.
The Customer Dimension Dataflow
Our Customer dimension needs to conform records from two sources: the ERP (which has B2B account hierarchies) and the e-commerce platform (which has B2C individual customers). Here's the M code for the base Customer entity in the dataflow:
let
// Pull from ERP via Azure SQL
ERPSource = Sql.Database(
"erp-sql-prod.database.windows.net",
"OperationsDB"
),
ERP_Customers = ERPSource{[Schema="dbo", Item="DimCustomer"]}[Data],
// Select and rename columns to conformed standard
ERP_Selected = Table.SelectColumns(ERP_Customers, {
"CustomerID", "AccountName", "AccountType",
"ParentAccountID", "SalesRegionCode",
"CountryCode", "StateCode", "PostalCode",
"CustomerTier", "ActiveFlag", "CreatedDate"
}),
ERP_Renamed = Table.RenameColumns(ERP_Selected, {
{"CustomerID", "SourceCustomerID"},
{"AccountName", "CustomerName"},
{"AccountType", "CustomerSegment"},
{"ParentAccountID", "ParentSourceID"},
{"SalesRegionCode", "RegionCode"},
{"ActiveFlag", "IsActive"}
}),
ERP_Tagged = Table.AddColumn(ERP_Renamed, "SourceSystem",
each "ERP", type text),
// Pull from e-commerce platform
EcomSource = AzureStorage.DataLake(
"https://enterprisedl.dfs.core.windows.net/processed/customers/"
),
Ecom_Customers = Csv.Document(
AzureStorage.DataLake(
"https://enterprisedl.dfs.core.windows.net/processed/customers/current.csv"
),
[Delimiter=",", Columns=15, Encoding=65001, QuoteStyle=QuoteStyle.None]
),
// [Ecom transformation steps similar to above, conforming to same schema]
// ...
// Combine both sources
Combined = Table.Combine({ERP_Tagged, Ecom_Tagged}),
// Create a stable surrogate key by hashing source system + source ID
// This ensures keys are consistent across refreshes
WithSurrogateKey = Table.AddColumn(Combined, "CustomerKey",
each Text.Combine({"CUS",
Text.PadStart(Text.From([SourceCustomerID]), 10, "0"),
if [SourceSystem] = "ERP" then "E" else "W"
}),
type text),
// Standardize CustomerSegment to conformed values
WithConformedSegment = Table.TransformColumns(
WithSurrogateKey,
{{"CustomerSegment", each
if _ = "Enterprise" or _ = "ENT" then "Enterprise"
else if _ = "Mid-Market" or _ = "MM" then "Mid-Market"
else if _ = "SMB" or _ = "Small Business" then "SMB"
else if _ = "Consumer" or _ = "B2C" then "Consumer"
else "Unknown",
type text}}
),
// Flag records where CustomerName is null or blank - don't silently drop them
WithDataQuality = Table.AddColumn(
WithConformedSegment,
"DQ_CustomerNameMissing",
each Text.Length(Text.Trim([CustomerName] ?? "")) = 0,
type logical
),
FinalType = Table.TransformColumnTypes(WithDataQuality, {
{"CustomerKey", type text},
{"CustomerName", type text},
{"CustomerSegment", type text},
{"RegionCode", type text},
{"CountryCode", type text},
{"IsActive", type logical},
{"CreatedDate", type date},
{"DQ_CustomerNameMissing", type logical}
})
in
FinalType
Warning: Never use auto-increment integers from source systems as surrogate keys in a shared dimension. If your ERP resets sequences during a migration or your e-commerce platform reassigns IDs, every downstream fact table relationship breaks silently. Use a computed, stable key like the hash-based approach above, or a dedicated key management table.
Notice the DQ_CustomerNameMissing column. Don't hide data quality problems — surface them as boolean flags in the dimension itself. This lets downstream datasets and reports expose data quality indicators without requiring re-transformation.
The Product Dimension with Ragged Hierarchy
The Product dimension is where hierarchy complexity bites. A typical retail product hierarchy looks like: Division → Category → Subcategory → Brand → Product. But not every product has a Brand node (private label products belong directly to Subcategory), making this a ragged hierarchy.
let
Source = Sql.Database("pim-sql-prod.database.windows.net", "ProductCatalog"),
Products = Source{[Schema="dbo", Item="Products"]}[Data],
Hierarchy = Source{[Schema="dbo", Item="ProductHierarchy"]}[Data],
// Flatten the hierarchy into a single wide table
// This is the most compatible approach for Power BI hierarchies
// Level 4 = Product (leaf)
L4 = Table.SelectRows(Hierarchy, each [HierarchyLevel] = 4),
// Level 3 = Brand (may be null for private label)
L3 = Table.SelectRows(Hierarchy, each [HierarchyLevel] = 3),
// Level 2 = Subcategory
L2 = Table.SelectRows(Hierarchy, each [HierarchyLevel] = 2),
// Level 1 = Category
L1 = Table.SelectRows(Hierarchy, each [HierarchyLevel] = 1),
// Level 0 = Division
L0 = Table.SelectRows(Hierarchy, each [HierarchyLevel] = 0),
// Join products to each hierarchy level
JoinedL4 = Table.NestedJoin(Products, "ProductID", L4, "NodeID",
"L4Data", JoinKind.Left),
ExpandedL4 = Table.ExpandTableColumn(JoinedL4, "L4Data",
{"ParentNodeID", "NodeName"}, {"BrandNodeID", "ProductHierarchyName"}),
// For Brand level - use ParentNodeID from L4 to join L3
// Handle ragged hierarchy: if no Brand exists, set BrandName = Subcategory name
JoinedL3 = Table.NestedJoin(ExpandedL4, "BrandNodeID", L3, "NodeID",
"L3Data", JoinKind.Left),
ExpandedL3 = Table.ExpandTableColumn(JoinedL3, "L3Data",
{"ParentNodeID", "NodeName"}, {"SubcategoryNodeID_via_Brand", "BrandName"}),
// Patch ragged hierarchy: products with no brand get BrandName = SubcategoryName
// We'll resolve the parent path below
WithBrandPatched = Table.AddColumn(ExpandedL3, "BrandNameResolved",
each if [BrandName] = null
then "[No Brand - " & [ProductHierarchyName] & "]"
else [BrandName],
type text),
// [Continue joining L2, L1, L0 similarly...]
// Final wide table has columns:
// ProductKey, ProductName, SKU, BrandNameResolved, SubcategoryName,
// CategoryName, DivisionName, IsActive, ListPrice, CostPrice
FinalProduct = Table.SelectColumns(WithBrandPatched, {
"ProductKey", "ProductName", "SKU",
"BrandNameResolved", "SubcategoryName",
"CategoryName", "DivisionName",
"IsActive", "ListPrice", "CostPrice"
})
in
FinalProduct
Tip: The "flatten to wide table" approach for hierarchies is almost always the right call in Power BI. Power BI's native hierarchy feature works best with explicit level columns in a single table. Parent-child hierarchy DAX functions (PATH, PATHITEM, PATHLENGTH) work but add query complexity and are harder for end users to consume. Reserve parent-child DAX patterns for truly variable-depth hierarchies like org charts where the depth is unknown.
Building the Shared Certified Dataset
Once your dataflows are running cleanly, the next step is building the shared semantic model. Create a new Power BI Desktop file — this will become your enterprise shared dataset.
Connecting to Dataflow Entities
In Power BI Desktop, open Power Query Editor and use Get Data → Power Platform → Power BI Dataflows. Connect to your "Core MDM Dataflows" workspace and select the entities you've built: Customer, Product, Store, Date.
This connection means your shared dataset imports from the dataflows, not directly from source systems. That single indirection is enormously valuable — if a source system changes, you update the dataflow, and the shared dataset continues to work unchanged.
Configuring the Dimension Tables
In the model view, disable the load of any intermediate query steps — only the final, clean dimension tables should load into the model. For each dimension table:
- Set the data category on geographic columns (CountryCode → Country, StateCode → State or Province, PostalCode → Postal Code)
- Configure sort-by-column relationships (MonthName sorted by MonthNumber, for example)
- Hide technical columns that consumers don't need (SourceSystem, SourceCustomerID, all DQ_ flags — these are available but not front-and-center)
- Create hierarchies in the model for the fields you've pre-flattened
For the Product dimension, right-click the table in Model view and add a hierarchy named "Product Hierarchy" with levels in order: DivisionName → CategoryName → SubcategoryName → BrandNameResolved → ProductName. This hierarchy will be available in every report that connects to this shared dataset.
The Date Dimension — Build It Once and Never Again
The Date dimension is the most universally shared dimension in any enterprise, and it's also the one most often duplicated. Build it directly in M (not by connecting to a source) so it's self-contained:
let
StartDate = #date(2018, 1, 1),
EndDate = #date(2030, 12, 31),
DateCount = Duration.Days(EndDate - StartDate) + 1,
DateList = List.Dates(StartDate, DateCount, #duration(1, 0, 0, 0)),
DateTable = Table.FromList(DateList, Splitter.SplitByNothing(),
type table [Date = date]),
WithYear = Table.AddColumn(DateTable, "Year",
each Date.Year([Date]), Int64.Type),
WithQuarterNum = Table.AddColumn(WithYear, "QuarterNumber",
each Date.QuarterOfYear([Date]), Int64.Type),
WithQuarterName = Table.AddColumn(WithQuarterNum, "QuarterName",
each "Q" & Text.From([QuarterNumber]), type text),
WithYearQuarter = Table.AddColumn(WithQuarterName, "YearQuarter",
each Text.From([Year]) & "-" & [QuarterName], type text),
WithMonthNum = Table.AddColumn(WithYearQuarter, "MonthNumber",
each Date.Month([Date]), Int64.Type),
WithMonthName = Table.AddColumn(WithMonthNum, "MonthName",
each Date.ToText([Date], "MMMM"), type text),
WithMonthShort = Table.AddColumn(WithMonthName, "MonthNameShort",
each Date.ToText([Date], "MMM"), type text),
WithYearMonth = Table.AddColumn(WithMonthShort, "YearMonth",
each Text.From([Year]) & "-" & Text.PadStart(Text.From([MonthNumber]), 2, "0"),
type text),
WithDayOfWeekNum = Table.AddColumn(WithYearMonth, "DayOfWeekNumber",
each Date.DayOfWeek([Date], Day.Monday) + 1, Int64.Type),
WithDayOfWeekName = Table.AddColumn(WithDayOfWeekNum, "DayOfWeekName",
each Date.ToText([Date], "dddd"), type text),
WithDayNum = Table.AddColumn(WithDayOfWeekName, "DayOfMonth",
each Date.Day([Date]), Int64.Type),
WithIsWeekend = Table.AddColumn(WithDayNum, "IsWeekend",
each Date.DayOfWeek([Date], Day.Monday) >= 5, type logical),
WithDateKey = Table.AddColumn(WithIsWeekend, "DateKey",
each Date.Year([Date]) * 10000 + Date.Month([Date]) * 100 + Date.Day([Date]),
Int64.Type),
// Fiscal year columns — adjust FiscalYearStartMonth to match your enterprise
FiscalYearStartMonth = 7, // July 1 fiscal year start
WithFiscalYear = Table.AddColumn(WithDateKey, "FiscalYear",
each if Date.Month([Date]) >= FiscalYearStartMonth
then Date.Year([Date]) + 1
else Date.Year([Date]),
Int64.Type),
WithFiscalQuarter = Table.AddColumn(WithFiscalYear, "FiscalQuarter",
each let m = Date.Month([Date]),
fq = Number.RoundUp((Number.Mod(m - FiscalYearStartMonth + 12, 12) + 1) / 3)
in "FQ" & Text.From(fq),
type text),
SetTypes = Table.TransformColumnTypes(WithFiscalQuarter, {
{"Date", type date},
{"DateKey", Int64.Type},
{"Year", Int64.Type}
})
in
SetTypes
Mark this table as a Date Table in the model (right-click → Mark as Date Table → Date column). This unlocks DAX time intelligence functions for every dataset that connects to your shared model.
DAX Measures in the Shared Dataset
This is a nuanced design decision. The shared dataset should contain:
- Dimension-level measures: things that are true regardless of which fact table is connected. For example, a measure counting distinct active customers, or a customer hierarchy path measure.
- Utility measures:
[_MeasureShell] measures that document conventions, blank placeholders consumers can override.
The shared dataset should not contain fact-table-specific measures like Revenue, Units Sold, or Budget Variance. Those belong in the domain datasets that connect to this shared model.
Key insight: Think of the shared dataset as the noun layer — it defines the entities, their attributes, and their relationships. Domain datasets are the verb layer — they define what happened to those entities. Keeping these separate allows the noun layer to be stable and trusted while the verb layer evolves independently.
Implementing Slowly Changing Dimensions (SCD Type 2)
Most enterprise dimensions need history. A customer's segment changes. A product moves to a new category. A store changes its regional assignment. Without history tracking, your reports will silently retroactively restate history every time a dimension attribute changes — and you won't know it happened.
SCD Type 2 adds start date, end date, and is-current flag columns to track historical versions of each row. In a dataflow, implement this using computed entities (which require Premium):
// Computed Entity: Customer_SCD2
// This entity references the base Customer entity and maintains history
let
// Load current snapshot from base entity
CurrentSnapshot = #"Customer", // Reference to base Customer entity
// Load previous snapshot from Azure Data Lake
// (persisted by a scheduled export step)
PreviousSnapshot = Parquet.Document(
AzureBlobStorage.GetContent(
"https://enterprisedl.dfs.core.windows.net/mdm-snapshots/customer-prev.parquet"
)
),
// Identify new records (in current, not in previous)
NewRecords = Table.Join(
Table.AddColumn(CurrentSnapshot, "_InCurrent", each true),
"CustomerKey",
Table.AddColumn(PreviousSnapshot, "_InPrevious", each true),
"CustomerKey",
JoinKind.LeftAnti // Records in current that have no match in previous
),
// Identify changed records (same key, different tracked attributes)
// Tracked attributes: CustomerSegment, RegionCode, CustomerTier
TrackedAttributes = {"CustomerSegment", "RegionCode", "CustomerTier"},
JoinedForChange = Table.NestedJoin(
CurrentSnapshot, "CustomerKey",
PreviousSnapshot, "CustomerKey",
"PrevData", JoinKind.Inner
),
// [Expand and compare previous values to current values]
// Records where any tracked attribute differs = changed records
// For changed records in the previous snapshot:
// Set EffectiveEndDate = today, IsCurrent = false
// All current records get:
// EffectiveStartDate = today (or original start if unchanged)
// EffectiveEndDate = null
// IsCurrent = true
// Combine: historical (now-expired) + current versions
FinalSCD2 = Table.Combine({
HistoricalClosedRecords, // Previous records now expired
CurrentActiveRecords // All current records with updated attributes
})
in
FinalSCD2
Warning: Full SCD Type 2 with dataflows requires careful orchestration. The dataflow that reads the "previous snapshot" and the one that writes the new snapshot must be sequenced correctly, and the persistence mechanism (writing to blob storage or ADLS) must be part of your pipeline. If you need full SCD Type 2 at scale, consider building the history logic in Azure Data Factory or Synapse pipelines and using dataflows only for the final conforming step. Don't let perfect be the enemy of good — SCD Type 1 (always overwrite) is correct for many dimension attributes.
Connecting Domain Datasets via Cross-Workspace Live Connections
Now comes the payoff. Your shared certified dataset is published to the "Core Data" workspace. A Finance team analyst wants to build a Revenue dataset that uses the shared Customer, Product, Store, and Date dimensions.
In Power BI Desktop, they use Get Data → Power BI Datasets and connect to the shared dataset. This creates a live connection. They can then switch to DirectQuery mode for the shared dataset's tables, add their own fact tables in import mode — creating a composite model. The result is a dataset that:
- Gets Customer, Product, Store, and Date from the shared certified model (with all the hierarchy definitions, sort orders, and formatting already configured)
- Adds SalesFact, BudgetFact, and finance-specific DAX measures locally
For detailed guidance on how composite models enable this pattern, see Composite Models and DirectQuery: When to Use Which in Power BI — the "mix import and DirectQuery tables" scenario maps directly to what we're building here.
Note: When a domain dataset connects to the shared dataset via live/DirectQuery, changes to the shared dataset (like renaming a column or removing a hierarchy level) will break the domain dataset. This is why governance and impact analysis are non-negotiable parts of the MDM architecture. Always check downstream dependencies before modifying the shared dataset.
The Cross-Workspace Connection in Practice
After connecting to the shared dataset in DirectQuery mode, the Finance analyst opens the model view in Power BI Desktop. They see all the shared dimension tables listed with a small "chain link" icon indicating they come from an external dataset. They cannot modify the structure of those tables directly — column names, data types, relationships between dimension tables are all inherited and locked.
They then add their fact tables. Use Get Data → Azure SQL Database (or wherever the finance fact tables live) and import the relevant tables. In the model view, they draw relationships from their fact tables to the dimension tables inherited from the shared dataset. For example:
SalesFact[CustomerKey] → Customer[CustomerKey]
SalesFact[ProductKey] → Product[ProductKey]
SalesFact[StoreKey] → Store[StoreKey]
SalesFact[DateKey] → Date[DateKey]
The dimension tables behave exactly as they would in a standalone model. The Product Hierarchy defined in the shared dataset appears in the Fields pane and works in visuals just as it would if the dimension were local.
Certification, Endorsement, and Governance
A shared dimension dataset is only valuable if people trust it and use it. The technical architecture is necessary but not sufficient — you need governance to back it up.
Start with dataset endorsement. In the Power BI Service, navigate to the shared dataset's settings and under Endorsement, select "Certified." Certification (as distinct from Promoted) requires a tenant-level setting that restricts who can certify — this should be the data governance team, not individual analysts. This puts a certified badge on the dataset that appears whenever someone searches for it in the data hub.
The certification workflow is covered in depth in Implementing Power BI Dataset Certification and Endorsement Workflows to Establish a Trusted Enterprise Data Catalog, so we won't repeat all of it here. The critical addition for MDM specifically is documenting the canonical definition of each dimension attribute. In the dataset settings description field, include:
- What each dimension represents and its authoritative source
- Which attributes are tracked for SCD Type 2 vs. always-current
- The refresh schedule and who to contact if data is stale
- The change management process (how teams request new attributes)
Row-Level Security Across Shared Dimensions
One complexity that surprises teams: row-level security (RLS) applied in the shared dataset propagates to downstream datasets in some scenarios but not others. If the shared dataset has an RLS role that filters the Store table by region, a composite model dataset that connects to it will inherit that filter.
This can be exactly what you want (Finance only sees stores in their region), or it can cause unexpected blank reports for users who should see all stores. Test RLS propagation explicitly with your specific composite model configuration. For a deep treatment of RLS configuration patterns, Row-Level Security in Power BI is the right reference.
Deployment Pipeline for the Shared Dataset
The shared dataset needs its own deployment pipeline with Dev, Test, and Production stages. Changes to the shared dataset are high-impact — a bad deployment breaks every downstream dataset simultaneously. Your pipeline should:
- Dev stage: Data team makes changes to dataflows and the shared dataset model
- Test stage: Automated validation runs — check row counts, check that all key columns have no nulls, check that hierarchy levels are populated
- Production stage: Requires sign-off from data governance team, not just the data engineer. Downstream dataset owners are notified via automated email before and after deployment.
Use the Power BI REST API to automate the notification step — a simple script that queries the lineage API to find all downstream datasets and sends notification emails to their owners before a production deployment.
Hands-On Exercise: Build a Three-Workspace MDM Architecture
Let's put everything together. This exercise walks you through a realistic implementation using three workspaces.
Setup requirements: Power BI Premium Per User (PPU) or Premium capacity, access to at least one data source (we'll use a public dataset from a SQL Server or you can substitute Azure SQL with a free-tier instance).
Step 1: Create the Workspace Structure
Create three workspaces:
MDM-Dataflows-PROD — holds the shared dimension dataflowsMDM-CoreDataset-PROD — holds the certified shared datasetFinance-Analytics-PROD — holds the domain-specific Finance dataset
Set workspace permissions:
MDM-Dataflows-PROD: only data engineering team as Members/ContributorsMDM-CoreDataset-PROD: data engineering team as Members, all analytics teams as ViewersFinance-Analytics-PROD: Finance BI team as Members, Finance analysts as Viewers
Step 2: Build the Date Dimension Dataflow
In MDM-Dataflows-PROD, create a new dataflow. Add a blank query and paste in the Date dimension M code from earlier in this lesson. Name the entity DimDate. Configure the dataflow to refresh daily at 1:00 AM.
Step 3: Build a Simple Customer Dimension Dataflow
For this exercise, use a public dataset or a SQL Server sample database (AdventureWorks works well — the SalesLT.Customer table gives you enough to work with). Build a dataflow entity that:
- Pulls from your source
- Conforms CustomerName, EmailAddress, and a derived CustomerSegment column (compute it from CompanyName — if null, segment is "Consumer", otherwise "B2B")
- Adds a stable CustomerKey
Step 4: Build and Publish the Shared Dataset
Open Power BI Desktop. Connect to the MDM-Dataflows-PROD workspace dataflows via Get Data → Power BI Dataflows. Import the DimDate and DimCustomer entities.
In the model:
- Mark DimDate as a Date Table
- Create a Date hierarchy: Year → Quarter → Month → Date
- Set MonthName to sort by MonthNumber
- Hide DateKey and other technical integer keys from report view (they're needed for relationships but aren't end-user-friendly)
Write one measure as a quality-check placeholder:
_SharedDimensionVersion =
"MDM v1.0 | Customer: " &
FORMAT(MAXX(DimCustomer, DimCustomer[LoadedDate]), "YYYY-MM-DD") &
" | Date range: " &
FORMAT(MIN(DimDate[Date]), "YYYY-MM-DD") & " to " &
FORMAT(MAX(DimDate[Date]), "YYYY-MM-DD")
This measure surfaces the data currency of the shared dataset in any report that uses it.
Publish to MDM-CoreDataset-PROD. In the Service, certify the dataset and write a description explaining its purpose and the contact person for governance questions.
Step 5: Build the Finance Domain Dataset
Open a new Power BI Desktop file. Use Get Data → Power BI Datasets and connect to the shared dataset in MDM-CoreDataset-PROD. Switch to DirectQuery mode for that connection.
Now add your Finance fact data — either from AdventureWorks SalesLT.SalesOrderHeader / SalesLT.SalesOrderDetail, or from a sample CSV. Import these fact tables.
Create relationships in the model between your fact tables and the shared dimension tables. Write a few Finance-specific measures:
Total Revenue =
SUMX(
SalesOrderFact,
SalesOrderFact[Quantity] * SalesOrderFact[UnitPrice]
)
Revenue YTD =
TOTALYTD(
[Total Revenue],
DimDate[Date]
)
Revenue vs Prior Year =
VAR CurrentRevenue = [Total Revenue]
VAR PriorYearRevenue = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(DimDate[Date]))
RETURN
IF(
ISBLANK(PriorYearRevenue),
BLANK(),
DIVIDE(CurrentRevenue - PriorYearRevenue, PriorYearRevenue)
)
These measures use DimDate[Date] which comes from the shared dataset — the time intelligence works perfectly because you marked the shared table as a Date Table.
Publish to Finance-Analytics-PROD. You now have a working three-workspace MDM architecture.
Common Mistakes & Troubleshooting
Mistake 1: Building the "shared" dataset but letting teams use it optionally
If teams can choose to use the shared dimensions or build their own, most will build their own (because it's faster in the short term). The shared dimension architecture only delivers value when usage is mandated by governance policy. This is a people and process problem, not a technical one.
Mistake 2: Refreshing the shared dataset and downstream datasets simultaneously
If the shared dataset refresh and a downstream domain dataset refresh run at the same time, the domain dataset may query partially-refreshed data. Schedule the shared dataset refresh to complete before downstream dataset refreshes begin. Add at least a 30-minute buffer. Use the Power BI REST API to trigger downstream refreshes only after confirming the shared dataset refresh completed successfully.
Mistake 3: Too many columns in the shared dimension
Resist the temptation to add every possible attribute to the shared dimension "in case someone needs it." Shared dimensions that have 200 columns become unmaintainable. Define a core set of conformed attributes (typically 15-25 columns per dimension) that are truly cross-domain. Team-specific attributes belong in that team's dataset, where they can be managed independently.
Mistake 4: Failing to version the semantic layer
When you rename a column in the shared dataset, every report and downstream dataset using that column breaks. Use a deliberate versioning strategy:
- Keep the old column name as a hidden column for a deprecation period (at least one refresh cycle, preferably longer)
- Add the new column name
- Communicate the change to all downstream owners with a migration deadline
- Remove the old column only after confirming no downstream usage
Workspace lineage analysis in the Power BI Service shows which datasets depend on yours — use it before every structural change to the shared dataset.
Mistake 5: Ignoring dataflow refresh failures
If the shared dimension dataflow fails silently, the shared dataset continues to serve stale data. Domain datasets continue to refresh against that stale data. No alarms go off. Reports look fine. The data is wrong. Set up refresh failure alerting at the dataflow level as well as the dataset level, and make sure those alerts go to someone who will act on them.
Tip: Build a "Data Currency" dashboard that shows the last successful refresh timestamp for every layer of your MDM architecture — dataflows, shared dataset, and each domain dataset. Surface this on your main governance workspace so the data team can see at a glance if anything is stale. This single dashboard has caught more silent failures than any alerting configuration alone.
Summary & Next Steps
You've now seen the complete arc of enterprise MDM in Power BI: from the architectural pattern that separates transformation (dataflows), semantic definition (shared dataset), and business logic (domain datasets), through the concrete implementation of conformed dimensions with proper surrogate keys, ragged hierarchy handling, and SCD Type 2 logic, to the governance practices — certification, deployment pipelines, impact analysis — that keep the architecture working as the business evolves.
The shared dimension pattern is the foundation that makes everything else in enterprise Power BI scale. Without it, every team rebuilds the same dimensions differently, and the data discrepancies compound with every new report. With it, a single authoritative version of Customer, Product, Geography, and Date flows through every dataset in the organization, and when the definition of "Region" changes, it changes in one place.
Where to go from here:
The measure of success for this architecture is simple: when two teams pull a report on the same dimension, they get the same answer. That goal is worth the investment.
