power-bi-datamarts
Miquel Zelich

Miquel Zelich

Business Intelligence Consultant | Data Analyst

Other Articles:

Power BI Datamarts

Share on linkedin
LinkedIn
Share on facebook
Facebook
Share on twitter
Twitter
Share on whatsapp
WhatsApp

Power BI Datamarts were introduced in May 2022 with the idea of taking self-service analytics a step further. With Power BI Desktop being a Windows-only tool, Datamarts are coming to make room for all kinds of analysis to be done in the Power BI Service web portal.

First of all, remember that this feature is only available for Power BI Premium or Premium per User licences.

What are Power BI Datamarts?

Power BI Datamarts are the relational analytics solution backed by an Azure SQL database with a low-code based experience. Basically, Datamarts help business users to develop solutions and take advantage of their data by reducing dependencies on the IT or systems department. Power BI Datamarts allow users to store and explore data by storing it in a fully managed database.

What can we do with Power BI Datamarts?

Extract data from different databases and use the familiar Power Query interface to transform this data and create our Datamart.

Once we have loaded all the selected data into the Datamart we can start to create the model with its relationships.

Power BI Datamarts also allow us to create DAX metrics, but, on the other hand, it is not possible to create calculated columns and tables. But if we stop to think about it, it is not really necessary as we can create these additional columns or tables directly in Power Query. Functionalities such as Row-Level-Security are also available.

Once we have our Datamart created we can write SQL queries and automatically generate a dataset for each Datamart. You can perform ad hoc analysis and create reports, all on the web. Datamarts can serve as a source for the different reports we have to use with the reliability of being used. It is on this last point that, in my opinion, the great potential of Power BI Datamarts lies.

Now business users, who in many cases become the most data literate, can create a Single Source of Truth. Imagine if the finance or human resources team had a single model from which to create the different reports and not having to create a model for each report with its metrics.

Creating a Power BI Datamart

As mentioned above, to create a Power BI Datamart we need a Premium per User licence or that the workspace where we are going to create the Datamart is supported by Power BI Premium Gen2.

How to create Power BI Datamarts

As always in Power BI, the data sources we can choose from are many, starting with Excel files, SQL Server databases or dataflows or any other source owned by other companies.

All this data that we select (database tables, Excel, etc.) will be stored in an Azure SQL database, but first we have to go through the Power Query web interface in order to make the necessary transformations.

Power BI Datamarts Interface Power Query

Once we have finished with the transformations we can save and the data ingestion into the database will start. When the creation of the Power BI Datamart is complete, the data view will open and you will be able to see the data that has been incorporated.

With the Datamart created we can navigate through the different tabs available which are:

  • Data view: We can explore the data just like in Power BI Desktop.
Power BI Datamarts Data View
  • Design view: We can review our transformations and add new data.
Power BI Datamarts Design View
  • SQL View: we can write SQL queries to be able to analyse the data we have.
Power BI Datamarts SQL View
  • Model View: we can create the relationships in our tables to be able to combine fields from different tables
Power BI Datamarts Model View

Finally, in the design view we can create DAX metrics in the same way as in Power BI Desktop:

Power BI Datamarts DAX

Now this Power BI Datamart will be part of the workspace as a dataset and you can design your reports directly and use it as a reliable source of data for the whole department.

Deja un comentario

Your email address will not be published. Required fields are marked *

Business Data Master Logo

No te pierdas el

WEBINAR
Gratuito

Explicaremos en detalle los contenidos y objetivos del Business Data Master

29/11/2021

18:30 (GTM+1)

Online

BUSINESS DATA MASTER

* Tu información será utilizada exclusivamente para contactarte en relación al Business Data Master. No hacemos spam ni compartimos datos con terceros.