Power BI Datamart gives you a managed relational database, a built-in SQL endpoint, and an auto-generated semantic dataset — all without provisioning a single Azure resource. Learn how to build, govern, and connect to a production Datamart for departmental self-service analytics.
Picture this: your sales operations team needs an analytics solution. They have transaction data in Salesforce, quota targets in SharePoint, and product catalog information in an Azure SQL database. The "right" answer — standing up a dedicated data warehouse, getting the data engineering team involved, modeling dimensions and facts, building ETL pipelines — takes months. The business needs something next quarter. Or next week.
This is exactly the gap Power BI Datamart was designed to fill. It's not a replacement for a proper enterprise data warehouse when you genuinely need one. But for departmental analytics scenarios, cross-functional self-service needs, and situations where a data engineering team is either unavailable or would be overkill, Datamart gives you a managed, cloud-hosted relational database with automatic schema detection, a built-in SQL endpoint, and a Power BI dataset — all wired together and maintained by the Power BI service. You get a real database, real SQL access, and real self-service analytics without provisioning a single Azure resource yourself.
By the end of this lesson, you'll understand how Power BI Datamart actually works under the hood, and you'll know how to build, configure, and govern one for a realistic departmental use case. Specifically:
What you'll learn:
You'll get the most from this lesson if you're already comfortable with:
You'll need a Power BI workspace backed by Premium Per User (PPU) or a Premium/Fabric capacity. Datamart is not available on Pro-only workspaces.
Before writing a single query, you need a clear mental model of what's happening behind the scenes. Datamart is not just a renamed dataflow. It's a layered stack:
Layer 1 — Managed Azure SQL Database: Every Datamart you create provisions a fully managed Azure SQL Database in Microsoft's tenant. You don't see it in your Azure subscription, you don't pay for it separately, and you don't manage the compute. Microsoft handles backups, patching, and scaling.
Layer 2 — Power Query ETL Engine: The same Power Query engine used in Dataflows loads data from your sources, applies transformations, and writes the results into that managed SQL database. Tables in the SQL database map directly to the queries you define.
Layer 3 — Auto-Generated Dataset: After each refresh, Power BI automatically generates a semantic dataset on top of the SQL tables. It detects relationships between tables using primary key and foreign key metadata, infers data types, and builds an initial model you can extend.
Layer 4 — SQL Endpoint: The managed SQL database exposes a TDS-compliant SQL endpoint — the same connection string format as Azure SQL. You can connect SQL Server Management Studio, Azure Data Studio, Excel, or any ODBC-compatible tool directly to it using read-only credentials.
Key insight: The SQL endpoint is genuinely useful for analysts who live in SQL. They don't need Power BI Desktop at all — they can query the Datamart tables directly with T-SQL, join them to other sources in Excel, or feed results into Python notebooks. This is what makes Datamart different from a dataflow: it's not just a stepping stone to Power BI; it's a shareable data asset in its own right.
This architecture means that when you build a Datamart, you're building three things simultaneously: an ETL pipeline, a relational database, and a Power BI dataset. That's the value proposition, and it's also where the complexity lives.
Datamart is the right tool when several of these conditions are true:
Datamart is probably the wrong choice when:
Note: Think of Datamart as filling the gap between "shared Excel spreadsheet" and "full enterprise data warehouse." It's explicitly designed for the scenarios that are too structured for ad hoc files but don't justify the overhead of a formal warehouse project.
We'll build a sales analytics Datamart for a fictional company, Meridian Industrial. Their data lives in three places:
Orders, OrderLines, Customers)This is a real-world combination: structured OLTP data, a business-managed flat file, and a lightweight collaboration tool used as a quasi-database. Datamart handles all three without requiring a separate ETL system.
Navigate to your Premium or PPU workspace in the Power BI Service. Click New and select Datamart (Preview). Give it a meaningful name — in this case, Meridian_Sales_DM. You'll land directly in the Datamart web-based Power Query editor. This editor is functionally similar to the Power Query editor in Power BI Desktop, but it runs in the browser.
Tip: Name your Datamart with an environment prefix and a clear subject area, like
PROD_Sales_DMorDEV_Sales_DM. Once you have multiple Datamarts in a workspace, naming becomes critical for governance. This connects directly to the endorsement and certification workflows covered in Implementing Power BI Dataset Certification and Endorsement Workflows to Establish a Trusted Enterprise Data Catalog.
In the Power Query editor, click Get data and select Azure SQL Database. Enter your server name and database name. For authentication, use organizational account or a service principal — avoid basic username/password for production scenarios.
Once connected, select the Orders, OrderLines, and Customers tables. The editor will create one query per table. At this point, resist the urge to do heavy transformation here — we'll layer that in deliberately.
For the Orders table, you might apply a simple filter to exclude test orders:
// Orders - filtered to production orders only
let
Source = AzureSQLDatabase("meridian-sql.database.windows.net", "SalesDB"),
Orders_Table = Source{[Schema="dbo",Item="Orders"]}[Data],
FilteredOrders = Table.SelectRows(Orders_Table, each [IsTestOrder] = false),
RemovedColumns = Table.RemoveColumns(FilteredOrders, {"IsTestOrder", "InternalNotes", "CreatedBySystemUser"}),
RenamedColumns = Table.RenameColumns(RemovedColumns, {
{"OrderID", "order_id"},
{"CustomerID", "customer_id"},
{"OrderDate", "order_date"},
{"TotalAmount", "total_amount"},
{"RegionCode", "region_code"}
})
in
RenamedColumns
Notice the lowercase snake_case column naming. This is deliberate — when your SQL endpoint users write T-SQL queries against the Datamart, consistent naming conventions dramatically reduce friction. Pick a convention and enforce it across all tables.
From Get data, select SharePoint Online list. Paste in your SharePoint site URL (not the list URL — the site root). After connecting, select your quota list. SharePoint lists come with a lot of noise — created/modified timestamps, content type columns, and various metadata fields that Power Query surfaces by default.
// SalesQuotas - cleaned from SharePoint
let
Source = SharePoint.Tables("https://meridianind.sharepoint.com/sites/SalesOps",
[ApiVersion = 15]),
QuotaList = Source{[Title="Sales Quotas FY2024"]}[Items],
SelectedColumns = Table.SelectColumns(QuotaList, {
"RegionCode", "QuarterLabel", "QuotaAmount", "ProductCategory"
}),
RenamedColumns = Table.RenameColumns(SelectedColumns, {
{"RegionCode", "region_code"},
{"QuarterLabel", "quarter_label"},
{"QuotaAmount", "quota_amount"},
{"ProductCategory", "product_category"}
}),
TypedColumns = Table.TransformColumnTypes(RenamedColumns, {
{"quota_amount", type number},
{"region_code", type text},
{"quarter_label", type text},
{"product_category", type text}
})
in
TypedColumns
Warning: SharePoint lists used as quasi-databases tend to have inconsistent data entry — regions spelled differently, quarters formatted as "Q1 2024" in some rows and "2024-Q1" in others. Always add a transformation step that normalizes lookup values before they hit your Datamart. A lookup or replacement table in Power Query is your friend here. If you leave this to the end users, you'll spend months answering "why don't the quota numbers match?"
From Get data, select Azure Blob Storage and provide your storage account name. Navigate to the container and select the product catalog CSV. Since this is a CSV, Power Query will apply automatic type detection — but you should override it explicitly:
// ProductCatalog - from Azure Blob Storage
let
Source = AzureStorage.Blobs("meridian-products"),
ProductFile = Source{[Name="product_catalog.csv"]}[Content],
ParsedCSV = Csv.Document(ProductFile, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]),
PromotedHeaders = Table.PromoteHeaders(ParsedCSV, [PromoteAllScalars=true]),
TypedColumns = Table.TransformColumnTypes(PromotedHeaders, {
{"product_id", type text},
{"product_name", type text},
{"category", type text},
{"unit_cost", type number},
{"list_price", type number},
{"is_active", type logical}
}),
FilteredActive = Table.SelectRows(TypedColumns, each [is_active] = true)
in
FilteredActive
Rather than leaving all joining logic to the auto-generated dataset, create a consolidated SalesPerformance fact query that enriches order lines with product information:
// SalesPerformance - enriched fact table
let
OrderLines = OrderLines_Base,
Products = ProductCatalog,
// Join product details onto order lines
Enriched = Table.NestedJoin(
OrderLines, {"product_id"},
Products, {"product_id"},
"ProductDetails",
JoinKind.LeftOuter
),
// Expand only what we need
Expanded = Table.ExpandTableColumn(Enriched, "ProductDetails",
{"product_name", "category", "unit_cost", "list_price"},
{"product_name", "category", "unit_cost", "list_price"}
),
// Calculate margin
WithMargin = Table.AddColumn(Expanded, "gross_margin",
each [line_amount] - ([unit_cost] * [quantity]), type number),
WithMarginPct = Table.AddColumn(WithMargin, "margin_pct",
each if [line_amount] = 0 then null
else ([line_amount] - ([unit_cost] * [quantity])) / [line_amount],
type number)
in
WithMarginPct
Tip: You can reference other queries in your Datamart by name, just as you would in Power BI Desktop. Use this to create a staging layer (raw tables named with a
_Basesuffix) and a presentation layer (clean tables without the suffix). The staging tables can be marked as disabled for load if you don't want them materialized to the SQL database — reducing storage and refresh time.
When you click Publish (or save and close the editor), the Datamart refresh process does several things automatically:
The schema detection logic uses a combination of column name matching (a column named customer_id in two tables will be flagged as a potential relationship) and cardinality analysis (the system checks whether the values on one side are unique, identifying the "one" side of a one-to-many relationship).
This is powerful, but it's not magic. Schema detection works well when:
It fails or produces wrong results when:
date column in two fact tables doesn't mean they should be joined)CustomerID while SharePoint uses customer_id)Key insight: Treat automatic schema detection as a first draft, not a finished product. After your first refresh, open the auto-generated dataset in the Datamart interface and audit every relationship it created. Remove any incorrect relationships immediately — a wrong relationship is worse than a missing one because it silently produces wrong numbers. This is the same discipline required when designing a star schema data model in Power BI Desktop for enterprise reporting.
After the Datamart refreshes, click the Model tab in the Datamart editor. You'll see a diagram view of the tables and the relationships the system detected. For our Meridian example, you should see:
Orders → Customers (many-to-one on customer_id) ✓SalesPerformance → Orders (many-to-one on order_id) ✓SalesPerformance → SalesQuotas (incorrectly detected based on region_code match) ✗That last relationship is wrong — sales performance facts don't join directly to quota targets row-by-row; quotas aggregate at the region/quarter level. Delete that relationship and instead let DAX measures handle the quota comparison logic in the dataset layer.
Once your Datamart is published and has completed its first successful refresh, navigate to the Datamart's settings page. Under SQL connection, you'll find a connection string in this format:
Server: powerbi://api.powerbi.com/v1.0/myorg/[WorkspaceName]
Database: [DatamartName]
Wait — that looks like the XMLA endpoint format for Analysis Services, not a SQL connection. That's because Power BI exposes the SQL endpoint through an Azure SQL gateway that translates TDS (Tabular Data Stream) protocol into queries against the managed database. In practice, you connect with SQL Server Management Studio or Azure Data Studio exactly the same way you would connect to Azure SQL Database.
In SQL Server Management Studio, choose Connect → Database Engine and enter:
<your-workspace-sql-endpoint>.datawarehouse.fabric.microsoft.com (the exact format shown in the Datamart settings)Once connected, you'll see the Datamart database with your tables listed under the default schema. You can now write T-SQL:
-- Sales performance by region and quarter with quota attainment
SELECT
sp.region_code,
DATEPART(QUARTER, o.order_date) AS quarter_num,
YEAR(o.order_date) AS order_year,
CONCAT('Q', DATEPART(QUARTER, o.order_date), ' ', YEAR(o.order_date)) AS quarter_label,
SUM(sp.line_amount) AS total_revenue,
SUM(sp.gross_margin) AS total_margin,
AVG(sp.margin_pct) AS avg_margin_pct,
COUNT(DISTINCT o.order_id) AS order_count
FROM SalesPerformance sp
INNER JOIN Orders o ON sp.order_id = o.order_id
GROUP BY
sp.region_code,
DATEPART(QUARTER, o.order_date),
YEAR(o.order_date)
ORDER BY
order_year,
quarter_num,
sp.region_code;
-- Quota attainment by region (joining to quota targets)
WITH RevenueSummary AS (
SELECT
o.region_code,
CONCAT('Q', DATEPART(QUARTER, o.order_date), ' ', YEAR(o.order_date)) AS quarter_label,
SUM(sp.line_amount) AS total_revenue
FROM SalesPerformance sp
INNER JOIN Orders o ON sp.order_id = o.order_id
GROUP BY
o.region_code,
CONCAT('Q', DATEPART(QUARTER, o.order_date), ' ', YEAR(o.order_date))
)
SELECT
r.region_code,
r.quarter_label,
r.total_revenue,
q.quota_amount,
r.total_revenue / NULLIF(q.quota_amount, 0) AS attainment_pct
FROM RevenueSummary r
LEFT JOIN SalesQuotas q
ON r.region_code = q.region_code
AND r.quarter_label = q.quarter_label
ORDER BY r.quarter_label, r.region_code;
This is genuinely useful. SQL analysts can build these queries without ever opening Power BI Desktop. They can schedule them in SQL Server Agent, embed them in Python scripts, or feed results into their own reporting tools.
Warning: The SQL endpoint is read-only. You cannot execute INSERT, UPDATE, DELETE, or DDL statements against it. The only way to modify data in the Datamart is through the Power Query ETL layer. This is by design — it ensures that your data always flows through the governed transformation pipeline — but it surprises SQL analysts who assume a SQL endpoint means full write access.
By default, cloud-to-cloud connections (Azure SQL, SharePoint Online, Azure Blob Storage) don't require a gateway. That covers our Meridian example entirely. But if you're connecting to on-premises SQL Server or file shares, you'll need the on-premises data gateway, configured exactly as you would for any Power BI dataflow.
For Datamart refresh scheduling, navigate to the Datamart in the workspace and open its settings. Under Refresh, you can configure:
One critical difference from regular dataset refresh: Datamart does not currently support incremental refresh through the standard Power BI UI. If you need incremental loading behavior, you have to implement it in Power Query yourself using a "last refresh timestamp" parameter and filtering source queries accordingly. This is a meaningful limitation for large datasets and is worth factoring into your architecture decision.
Datamart supports row-level security in Power BI through the auto-generated dataset layer, not at the SQL endpoint layer. This is an important distinction.
What this means in practice:
To configure RLS on the Datamart dataset, click on the Dataset tab within the Datamart editor. From there, navigate to the model view and define roles using DAX filter expressions on your tables:
// Region Manager role - restricts to rows matching the logged-in user's region
[region_code] = LOOKUPVALUE(
RegionManagerMapping[region_code],
RegionManagerMapping[email],
USERPRINCIPALNAME()
)
This assumes you've loaded a RegionManagerMapping table into your Datamart that maps email addresses to region codes. That mapping table should itself be a query in your Datamart, sourced from your authoritative HR or CRM system.
Warning: If analysts query the SQL endpoint using a shared service account (a common pattern for connecting Excel or BI tools), RLS will not apply — all rows will be visible to whoever has the service account credentials. For sensitive data, enforce per-user AAD authentication at the SQL endpoint and document this requirement explicitly in your Datamart's governance documentation.
A Datamart without governance is just a database that nobody trusts. The endorsement model that applies to Power BI datasets also applies to Datamarts. In the workspace, you can mark a Datamart as Promoted (any workspace admin can do this) or Certified (requires tenant-level certification authority permissions).
Certified Datamarts appear with a badge in the Power BI data hub and in the dataset picker when analysts create new reports. This is your primary mechanism for steering users toward trusted data assets rather than building their own shadow solutions.
For a deeper look at the certification workflow, see Implementing Power BI Dataset Certification and Endorsement Workflows to Establish a Trusted Enterprise Data Catalog.
You should also configure the Datamart's sensitivity label through the tenant settings if it contains personally identifiable or commercially sensitive information. This integrates with Microsoft Information Protection and ensures that data exported from the Datamart (including via SQL endpoint) carries appropriate classification labels. More on this in Implementing Power BI Tenant Settings and Sensitivity Labels for Enterprise Data Protection and Compliance.
There are two ways to build reports against your Datamart:
Option 1 — Use the auto-generated dataset directly. In Power BI Desktop, connect via Live Connection to the Datamart's auto-generated dataset. This gives you access to all the tables, relationships, and any measures you've added in the model layer. This is the recommended path for most report authors — they get a pre-built, trusted semantic layer without touching the raw data.
Option 2 — Connect via DirectQuery to the SQL endpoint. For scenarios where you need columns or relationships that the auto-generated dataset doesn't expose, you can connect Power BI Desktop directly to the Datamart's SQL endpoint as an Azure SQL Database source and use DirectQuery. This trades the convenience of the semantic layer for raw flexibility.
Tip: For most departmental scenarios, Option 1 is the right call. You get the governance, RLS, and caching benefits of the semantic layer. Use Option 2 only when you have a specific reporting requirement that the auto-generated dataset genuinely can't satisfy. When you choose Option 2, you're essentially bypassing the Datamart's semantic layer and connecting directly to the database — which partially undermines the self-service value proposition of the Datamart.
Work through this exercise to build the complete example described in this lesson.
What you'll need:
Step 1: Create and name the Datamart
In your workspace, click New → Datamart. Name it Meridian_Sales_DM.
Step 2: Create staging queries
Create three "base" queries (one per source) using the M code patterns shown earlier in this lesson. Name them Orders_Base, OrderLines_Base, ProductCatalog_Base, Customers_Base, and SalesQuotas_Base. These are your raw ingestion layer.
Step 3: Create presentation queries
Create a new query called SalesPerformance that references OrderLines_Base and ProductCatalog_Base, joins them, and adds the margin calculations. Create a query called Orders that references Orders_Base with filtering applied. Repeat for Customers and SalesQuotas.
Disable the _Base queries from loading to the database by right-clicking each one in the query list and unchecking Enable load. This keeps your SQL schema clean.
Step 4: Publish and review the schema
Click Save and allow the Datamart to refresh. Navigate to the Model tab and audit every relationship that was auto-detected. Remove any incorrect relationships. Verify that primary keys were correctly identified on your dimension tables.
Step 5: Connect with SSMS
Copy the SQL connection string from the Datamart settings. Connect with SSMS using your AAD credentials and run the quota attainment query from earlier in this lesson. Verify the results match your expectations.
Step 6: Add an RLS role
In the Dataset tab, create a role called RegionManager with a filter on the Orders table restricting region_code to the value returned by USERPRINCIPALNAME() matched against a mapping table.
Step 7: Certify the Datamart
If you have certification authority in your tenant, promote and certify the Datamart. If not, promote it and document who should be contacted to request certification.
Step 8: Connect a report
Open Power BI Desktop and connect via Live Connection to the Datamart's auto-generated dataset. Build a simple matrix visual showing revenue and margin by region and quarter, plus a KPI card showing overall quota attainment.
This almost always happens when two tables share a column with the same generic name — date, id, code — that means different things in each context. The fix is prevention: rename columns in your Power Query transformations to be table-specific and unambiguous. order_date instead of date; customer_id instead of id. After fixing the naming, delete and recreate the Datamart (or just delete the incorrect relationships manually in the Model view and update the column names for future refreshes).
First, check that the Datamart has completed at least one successful refresh — the SQL endpoint isn't available until after the first refresh populates the database. Second, verify that you're using AAD authentication and that your account has access to the workspace. Third, check whether your organization's conditional access policies block the SQL endpoint port (1433 outbound). This requires IT involvement.
Datamart Power Query runs in the Power BI service, not locally. Complex M transformations that work fast on your local machine may be slow in the service because they can't leverage your local compute resources. The most common culprit is row-by-row operations — any use of Table.AddColumn with a function that can't be folded to the source. Investigate query folding: right-click any step in the query editor and look for the "View Native Query" option. If it's greyed out, that step and all subsequent steps are executing in the M engine rather than being pushed to the source database. Push as much filtering, column selection, and type conversion as possible into steps that fold.
Tip: For Azure SQL sources, query folding works well through joining, filtering, column selection, and grouping. For SharePoint and CSV sources, folding stops at the point of ingestion. Accept this and minimize the data volume at ingest time by filtering rows and selecting only necessary columns as your first transformation steps.
Tables only appear in the SQL database and the auto-generated dataset if "Enable load" is turned on for that query. Check whether you accidentally disabled load on the table in question.
As covered in the RLS section: RLS applies only when users authenticate with their own AAD credentials through the Power BI SQL endpoint. If they're using a connection string with a shared service account, they bypass RLS entirely. Audit how your SQL endpoint is being consumed and enforce per-user authentication where data sensitivity requires it.
The auto-generated dataset inherits the workspace permissions of the Datamart. Users need at least Viewer access to the workspace to see the Datamart in the data hub. If you want to share the dataset more broadly without granting workspace access, you can share the dataset directly using the standard dataset sharing workflow — this works the same way as sharing any Power BI dataset, as described in Implementing Power BI Dataset Sharing and Cross-Workspace Live Connections to Build a Reusable Enterprise Semantic Layer.
Power BI Datamart solves a real problem: it gives departmental teams a governed, relational data asset with SQL access and an auto-generated semantic layer, without requiring a dedicated data engineering team or Azure resource provisioning. The automatic schema detection is genuinely useful as a starting point, but it requires audit and correction after every significant schema change. The SQL endpoint is a first-class citizen in the Datamart architecture — not an afterthought — and its read-only nature is a feature, not a limitation.
The pattern we built for Meridian Industrial — staging queries that are disabled from loading, presentation queries that define the SQL schema, deliberate column naming for schema detection, and RLS layered on the auto-generated dataset — is applicable to virtually any departmental analytics scenario.
Where to go from here:
The real measure of a successful Datamart isn't whether it refreshes cleanly — it's whether analysts actually trust it enough to stop building their own spreadsheets. Getting the naming conventions right, reviewing the auto-detected schema, and applying proper endorsement are what convert a technical artifact into a genuine organizational data asset.