Understanding Data Mart & Data Flow Gen 1 in Power BI Experience

8 min readApr 21, 2024

Explore the concepts of Data Mart and Data Flow Gen 1 within Power BI experience. Learn how these components are applied with a PBI license to streamline data management and analysis. Enhance your understanding of creating efficient data pipelines and optimizing your business intelligence workflows.

Introduction Data Mart

Data Mart are self-service analytics solutions, enabling users to store and explore data that is loaded in a fully managed database. Data Marts provide a simple and optionally no-code experience to ingest data from different data sources, extract transform and load (ETL) the data using Power Query, then load it into an Azure SQL database that’s fully managed and requires no tuning or optimization.

Data Mart benefits and features:

Self-service users can easily perform relational database analytics, without the need for a database administrator
Datamarts provide end-to-end data ingestion, preparation and exploration with SQL, including no-code experiences
Enable building semantic models and reports within one holistic experience
100% web-based, no other software required
A no-code experience resulting in a fully managed datamart
Automated performance tuning
Built-in visual and SQL Query editor for ad-hoc analysis
Support for SQL and other popular client tools
Native integration with Power BI, Microsoft Office and other Microsoft analytics offerings

In this architecture, the data source is csv file in local. Before beginning this architecture in PBI that the workspace must be created as premium workspace.

In the workspace Data Mart is created.

When Data Mart is created. This is the visualization how the screen is shown. There are lots of option in the Data Mart.

“Refresh Icon” (Refresh data)
“Get Data” (Take datas from different sources)
“Transform Data” (This is the Power Query editor that you can develop ETL process)
“New SQL Query” (Ad-hoc Analysis with SQL)
“New Visual Query” (Ad-hoc Analysis with Diagram Power Query), “New Report” (Creating a report that you can visualize)
“New Report” (Creating a report that you can visualize)
“New Measure” (Creating a measure that you can use in your reports)
“Manage Roles & View As” (RLS-Row Level Security)

“Data” (collection of discrete or continuous values that convey information)
“Query” (a question or request for data)
“Model” (Define the relationship between the data sources)

Clicking Get Data → New Source → Text/CSV.

Upload the Case Death CSV file.

When confirms the upload file this is the transform side which means Power Query side. This is the Power Query of case death csv file.

Upload Dim Date CSV file.

Power Query of dim date csv file. Needing to match dim date to Case Death date to find week, month information etc.

Example of some transform process.

There is 2 option to use Dim Date datas that are Merge Queries or ModelLing. In this one shown Merge Query.

Clicking Merge Queries as New that created a new table with merged.

Merged Case_w_date table.

dim_date must be expanded to observe the data with merged table.

With Query Setting that the lineage visualization can be seen that what have been done so far.

After clicking save button. In Data can be observed with details.

Navigating Ad-hoc analysis with SQL.

With Query tab, example for Ad-Hoc Analyzing Spain of count number.

With Model tab that can be defined the relationship between the data sources.

As explained before the second option without merge is that can be used make relationship between tables.

Example of make relationship with tables. Also there ise option “New Visual query” that can be used Ad-hoc Analysis with Diagram Power Query.

Option to observe Cases_w_date merged table with filter.

Filtering “country” Zimbabwe.

There is 2 option to observe detail data that are Download Excel file, Visualize Result.

In Power BI Service can be created report via “New Report”. Also, via Power BI Desktop that can be created report to get data from Data Mart or SQL Connection String ( Power BI Service → Data Mart → Copy SQL Connection String)

Example of the report can be created.

To workspace that can be seen Data Mart, Semantic Model and Report and that is able to refresh with settings.

Also with SQL Connection String that can be analyzed via Azure Data Studio, Azure SQL Server, Microsoft SQL Server.

Example of Azure Data Studio. Datas are stored in view in SQL.

Data Mart Limitation:

When Data Mart refresh, all data in data Mart will be refreshed.
SQL DML / DDL Command is not available

Data Mart Data Store Properties

Data Volume: Up to 100GB
Type of data: Structured
Primary developer skill set: No code, SQL
Data organized by: Database, tables, queries
Read operations: T-SQL, Power BI
Write operations: Dataflows, T-SQL
Multi-table transactions: No
Primary development interface: Power BI
Security: Built-in RLS editor
Access data via shortcuts: No
Can be a source for shortcuts: No
Query across items: No

Introduction Data Flow Gen1

A dataflow is a collection of tables that are created and managed in workspaces in the Power BI service. A table is a set of columns that are used to store data, much like a table within a database. Also, experience to ingest data from different data sources, extract transform and load (ETL) the data using Power Query.

Data Flow Gen1 benefits and features:

Data Preparation and Transformation Efficiencies
Centralized Data Management and Reuse
Enhanced Data Refresh and Update Options
ML Scenario
Incremental refresh is available

Scenerio 1

With Data Flow that can be created lots of Scenerio. 2 Scenerios that mostly used widely will be given as Example. Both scenerio architecture, the data source is csv file in local.

Fetching the data that is csv file should be used “Define New Tables”.

Data Mart Power Query and Data Flow Power Query are same nothing different. Therefore, Clicking Get Data →New Source →Text/CSV.

Upload the Country_Lookup to find 2 or 3 country digit code.

The data is demonstrated in Power Query.

Example of some transform process.

Transformation has completed.

When Clicking Save button. Tables will be demonstrated. The importing thing is after closing Data Flow that must be refreshed. Otherwise, the tables wont be seeing in Power BI Desktop while getting data from Power BI.

Clicking Power BI Data Flows(Legacy). Other Data Flows are not PBI Data Flows. Others are Power Apps Data Flow and Fabric Data Flow. In this scenerio that has been used PBI Data Flow.

Lastly, the reports can be created.

Scenerio 2

Last time country_lookup ETL process has been completed in Data Flow. Hence, completed ETL process table can be used in Data Mart which means that can be stored in Azure SQL and doesn’t effect performance. Because ETL process hasn’t done in Data Mart Power Query.

Inside Data Mart get data from Data Flow that created.

Connect to Power bı Data Flow(Legacy).

Contry_Lookup datas.

In the next steps, that can be joins with Country_Lookup and Case_w_date and reporting.

Data Flow Limitation:

Semantic Model is not available

Comparison of Dataflow Gen1 Pro Workspace & Dataflow Gen1 Premium WorkSpace

Comparison of Data Mart, Data Flow

Data Mart stores the results of all of the source tables and it extracts, stores new tables that are created as a result of data transformations.
Data Flow fetch data from different data sources and it can be created a new output table.

Thank you for reading!

If you enjoy this article, please click the Clap icon. If you would like to see more articles from me.