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.
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:
- Fully web-based, no other software is needed.
- They allow users to import from multiple data sources.
- The entire ETL process happens within Datamart. Users can easily create relationships between tables without having to code.
- It gives users the get data experience Power Query gives to get data into Datamart (low code)
- 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.
- Users can also perform Row-level security to both data and datasets and also sensitivity.
- An Azure SQL DB that is automatically created, does not require any tuning or optimization and will not cost anything extra.
- It provides a web UI enabling users to query and perform calculations.
- Users can also write their queries directly for those who are familiar with the Language using the built-in query editor.
Benefits of Datamart
- Self-service users can easily perform relational database analytics without the need for a database administrator.
- Power BI Datamart provides end-to-end data ingestion, preparation, and exploration with SQL or using the no-code Visual Editor.
- No separate payment is required for Azure SQL Databases.
- 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).
- It comes with governance, including Sensitivity Labels
- Simple web UI for no/low code development
- 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!