How to Create a Datamart in Power BI

Power BI datamarts give you a powerful way to manage and analyze your business data without waiting on IT. This article provides a straightforward guide on what datamarts are, why they are so useful, and how to create your first one step-by-step.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Exactly is a Datamart in Power BI?

Think of a datamart as your own personal, self-service data warehouse, but simplified and built right into Power BI. It's a fully managed feature that lets you pull in data from various sources, clean it up, model it, and then make it ready for analysis - all within the familiar Power BI web environment.

Traditionally, if you wanted to combine data from, say, your Salesforce CRM, a few Google Sheets marketing trackers, and Google Analytics, you’d often face a few hurdles:

• Manually exporting CSVs and combining them in Excel or Power BI Desktop.
• Waiting for a data engineering or IT team to build a pipeline and model the data in a central data warehouse.
• Dealing with performance issues when your Power BI Desktop file becomes too large and slow.

A datamart solves these problems. It bundles three key components into one package:

1. Data ingestion and transformation (Power Query): It uses the same Power Query editor you know and love from Power BI Desktop and Excel, but it runs entirely online.
2. A fully managed database (Azure SQL DB): Behind the scenes, Power BI automatically provisions and manages an Azure SQL Database to store your data. You don't have to configure, tune, or pay for it separately, it's all handled for you.
3. An auto-generated dataset: Once the datamart is created, it automatically generates a standard Power BI dataset. This is the "semantic layer" that your reports connect to, keeping definitions and calculations consistent.

In simple terms, you get to build a reliable, central source of truth for your team or department without needing to be a database administrator or a data engineer.

Why Should You Use a Datamart? The Core Benefits

Datamarts might seem like just another feature, but they fill a specific and important gap between messy, one-off reports and massive, enterprise-wide data warehouses. Here’s why that matters.

Empower True Self-Service Analytics

The primary benefit is empowerment. A marketing manager can create a datamart that combines spend data from Facebook Ads, Google Ads, and LinkedIn Ads with conversion data from HubSpot and revenue from Shopify. They can build this "single source of marketing truth" themselves, refresh it automatically, and share it with their team to build reports from. No more waiting weeks for a ticket in the IT queue.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Bridge the Gap Between Business and IT

While business users can create datamarts, they aren't a free-for-all. Because the data is stored in an accessible SQL database, IT and data professionals can still connect to it, govern it, and use it for more complex analysis if needed. It creates a space where business users' domain expertise meets IT’s governance and technical skills.

Improve Performance and Scalability

As your Power BI reports grow, loading massive tables of data directly into a PBIX file can make it sluggish and difficult to manage. With a datamart, the heavy lifting of data storage and processing is handled by the underlying Azure SQL database. Your reports simply query this optimized source, resulting in faster load times and snappier visuals. The performance is handled on the server, not your local machine.

Ensure Consistency Across Reports

Have you ever seen two different reports with two different numbers for "Total Revenue"? This often happens when analysts pull data and apply their own business logic independently. Because a datamart generates ONE auto-generated dataset for reporting, everyone connects to the exact same source. If a business rule (like "exclude test orders from revenue") is defined once in the datamart, it's automatically reflected in every single report connected to it.

Prerequisites for Creating a Power BI Datamart

Before jumping into the step-by-step guide, there are a couple of requirements to keep in mind. Datamarts are a premium feature, so you'll need the right license and workspace permissions.

• Licensing: You or your organization must have a Power BI Premium (PPU - Premium Per User) or Power BI Premium (Per Capacity) license. A standard Power BI Pro license won't give you the ability to create datamarts.
• Workspace Permissions: You need to be an Admin, Member, or Contributor in a premium workspace. You cannot create datamarts in the "My Workspace" area.

Step-by-Step Guide: How to Create Your First Datamart

Ready to build? Let's walk through the process of creating a simple datamart. For this example, imagine we are a small e-commerce business, and we want to combine product sales information (from an Excel file) with product inventory details (from another Excel file).

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Navigate to your Power BI Workspace

Datamarts live inside Power BI workspaces. Log in to the Power BI Service (app.powerbi.com) and navigate to the premium-enabled workspace where you want to create your datamart. This workspace could be for your department, like "Marketing Analytics" or "Sales Operations."

Step 2: Create a New Datamart

Inside the workspace, click the + New button located in the top-left menu. From the dropdown list, select Datamart. If the option is greyed out, it’s a sign that your workspace is not on a Premium capacity.

Power BI will take a moment to set up the necessary components. Once it's ready, you’ll be taken to the datamart editor interface.

Step 3: Get Data into Your Datamart

The first thing you’ll see is a familiar dialogue for connecting to data sources. It looks almost identical to the "Get Data" experience in Power BI Desktop.

For our example:

1. Click on Excel Workbook.
2. You'll be prompted to upload a file from your computer or connect via OneDrive/SharePoint. Let's upload a local file named Product_Sales.xlsx.
3. A navigator window will appear, showing you the sheets and tables within the Excel file. Select the table containing your sales data and click Transform data.

You can repeat this process for any other data sources you need. Let’s also add our second file, Product_Inventory.xlsx.

Step 4: Transform Your Data with Power Query Online

This is where the magic starts to happen in a familiar environment. After clicking "Transform data," you are taken directly into the Power Query Online editor. If you've ever cleaned data in Power BI Desktop or Excel, you'll feel right at home.

You can perform all the standard transformations you’re used to:

• Remove unnecessary columns.
• Filter out unwanted rows (e.g., test transactions).
• Change data types (e.g., ensure your OrderDate is a date type).
• Add conditional columns or merge columns.

For our sales data, let's make sure the ProductID column is a Whole Number and the SaleAmount is a Decimal Number. Do a similar check for the inventory data. Once you are satisfied with the cleanup steps, click the Save button in the bottom-right corner.

Power BI will begin loading your transformed data into the Azure SQL database it created for your datamart. This may take a few minutes depending on the size of your data.

Step 5: Explore the Loaded Data

Once the data load is complete, you can explore it in the datamart editor's main window. Notice the three different views available on the bottom-left of the screen:

• Data view: Lets you see the actual data in your tables, just like in Power BI Desktop. You can use this to quickly verify that your data loaded correctly.
• Design view: A visual interface for creating and running queries against your tables. You can drag tables onto the canvas, join them, apply filters, and aggregate data without writing code.
• SQL query view: For more advanced users, this view gives you a direct SQL endpoint to your data. You can write and execute your own SQL queries to analyze the data, which is a massive bonus for those comfortable with SQL.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 6: Build Relationships Between Tables

Your data is clean and loaded, but to get real insights, you need to connect your tables. This is done in the Model view, which can be accessed via the icon on the far left that looks like three connected boxes.

This is, once again, the same experience as Power BI Desktop's model view.

1. You will see cards representing each of your tables (Product_Sales and Product_Inventory).
2. Find the common column that connects them. In our case, this is the ProductID column.
3. Click and drag the ProductID field from the Product_Inventory table onto the ProductID field in the Product_Sales table.
4. Power BI will automatically detect the relationship (typically a one-to-many relationship) and draw a line between the two tables.

You’ve now successfully modeled your data! Any Power BI report connecting to this datamart will automatically understand how sales are related to inventory.

Step 7: Build Reports and Analyze Your Data

Your datamart is now ready for the final step: visualization and analysis. When you created your datamart titled "Ecomm Analytics," Power BI simultaneously created a dataset also named "Ecomm Analytics."

You have a few options to build reports:

• New Report in the Service: In the datamart editor, you can click the New Report button in the top ribbon to immediately start building a report in the Power BI service.
• Connect from Power BI Desktop: This is the most common workflow. Open Power BI Desktop, click on Get Data, and choose Power BI datasets. Select the "Ecomm Analytics" dataset from the list. You'll get access to all the tables you created and the relationships you defined, ready to create visualizations without having to import the raw data again.
• Analyze in Excel: For those who love pivot tables, you can also connect your Excel workbook directly to the Power BI dataset, giving you a live, refreshable feed of governed data for your spreadsheets.

Now, your entire team can connect to this single, reliable dataset to build their own reports, confident they are all using the same approved data and logic.

Final Thoughts

Power BI datamarts finally provide a practical middle ground for departmental analytics. They empower business users to create their own governed, scalable, and performant data sources, taking the pressure off IT and accelerating the time it takes to get from raw data to actionable insights.

Ultimately, data tools are all about making it easier to answer business questions. While tools like Power BI are incredibly powerful, they still involve a significant learning curve. To address this, we created Graphed for teams who want to get straight to the answers. You connect your data sources - like Shopify, Google Analytics, or HubSpot - and simply ask questions in plain English to build dashboards and reports in seconds, enabling anyone on your team to make data-driven decisions without becoming a data expert.