You may be familiar with terms like Databases, Data Lake, Data Flow, Data Warehouses, and now you’re about to learn about Datamart. This article intends to provide you with all the information you need about Datamart in Power BI and how they differ from the other terms mentioned. Please note that at the time of writing this article, the Datamart feature was still in Preview.

Why Datamart?

Several functionalities already exist in Power BI that perform shared datasets and data transformation so why then do we need Datamart?

Dataflow: Data flow is a tool that performs reusable ETL in the cloud. It is a Power Query process that runs in the cloud, independent of Power BI report and dataset, and stores the data in Azure Data Lake storage.

Data Lake: Data Lake allows businesses to store large amounts of structured and unstructured data (like social media data), and make it available and useful in real-time for analytics, data science, or machine learning. With a data lake, data is ingested in its original form, without alteration.

Data Warehouse: A data warehouse stores structured data from various sources.

Power BI shared dataset: Power BI Dataset also known as datahub houses the data, tables, measures, and relationships between tables. Once a report is published to Power BI service, the actual report and dataset are saved individually. Datasets can be shared between multiple reports and give you the ability to reuse models and measures.

What is Datamart in Power BI?

I would like to start by providing a broader definition of what a Datamart is in Tech/IT and its role within an organization before delving into the new Power BI Datamart offering and its advantages.

A Datamart is a subset of a data warehouse that focuses on a specific department, business unit, or subject matter. A Datamart makes specific data available to a defined group of users within the organization, which allows those users to quickly access critical insights while saving the time spent searching through an entire data warehouse.

Since the focus is usually on a specific department or business unit, Datamart pulls data from fewer sources than a data warehouse hence they are referred to as a subset. For example, the CX team is looking for data to assist them in improving their customer experience during the holiday season, sorting, and merging data that is scattered across various systems/sources could be time-consuming, inaccurate, and ultimately expensive. So having a Datamart specific to all things related to CX would be less time-consuming and more accurate.

Having established what Datamart is, let’s explore what Datamart in Power BI is. Power BI Datamart is the access layer of the Datawarehouse environment that is used to easily analyze and distribute data to the users. In essence, it bridges the gap between users and IT by simplifying creating and managing a data warehouse-like process without IT intervention.

Power BI Datamart provides a single low/no code web UI and combines the functional components of data flow and shared data sets. Features include:

1. Fully web-based, no other software is needed.
2. They allow users to import from multiple data sources.
3. The entire ETL process happens within Datamart. Users can easily create relationships between tables without having to code.
4. It gives users the get data experience Power Query gives to get data into Datamart (low code)
5. Allows users to write DAX, however, calculated columns are supported, instead calculated columns can be created at the transformation stage using Power Query inside of Datamart.
6. Users can also perform Row-level security to both data and datasets and also sensitivity.
7. An Azure SQL DB that is automatically created, does not require any tuning or optimization and will not cost anything extra.
8. It provides a web UI enabling users to query and perform calculations.
9. Users can also write their queries directly for those who are familiar with the Language using the built-in query editor.

Benefits of Datamart

1. Self-service users can easily perform relational database analytics without the need for a database administrator.
2. Power BI Datamart provides end-to-end data ingestion, preparation, and exploration with SQL or using the no-code Visual Editor.
3. No separate payment is required for Azure SQL Databases.
4. Power BI Datamart needs only one scheduled refresh since all components are refreshed together, as opposed to refreshing a Dataflow and then needing to refresh the associated Dataset(s).
5. It comes with governance, including Sensitivity Labels
6. Simple web UI for no/low code development
7. Great news for Mac users since the web experience for data modeling and measures authoring does not require Power BI Desktop.

Who can use Datamart?

Power BI developers, users with no developer background and unfamiliar with SQL but with a Power BI Premium or Premium per user license. Also, users who don’t have access to Power BI Desktop.

Getting Started with Power BI Datamart

You need to create a workspace with a premium or premium per-user license. If you don’t have a PPU license, you can easily apply for a 60-day trial through the Power BI service.

The diamond icon beside the workspace name shows it’s a premium workspace.

Creating a Datamart

To create a Datamart, click on new and then Datamart within the premium workspace you just created.

Give your Datamart a name!

After creating the workspace and Datamart, the step that follows is to Get Data – Power Query Online. Datamart supports more than 120 data sources which include Excel, SQL Server, or even dataflow just like you would in Power BI Desktop. For this example, I’ll work with an excel file, then click on next.

For this example, I’ll work with an excel file, then click on next.

You will see all your tables, click on the tables you need and then click on transform data.

The power query editor will appear and you can transform the data just like you would do in Power Query on Power BI Desktop. Once you have done the transformation, you can then save.

What happens during saving is that it saves the data in data flow and dataset and also loads the data into Azure SQL Database because Datamart is also works as a database.

Clicking on the Datamart icon takes you to a new window that’s similar to the data view in Power BI Desktop, in this window, you can create measures, add data, enter data, and also transform data.

By clicking on any table, you can also add a measure or incremental refresh which is very useful when working with large data sets and you just want to load a part of the data and not the who data for example using time periods.

Working with queries is another great feature of Datamarts. Navigate to the bottom left of the page to use queries. Datamarts allows users query data visually or by writing traditional T-SQL queries.

Users can write SQL queries to query the database, join tables and perform aggregate functions or use the visual query which is ideal for users that are not so comfortable writing SQL queries. The visual query allows users to perform joins using the merge query option.

The modeling tab allows users to create relationships between tables it’s very similar to what we have in Power BI Desktop.

Writing DAX and Creating Measures.

To create a measure, click on a table and then navigate to the reporting tab you’ll see the “New measure” option then type in your measure.

Creating a Report in Datamart

You can also create a report within Datamart by clicking on the new report option in the home tab. The UI is just like Power BI Desktop and users can create and design reports from scratch. However, all your measures will have to be done in the data view.

Row Level Security all in one place

The row-level security feature in Power BI Datamart is very similar to what we have in Power BI desktop but has an advantage because you create row-level security in just one location compared to two-way in Power BI Desktop. The Roles are created and assigned to users, in one place. Unlike the Power BI Desktop which is for role creation and then Power BI service for role assignment.

Conclusion

This article explained what Datamart is, its features and benefits, and who can use it. You’ve also learned how to create a Power BI Datamart, As shown through the example, Datamart gives a single unified editor experience from getting data and transforming it, to writing queries, creating relationships, and Row-Level Security.