Power BI Datamarts are the newest major addition to the Power BI ecosystem and an indication of the level of investment and overall direction that Microsoft is taking with the power platform.
In this article, we’ll share an overview of what Power BI Datamarts are, why they’re important, and finally, highlight some real-world use cases. Please note that at the time of writing this article, the Power BI Datamarts feature was still in Public Preview.
This means changes are actively being made to improve the feature, but this doesn’t mean you can’t start taking advantage of it right away!
What are Power BI Datamarts?
Before we jump into the new Power BI Datamarts offering and its benefits, let’s take a step back and start with a broader definition of what a data mart is and its role within an organization.
A data mart is a subject-specific database aligned to the needs of a specific user group. In other words, a data mart is a curated subset of data pertinent to a specific subgroup within an organization and is generally reserved for analytics.
Data marts typically pull data from larger enterprise data warehouses like the Snowflake Data Cloud. For example, finance business analysts may have a separate data mart with data relevant to their business than a team of supply chain business analysts. That way, these business analysts can go to their central location for their day-to-day analyses instead of stitching together queries from multiple data sources.
Now that we’re clear on what data marts are, let’s get into Power BI’s new offering. Power BI Datamarts take several separate legacy functionalities and combine them into a single solution where developers can build self-service data marts.
Where we Started
Here are the previous functionalities that were available (and still are) to build data transformations and shared datasets:
- Dataflow: a tool that connects to data sources, builds transformations in Power Query, and then stores the data in an Azure Data Lake on the back end.
- Shared Power BI Datasets: Power BI datasets that read the results from the Azure Data Lake and then builds calculations, relationships, and measures.
- Power BI Reports: analytical reports using connections to shared datasets.
Where we are now:
Power BI Datamarts package together the components that already exist and provide a single low/no code web UI to perform all of the following tasks:
- Dataflow: perform ETL transformations
- Data Warehousing: an Azure SQL DB that is automatically created, doesn’t require any tuning or optimization and won’t cost anything extra
- Shared Power BI Datasets: an auto-generated semantic layer which is used to create reports and dashboards
Power BI Datamarts provide a low/no-code experience that allows users to ingest data from different data sources, perform ETL tasks with Power Query, and load the data into a fully managed Azure SQL database. Not only does the Azure SQL DB not require any tuning or optimization, but organizations will not be charged extra for it.
Once the data is loaded into the data mart, users can define relationships, as well as policies for business intelligence and analysis. The data automatically becomes available as a semantic layer in the form of a Power BI Dataset within the same workspace that the data mart has been created.
This allows for quick report building across the enterprise while also being able to instill data governance. In addition to all of this, users can query the data mart directly by using a visual query builder within the data mart UI or a T-SQL endpoint.
This low/no-code solution combines the self-service analytics features we have come to know from Power BI with the ability for users to segment and explore their data without needing extensive coding knowledge, existing database architecture, or an IT development team to build any additional reporting structure.
Navigating to Power BI Datamarts
Datamarts can be built directly from a Premium Workspace by clicking the “+ New” button on the top left of the workspace and following the subsequent steps to connect and transform the data. Once it is built, the data mart will automatically create a semantic level Power BI Dataset available in the same workspace with icons in the screenshot below.
Why are Power BI Datamarts Important?
Power BI Datamarts are important because they equip citizen data analysts in a way that was previously not available to them within the Power BI ecosystem. It does this by taking the existing benefits and functionalities of Dataflows, Power BI Datasets, and Azure SQL Databases and combining them into an easy-to-use solution.
Power BI Datamarts not only allow for secure sharing across departmental boundaries, but also to external users who may need to gain access to information within the organization.
Power BI Datamarts also integrate with Dataflows using refresh schedules to optimize the data loads and separate the data preparation steps from the data storage, which allows multiple datasets to leverage the same data. Providing a true citizen data analyst experience, Power BI Datamarts enable users to pursue their own ad-hoc analyses using existing Dataflows and other data sources without the need for IT intervention.
Some of the benefits of Power BI Datamarts include:
- Self-service users can easily perform relational database analytics without the need for a database administrator.
- Power BI Datamarts provide end-to-end data ingestion, preparation, exploration with SQL or using the no-code Visual Editor.
- Simple web UI for no/low code development – can be used anywhere (not just Windows).
- You don’t need to pay for Azure SQL Databases separately.
- Power BI Datamarts need only one scheduled refresh since all components are refreshed together, as opposed to refreshing a Dataflow and then needing to refresh the associated Dataset(s).
- It comes with governance, including Sensitivity Labels.
A couple of limitations that come with Power BI Datamarts include:
- This functionality requires a premium license – either Premium Per Capacity or Premium Per User.
- Support volumes of data up to 100 GBs.
- Power BI Datamarts don’t support the currency data type, these will be converted to a float.
Note: When Power BI Datamarts were first released, they were an “all or nothing” type of option in the Power BI tenant admin portal. Now, users are able to specify certain security groups that are able to create data marts. This should help more administrators enable this feature without the fear of every person in their organization having the ability to create a data mart.
Power BI Datamarts Are Still in Preview!
Datamarts is an exciting feature and we encourage you to test it out! That being said, not all features are final, and updates can be expected shortly. Use this time to provide feedback to Microsoft on how you would like to see the product look in the future.
Conclusion
Datamarts allows us to sort, filter, and aggregate data for ad-hoc analysis through SQL or the no-code Visual Query design, access the data through a SQL endpoint from supported programs and enable users who don’t have access to Power BI Desktop to efficiently comb through your organization’s data.
While this feature is still in preview, it will soon be available with additional features!
