Power BI Datamart updates 2023 part 1

The year 2023 has started with the announcements a few exciting improvements to the Power BI ecosystem and Power BI architecture. There were already announced a few important Power BI Datamart updates, which is already a public preview feature in Power BI Premium.

As we stated in Top Business Intelligence for 2023, a separate semantic layer is a BI trend that is evolving rapidly. Such a layer facilitates maintaining and updating BI applications over time, especially when data modelling and visualization layers are extensive.

Along with the trend, Microsoft introduced Power BI Datamarts that can act as a kind of Sematic Layer in a Power BI implementation, allowing business users to define data models and visualization while also supporting advanced modeling features like calculated columns, and DAX measures, and row-level security.

If you are not familiar with Power Bi Datamarts yet, I highly recommend starting by looking into the article on Dataflow vs. Datamart – when to use them to enhance your Power BI solutions.

Let’s check what Power BI Datamart updates have happened in 2023 and what new features you can implement for your Power BI solutions from now on.

Datamart editor simplicity

One of the huge Power BI Datamart’s benefits include the possibility to create a data model online in Power BI Service instead of downloading a Power BI dataset and using Power BI Desktop. Nevertheless, Microsoft is not resting on their laurels and is improving the online editor with new features to facilitate the development process and increase UX.

One of the recent improvements in the Power BI Datamart online editor is that Microsoft streamlined the SQL and visual querying editor into one unified web UI:

• Data view to preview, sort, and filter the data in tables quickly (data management),
• Query view to write SQL and visual queries to analyze data marts,
• Model view to model the default data model, add measures, and create reports.

As a result, you can easily and intuitively develop Power BI Datamarts without switching between specific views. And without downloading a Power BI file to open it in Power BI Desktop.

It not only enhances the UX of building Power BI Datamarts, but also fits perfectly into the idea of data marts themselves as self-service analytics solutions for storing data, data analysis, and data sharing with optionally no code experience.

Takeover ownership

Just like in dataflows (based on transformation in Power Query online), a creator of Power BI Datamart has special permissions, such as:

• setting up data source credentials,
• configure settings to allow data mart to ingest data.

From now on, you are finally able to transfer these rights to another member of the team.

This feature is of utmost importance when there is an organizational change, an employee leaving, or a retirement. Without it, it would be impossible for other team or organization members to have access to the organization’s data within datamarts, modify datamarts created by others, or connect them to new data sources (relational databases like Azure SQL databases, Azure Data Factory, data warehouses, etc.)

Luckily, Microsoft finally introduced takeover ownership of datamarts in Power BI Service enabling users to take over Datamarts (in Dataflows it was also an issue as

Adding connectivity to any SQL client

As an addition to a SQL endpoint to connect to Microsoft client tools like Server Management Studio and Azure Data studio introduced in 2022, there was added a SQL endpoint for any 3rd party client tools.

Consequently, from now on, it allows for secure AAD authentication and connections to notebooks, BI tools, and other client applications.

As a result, when working with Power BI datamarts, developers can easily access data stored in:

• various databases, like the Azure SQL database (Azure SQL db), or
• other data sources, like data warehouses, Azure data lake,

and work with data in real-time. They are also able to optimize queries for better performance, data preparation steps, and more powerful data transformation capabilities.

Power BI Datamart updates – managing the default dataset

If you are familiar with Power BI, you must know that a dataset is always required before the development of any Power BI report. Consequently, default datasets allow developers to quickly develop reporting capabilities on top of the datamart but also provide additional semantic modeling properties, such as hierarchies and descriptions. Afterwards, they are used to create the Power BI dataset’s tables.

In February 2023, Microsoft added the possibility for data users to add and remove tables from the default dataset within datamarts. Once you navigate to the model view, you can click on a new banner prompting you to manually update the dataset:

You can add tables to the default dataset:

• automatically to the Power BI dataset (no action needed, auto-detect experience opportunistically adds Power BI datasets),
• manually add specific objects to the Power BI dataset (you control which tables are included).

Visualizing results

Another useful capability that was added to Power BI Datamarts this year is the data visualization of query results. Using this, you can use the Power BI datamart editor to easily explore data and perform ad-hoc analysis by building visuals on top of your query results.

The visualizing results feature is already available both in the visual query editor and the SQL query editor. You cannot see it, but the Power BI system created a transient model on the fly, so you are able to start visualizing your query results instantly.

If you want to try this out, follow the steps below:

• Open the Power BI Service and navigate to the Premium workspace.
• Open a Power BI Datamart.
• Run a new SQL or visual query.
• Click on the “Visualize results” button. You will see the name of the query and a list of columns associated with it on the left pane.

• Easily drag-and-drop preferred columns to the Visualizations and Filters pane while building your report.

• Click on “Save as report” to make it accessible within your Power BI workspace.

Keyboard shortcuts implementation

Another addition to Power BI Datamarts that can facilitate and speed up the process of development is implementing keyboard shortcuts.

Consequently, from now on, you can navigate quickly and work more efficiently in the SQL query editor.

These shortcuts are the following:

• Ctrl + Q – to create new SQL query,
• Ctrl + Shift + F4 – to close current tab,
• Ctrl + Enter, Shift +Enter – to run SQL script,
• Alt + Break – to cancel running SQL script,
• Ctrl + F – to search string,
• Ctrl + H – to replace string,
• Ctrl + Z – to undo,
• Ctrl + Y – to redo,
• Ctrl + Left/Right arrow key – to go one word left/right,
• Tab – to indent increase,
• Shift + Tab – to indent decrease,
• Ctrl + K, Ctrl + C – to comment,
• Ctrl + K, Ctrl + U – to uncomment,
• ↑ – to move cursor up,
• ↓ – to move cursor down,
• Ctrl + A – to select All.

Possibility to open Datamarts with Azure Data Studio

If you are a BI developer or a data analyst with SQL experience, working with SQL Server Management Studio or Azure Data Studio, as an extension to Datamarts can put you in a more familiar querying environment.

Before, to connect to a datamart’s SQL endpoint with client tooling, you had to copy the SQL connection string in datamart settings. From now on, you are able to open your Power BI datamart directly with Azure Data Studio. 

If you have the Studio installed, you just need to click on the “Azure Data Studio” option, and it will automatically open with your connection string and authentication type prefilled. Once you click “Connect”, you will be able to start querying in your familiar environment.

Sensitivity label in the Power BI Datamart header

Finally, Microsoft also updated the header in Datamarts by equipping it with the possibility to set a sensitivity label without navigating away from the editor.

As a result, you can now use the Datamart header to:

• rename your data mart,
• get the name of workspace,
• navigate to the workspace,
• apply sensitivity label to the data mart.

All in all, despite being a preview Power BI solution, new Power BI Datamart updates come regularly, improving the data transformation layer, creating queries, and adding meaningful visualizations to your reports and dashboards. As a result, this Business Intelligence tool is going to become even more powerful and equipped with more and more modelling, visualization, and analytics capabilities.