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.
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.
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.
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.
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.
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:
Finally, in the design view we can create DAX metrics in the same way as in Power BI Desktop:
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.
* Tu información será utilizada exclusivamente para contactarte en relación al Business Data Master. No hacemos spam ni compartimos datos con terceros.
