Update (9/26/2024) At Synthelize we have been transitioning clients away from the Power BI datamarts. After a year and a half of working with the datamarts we still encounter many load failures, data mart editing is still painfully slow, and overall this just does not feel like a good solution for further investment. It appears that Power BI users on Reddit agree and report that they are being given official guidance that datamarts will be deprecated. Therefore we would not advise anyone to build further on this technology unless there is a clear signal from Microsoft that they intend to further develop and support it in the future.
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 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.
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.
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.
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.
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.
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.
The following flow chart should give you an idea of whether Power BI datamarts make sense for your organization:
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.
Synthelize has revolutionized the manner in which our statewide non-profit program tracks, analyzes, and leverages data to improve our outreach efficiency, planning, and effectiveness. . .Synthelize has created an advanced online dashboarding system unique to our program that allows our team to see overall trends, categorize program data and effectiveness, and drill-down into specific areas to crosswalk demographics with program strategizes.
“Synthelize has been an absolute pleasure to work with. Toby always goes above and beyond with all of my requests, and his analysis and report creations are always top-notch. I can't recommend their services enough.”
322 North Shore Drive
Building 1B, Suite 200
Pittsburgh, PA 15212
I am interested in information on:
