How to Create a Data Mart in Power BI

Creating analytics reports in Power BI often starts the same way: you connect to data sources, clean them up in Power Query, and build a data model. But what happens when that model needs to serve multiple reports or be accessed by different people in different ways? This is where Power BI datamarts come in, offering a more robust, shareable, and scalable solution. This tutorial will walk you through exactly what a datamart is, why you should use one, and how to create your first one step-by-step.

What is a Power BI Datamart?

Think of a Power BI datamart as a supercharged, self-service version of a traditional data mart, all built and managed directly within the Power BI service. It's a fully web-based, no-code experience that packages up your data ingestion, storage, and modeling into a single, cohesive unit. This approach bridges the gap between simple, self-service reports and complex, IT-managed data warehouses.

Every datamart you create in Power BI is made up of three core components that work together automatically:

• Dataflow: This is a Power Query process that handles the extraction, transformation, and loading (ETL) of your data. If you've ever used Power Query in Power BI Desktop or Excel, the experience here is nearly identical. You connect to your sources and clean your data here.
• Azure SQL Database: This is the game-changer. Once your data is transformed by the dataflow, Power BI automatically loads it into a fully managed Azure SQL Database. You don't have to configure, tune, or pay for this database separately - it just works. This provides a scalable and high-performance backbone for your analytics.
• Semantic Model (Dataset): Power BI also creates a web-ready semantic model, or dataset, on top of the Azure SQL DB. This is where you build relationships between your tables, write DAX measures, and set up security. It's this component that report designers connect to in Power BI.

In short, a datamart hands you a complete analytics pipeline - from data source to a queryable SQL database and a ready-to-use Power BI dataset - without you ever needing to write SQL or involve an IT administrator.

Why Use a Datamart Instead of a Regular Dataset?

You might be wondering why you'd go through the process of creating a datamart when a standard Power BI dataset has served you well. Datamarts offer several distinct advantages, especially as your data scale and user needs grow.

It's More Than Just a Power BI Model

A standard dataset can only be accessed through Power BI reports or an "Analyze in Excel" connection. A datamart, however, gives you direct access to the underlying Azure SQL Database through a T-SQL endpoint. This means you can connect to your curated data using other tools, including:

• SQL Server Management Studio (SSMS)
• Azure Data Studio
• Any other client tool or application that can query a SQL database

This opens up your data to a wider audience, including data analysts who prefer writing SQL queries or developers building custom applications, all while leveraging the data you’ve already cleaned and modeled.

Scalability and Performance

Because the data is stored in an optimized Azure SQL Database, queries and report interactions can perform better, especially with larger data volumes. The processing workload is handled by the database engine, lightening the load on the Power BI analysis engine that renders your visuals.

Centralized and Governed Self-Service

A datamart provides a "single source of the truth." Instead of each analyst creating their own separate dataset from the same sources - a process that often leads to inconsistencies - they can all build their reports from one centrally governed datamart. You can define measures, roles, and security rules once, and everyone who uses the datamart will automatically inherit them.

Unlock A Completely No-Code Experience

Building a traditional data warehouse requires technical expertise in database management, SQL, and data pipeline tools. A datamart provides all of that power in a user-friendly, no-code web interface. All the data loading, database creation, and performance tuning happens automatically in the background.

Before You Begin: Prerequisites

Before jumping in, there are a few important requirements to be aware of. Because datamarts use a powerful backend infrastructure, they aren't available on all Power BI plans.

Licensing Requirements

To create or use datamarts, your organization needs to have one of the following licenses:

• Power BI Premium Per Capacity (P SKU)
• Power BI Premium Per User (PPU)

Datamarts are unfortunately not available with a Power BI Pro or free license. You must also be working in either a "My Workspace" under a PPU license or a Premium workspace.

Workspace Permissions

To create a datamart, you need to have an Admin, Member, or Contributor role in the Premium workspace. Viewers can consume reports built on top of datamarts, but they cannot create them.

Step-by-Step Guide: Creating Your First Power BI Datamart

Let's walk through the process of building a datamart from scratch. For this example, we’ll imagine we're connecting to some public OData about product sales.

Step 1: Create a New Datamart

1. Navigate to the Power BI Service (app.powerbi.com) and select a Premium-enabled workspace from the left-hand navigation pane.
2. From within your workspace, click the + New button and select Datamart from the dropdown menu.

Power BI will take a moment to provision the necessary resources. Once it's ready, you'll be taken to the datamart editor interface.

Step 2: Get and Transform Your Data

The first thing you'll see is the familiar "Get Data" screen. This experience is powered by Power Query Online, which looks and feels just like the Power Query Editor in Power BI Desktop.

1. Click Get data or choose a common source. For our example, we'll choose OData Feed.
2. A dialog will appear asking for connection settings. We'll use the sample Northwind OData feed URL:
3. After connecting, the Navigator window will appear, listing all the available tables. Let's select Orders, Order_Details, and Products, then click Transform data.

This opens the full Power Query editor right in your browser. Here you can perform any clean-up and transformation steps needed, such as:

• Removing unwanted columns
• Filtering rows
• Changing data types
• Merging or appending queries

Once you're satisfied with your transformations, click Save in the bottom right corner. This starts the backend process where your dataflow runs, and Power BI loads the transformed data into the automatically provisioned Azure SQL Database.

Step 3: Define Your Data Model

After the data is loaded, you can now define the business logic. Along the bottom-left of the datamart editor, you'll see different views very similar to Power BI Desktop.

1. Click on the Model view icon (it looks like three connected boxes).
2. Power BI might have automatically detected relationships, but you can confirm, edit, or create them here. To create a relationship, simply drag a field from one table and drop it onto the corresponding field in another table. For our example, we would:

Step 4: Create Measures with DAX

To make your data easy for business users to analyze, you need to create measures. You can add new measures directly from the ribbon in the Model view.

1. On the ribbon, click New measure.
2. This will open the DAX formula bar. Let's create a simple measure to calculate total sales revenue. We'll assume the Order_Details table has UnitPrice and Quantity columns.

Total Sales = SUMX(Order_Details, Order_Details[UnitPrice] * Order_Details[Quantity])

Press Enter to save your measure. You can now add other foundational measures like Total Quantity, Unique Products, etc., right here in the web editor.

Using and Managing Your New Datamart

With your datamart modeled and ready, now comes the fun part: using it.

Building Reports

From the datamart editor, click the New report button on the main ribbon. This will open the Power BI report builder in a new tab, already connected to your datamart's semantic model. You can begin dragging your tables and measures onto the canvas to build visuals immediately. Alternatively, users can connect to the shared dataset from Power BI Desktop to build reports there.

Connecting with SQL

To use the powerful T-SQL endpoint, you first need the connection string.

1. In your workspace, find your datamart and click the three-dot menu (...).
2. Go to Settings.
3. Under "Server settings," you'll find the SQL connection string. Copy it.

You can now paste this into a tool like SSMS, select "Azure Active Directory - Universal with MFA" for authentication, sign in with your Power BI credentials, and start running SQL queries directly against your data.

Setting Up Refreshes

To keep your data current, you'll want to schedule it to refresh automatically. In the datamart's settings, you can configure both semantic model refreshes and datamart refreshes. The datamart refresh runs the entire dataflow to pull new data into the Azure SQL DB, and the semantic model refresh updates the dataset that your reports connect to.

Final Thoughts

Power BI datamarts are a significant step forward, combining the powerful, user-friendly data transformation of Power Query with the robust performance of an Azure SQL Database. This lets business users create and manage a controlled, scalable data analytics solution from end-to-end without deep technical expertise, making a single source of truth more achievable than ever before.

For organizations not yet on a Power BI Premium plan or for teams who find even this streamlined process too technical, there are simpler ways to achieve similar outcomes. We created Graphed because we believe anyone should be able to get answers from their data without learning a new tool or managing data pipelines. By connecting sources like Google Analytics, HubSpot, or Shopify in a few clicks, you can use simple English to build instant, real-time dashboards that your entire team can use to make smarter, faster decisions.