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.

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?

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.

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.

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.

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?

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.

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.

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.