Power BI Datamarts Review

Power BI Datamarts Review Image

As of the date of this article (10/30/2023) Power BI datamarts are still in PUBLIC PREVIEW mode. This means that though Microsoft encourages you to use the functionality there still might be unresolved bugs and issues and the design of the service might change before the final release date. Power BI datamarts should not currently be used for any mission-critical functionality.

What are Power BI Datamarts?

Microsoft has recently been developing something called the Microsoft Fabric platform, which is designed to make it easier for organizations to deploy analytics solutions in Office 365 and to Microsoft Azure. These services include storage (through Azure Data Lake), data integration (through Power Query and DataFlows), and data visualization (through Power BI).

A part of the new Microsoft Fabric offering is the new Power BI/Microsoft Fabric Datamart. Power BI datamarts are data structures that can combine ETL dataflows and data storage into one "entity" that can be managed, secured, and reported upon.

Power BI Dataset Plus Dataflow

DataMarts = Datasets + Dataflows

Power BI datamarts are essentially a marriage between two pieces of the Power BI puzzle - data sources and data flows. In a desktop Power BI report the data source, data visuals, and data transformation are combined into one .pbix file. However, when a report is deployed to Power BI Online the file is broken into the visual component (called "Report"), and the data/logic behind the report (called "Dataset"). This separation is important in that it allows the organization to refresh or export the data from the dataset independent, but it has an important limitation - you cannot EDIT the logic within the Power BI portal. To update the dataset you must edit the logic inside the .pbix file and re-publish it.

Why did Microsoft create Power BI datamarts?

Many organizations or organizational departments find themselves in an awkward position where their IT needs have risen above the level of paper and spreadsheets but have not quite reached the point where a full IT/data analytics team is justified. These organizations might have a few team members who are technologically savvy but lack the resources or buy-in of a larger organization to develop a full-fledged analytics infrastructure. Microsoft's goal is to give these organizations more self-serve analytics capability.

What are the advantages of Power BI datamarts?

Advantage 1 - Datamarts allow departments to build longer term data structures without going through central IT

At its heart the biggest problem Power BI datamarts are trying to solve is that they seek to help smaller groups of data analyts to create shared data and ETL repositories without having to build/purchase full-blown data warehouses. In the past organizations might have solved their data sharing problems by asking central IT to build a shared data warehouse for their data. This request might have been ignored or deferred to a later date. Power BI datamarts allow organizations who are already using Power BI to create their own mini data warehouse to serve their needs without going through central IT.

Advantage 2 - Datamarts encourage the consolidation of ETL and business logic across reports

As soon as an organization needs to create more than one Power BI report a problem becomes readily apparent: there is not a good way to share logic between individual Power BI reports. Most organizations have certain calculations that they perform on their data for analysis. These might be related to the business calendar, or regional distribution of offices, or separate product lines. If this business logic is not well represented in the data source itself it needs to be created either through DAX expressions or Power Query. However, there is no easy way to copy that logic from one Power BI report to another. This means that when the logic changes it must be changed and updated in every single Power BI report.

Power BI datamarts allow you to use Power Query to define this business logic ONCE in ONE LOCATION and then make the results of those calculations available to many different reports.

Advantage 3 - Datamarts enable collaboration between data analysts and report designers

An organization might have several data analysts supporting a team of business power users and other end users. A Power BI datamart provides a shared environment where multiple data analyts can build the backend calculated tables for reports, share logic and data between reports, and provide report creators one central repository to run against for report creation.

Advantage 4 - Datamarts enable the use of different platforms and remote work on Power BI data

Not everyone in an organization has access to a desktop/laptop computer running a copy of Power BI. Datamarts allow users to work on data ETL logic on their device, whether that device is based on Windows, Mac, Linux, or some other mobile operating system. This is especially important as people are more mobile than ever and may be required to fix problems without having access to a copy of Power BI desktop.

What are some limitations of Power BI datamarts?

Limitation 1 - Only One Data Refresh Allowed Per 24 hours

Though the new datamart functionality is very powerful, it comes with a major limitation. As of now, Power BI datamarts can only be scheduled to refresh once every 24 hours. This makes Power BI datamarts a nonstarter for any reports that require several scheduled refreshes within the day.

We are hoping that Microsoft will allow more frequent refreshes in the future, though it is understandable considering a Power BI datamart might contain hundreds of tables and a lot of complex ETL logic that puts a strain on servers.

As of now Power BI datamarts are best used to drive aggregate reports that are time-bounded on the scale of weeks, months, or years, as in these scenarios a daily refresh is more than sufficient.

Limitation 2 - There is no easy way to back up a Power BI datamart

Power BI Datamart Backup ETL Image

Once you begin to create anything that is more than a few tables in the Power BI datamart you will want to have a way to back it up, ESPECIALLY during the public preview period of the datamart functionality. Unfortunately there is no easy way to back up the datamart ETL logic. This is a serious downside to putting your logic in the datamart and something Microsoft should really address before the datamart functionality goes out of public preview.

There is a hack to "back up" the Power Query ETL logic. When you create a datamart, make sure that all of your "transform data" Power Queries are stored in folders. If you right-click on a folder and choose "copy", this will copy all the Power Query statements for every query in the folder to the clipboard at once. This will allow you to paste the logic into your own text files to save. You can "restore" query logic by copying from your textfile and then selecting "paste" on the folder where you wish to place the queries.

Limitation 3 - Power BI Datamart editing is slow

As a Power BI datamart grows it becomes increasingly difficult to edit and save the datamart structure and new ETL queries. Testing the results of a query often involves saving and reloading the entire datamart and that can be particularly time consuming when the datamart grows large.

Limitation 4 - Direct Query mode only access mode allowed during Preview

While Microsoft may change its mind when Power BI Datamarts leave Preview Mode to General Availability, for now you can only reference a Power BI Datamart in Direct Query mode. This means that you CANNOT use Power Query to manipulate the results of a query into something more suitable for your Power BI report. This is certainly frustrating. However, since Power BI Datamarts have Power Query functionality built in one can simply move whatever tranformation logic was going to be in the end Power BI report into the datamart itself.

Are Power BI datamarts right for you?

The following flow chart should give you an idea of whether Power BI datamarts make sense for your organization:

Should You Use A Power BI Datamart?

Looking for a technology partner who can help you navigate Power BI datamarts and other data strategy concerns?

Get in touch with us via the contact below and we will be glad to reach out to you about your organization's unique needs.

Contact Us

322 North Shore Drive

Building 1B, Suite 200

Pittsburgh, PA 15212

Phone number
+1 (412) 230-8552

I am interested in information on: