Power BI Datamarts: Bridging the Gap Between IT and Business Users

Introduction to Power BI Datamarts

For everyday business function, Power BI Datamarts are positioned to become a strong wielding component to help bridge the gap between business users and the IT team. This new feature in Power BI allows for business users to extract data from different sources, define relationships, and store and explore that data using Power BI, Power Pivot, and even SSMS. Within datamarts, data is stored in a fully managed Azure SQL Database with no need for IT to provision or manage that data. By combining the features of Dataflows, Azure SQL Database, Datasets, and Power BI Desktop into a single solution, IT teams and business users can use Power BI Datamarts to improve efficiency within their business practices.

The Benefits of Power BI Datamarts

To understand the use and functionality of Power BI Datamarts, it’s essential to understand the main benefits for business users and IT teams. These benefits have been proven to increase efficiency in day-to-day business processes, and as said previously, have helped to bridge the typically large gap between the technology team and other business members.

Some of the benefits of Power BI Datamarts include:

Ingest and Prepare Data from Disparate Sources

Datamarts can connect to all of the data sources you’re familiar with in Power BI.  Using Power Query, this data can be easily filtered and transformed based on your business rules.  Conversely, a datamart can also be sourced from an existing data warehouse to provide a business unit with a subset of data specific to their needs.

Add Business Semantics

It’s not often that all of our data can be sourced from a single table.  Our data gains value when it’s combined with other data or augmented by calculated metrics and KPIs. Prior to datamarts, relationships and measures could only be created in the dataset.  This development experience could get cumbersome if the dataset got very large.  And while datasets could be shared by multiple reports, allowing for reuse of the measures and model, it wasn’t possible to create a composite model with a shared dataset and other data sources.  With datamarts, relationships and measures are created within the datamart development interface.  These can then be selectively ingested by other datamarts or datasets as needed across the organization.

Eliminate The Need for an IT-Managed Database Platform But Still Get a Fully Managed Database Platform

For the first time, business users have access to create their own fully managed database environment, without needing to involve IT resources, and without worrying about additional licensing or Azure costs.

When you create a datamart, Power BI creates a fully managed Azure SQL Database behind the scenes.  This database can be accessed (and queried) from other SQL tools, like SSMS, allowing users to query the datamart data in an ad-hoc manner (which is invaluable during the development process).

Web Interface

Apple Macbook users rejoice!  With datamarts comes a brand-new web development environment that allows users to perform data modeling and create measures without running Power BI Desktop.  The entire datamart solution can be built and managed in a browser.

Datamarts vs. Dataflows vs. Datasets

Power BI Datamarts combine the functionality and features of different database processes including dataflows and datasets, but you may be wondering where the differentiators lie.

First, let’s identify what each one is and what it does:

• Datasets – define a semantic layer for data reporting, create relationships between tables, and define measures for business calculations
• Dataflows – build reusable ETL to prepare data; data is stored behind the scenes in a data lake; cannot browse the data, query it, define relationships, or create measures
• Datamarts – combines the functionality of data flows and datasets, and adds on Azure SQL Database for data storage and a Web UI to build and explore the data

When deciding which one to use for a process or project, you want to consider the benefits and limitations for dataflows, datasets, and datamarts. Dataflows are typically used in cases where you are building ETL for tables and shared dimensions that can be leveraged by multiple consumer groups or business areas (examples are date or product dimensions). Datamarts are beneficial in cases where the team may want to allow users to create their own data “warehouse” without the need for IT resources.

Existing dataflows can be used as data sources in datamarts with no need to recreate that logic in the new datamart. By consuming the dataflow output in the datamart, you allow users to explore that data, apply additional logic, or run ad-hoc queries using SSMS.

The Caveats of Power BI Datamarts

Though Power BI Datamarts are sure to become highly popular for businesses, that doesn’t mean that there aren’t some caveats that should be considered. This is a new feature still in public preview, so expect some bugs. But, with that being said, also expect a lot of enhancements and expanding capabilities coming in the near future. Additionally, know that Power BI Datamarts are only available to users with a Power BI Premium Capacity or Power BI Premium Per User (PPU) license.

At Fortified, our experienced and knowledgeable DBAs are experts in Power BI functionality. If your business is ready to bridge the gap between your business users and your internal IT Team, our database experts are ready to help create and implement Power BI Datamarts into your business processes today.

Schedule a meeting with one of our Fortified team members today!