Power BI Datamart is a new feature introduced by Microsoft for efficient data visualization. Currently in the preview stage, Power BI Datamarts are self-service analytics solutions that enable users to store and explore data loaded in a fully managed database. This eliminates the need for database administration.

Through this blog, we will explore more about Power BI Datamarts, its features, advantages, and differences from Dataflows. We will also look into the steps to create a datamart using BI Connector.

What is a Power BI Datamart?

Power BI Datamart enables users to build, transform, load, and upload data onto data warehouses and create datasets. It also empowers them to create secured and governed databases from various data sources with no code experience.

Apart from data visualization, users can also build relationships, store, query, retrieve and refresh data from different data sources.

In a nutshell, Power BI Datamart is a combination of Dataflow, Data warehouse, and Dataset with a web-based UI to handle the overall functionalities. 

Datamart is a great option for users who don’t want to use Power BI Desktop. It is part of the Power BI Premium subscription ​​(Premium Per Capacity or Premium Per User). 

Principle flow:

• Datamarts are designed to fill the data storage gap in Power BI.
• Its principle flow is that it can fulfill BI requirements under a single cloud feature 
• No code mechanism has been carried across end-to-end development of creating data marts.

Dependencies:

• Creating Datamarts needs a data source (BI Connector) from which data is loaded as a schema into the cloud.
• A data analyst or a BI developer can create datamarts easily.

How does Datamart work?

Components of Datamart: 

When you create a Power BI Datamart, three objects are created behind the scene: A dataflow, an Azure SQL database, and a Dataset. 

Some of these are exposed as individual components, while others are hidden.

Datamart works in the contiguous process of steps as follows:

1. Dataflow ETL process 

• Create a connection to any data source (Excel, Oracle, Snowflake)
• Fetch data from the data source
• Transform data as needed

2. Upload to Data Warehouse

• Store the data in Azure SQL Database

3. Dataset creation

• Datasets can be used to feed data into multiple reports. Whenever Datamart is created, a corresponding dataset gets created at the end of the process.
• Later if needed, users can also connect to the Dataset using Power BI Desktop (like a normal dataset).

Key Features of Power BI Data Mart

• Data Mart is a four-layer implementation in Power BI to build a complete BI Ecosystem.
• The user interface to build the datamart is web-based. 
• Power BI Desktop installation is not mandatory. 
• An Azure SQL database license is not needed for the data warehouse. 
• Security governance is made flexible. Datamart can have maintenance aspects such as endorsements, organizational sensitivity labels, and configurations handled by  Microsoft Azure behind the scene.

Who Can Create DataMart?

Datamarts are solutions created for self-service workloads. These enable data analysts and BI developers to build data models and make the maximum value out of Power BI. 

Data Analyst

Datamarts can be used by data analysts who primarily work with data and understands business but has no coding experience.

They can use Power BI Datamarts to have a fully governed architecture with an ETL layer, data warehouse, and a dataset. 

With all three functionalities combined in a single place, users need not go to any other platforms or learn SQL or programming languages.

BI Developer

Developers can use their coding knowledge to leverage the benefits of Power BI Datamarts. 

They can write T-SQL queries to the Azure SQL Database using Datamarts’ Web UI. Power BI Datamarts also allows them to create a database connection and connect to the database using tools such as SQL Server Management Studio.

Benefits of using Power BI DataMarts

1. Connection with SQL endpoint outside Power BI

Datamarts comprises of a SQL endpoint, allowing users to connect to this endpoint from other tools.

2. DAX measures in Datamarts

Users can create DAX measures in Datamarts, which then appear in the auto-generated dataset.

3. Sharing the dimensions with security

Users can set up a Datamart where data is ingested from various sources, define row-level security on top, and then share the Datamart with others.

4. User-friendly

No-code experience is required for data ingestion, preparation, and transformation.

Datamart creators can utilize their knowledge about Power Query to build dataflows and datasets with Power BI.

Key Differences between Power BI Datamarts and Power BI Dataflows

Power BI Dataflows

Power BI Dataflows is an efficient, fully-managed tool from Microsoft to transform and prepare data. It provides reusable extract, transform, and load (ETL). Dataflow enables users to define tables for reuse. However, they need a dataset to browse, query, or explore tables. 

Dataflows are used to ingest data into Power BI Datamarts. Dataflows find applications in scenarios that require reusing ETL logic or building data preparation for items in Power BI. 

Power BI Datamarts

Datamarts are a  fully-managed database to store and explore data in a relational Azure SQL database. It enables users to perform ad-hoc analysis and create reports. 

Power BI Datamarts provide end-to-end data ingestion, preparation, and exploration with no-code experience.

How to create a data mart using BI Connector

BI Connector empowers users to connect with OBIEE/OAC data and create data marts in the Power BI cloud. 

Here are the prerequisites and steps to create Power BI Datamarts using BI Connector:

Prerequisites:

• A Power BI workspace setup with Power BI Premium (Per Capacity or Per User) account.
• Installing licensed BI Connector Server edition on the machine where Power BI on-premises gateway is configured.

Steps

• Configure the BI Connector DSN using the steps here
• In your Power BI Service via browser, navigate to any workspace setup, click New, and select Datamart(Preview)

• Click Get Data and Select BI Connector

Now you’ll be able to connect to your OBIEE data source and set up the Datamart on Power BI Service.

Conclusion

Power BI Datamart provides a complete ecosystem for BI development. Users can perform end-to-end data ingestion, preparation, and exploration without coding experience. This ensures that developers and non-technical teams can work on Power BI without the help of a database administration. 

Power BI users can create Datamarts using BI Connector. Our unique solution enables users to bring their Oracle data (OBIEE, OAC, OAS, and Oracle Fusion) to Power BI Cloud and create Datamarts for interactive data workloads. Check out more about BI Connector here.