Datamart -- Improve user experience

[v-yiruan-msft]

Preamble

In the process of using Power BI,I am quite familiar with how to use the Power BI to connect the data source, create reports and publish them to Service to configure the refresh and other operation.

Therefore, I suppose that some end users may have difficulty transforming data when they connect the datasets in Power BI Service to create reports if they cannot use Power BI Desktop. Because if we would like to create a report, we have to create a dataflow first and transform it into datasets in the Power BI Desktop, which is not desirable for those who do not want to use the Power BI Desktop.

To our great relief, Microsoft has introduced a new data tool- Datamart which can solve the problem I mentioned above successfully. Next, I am going to talk about how to better use the tool.

Introduction

Power BI Datamart is a combined set of Dataflow, Azure SQL Database, Power BI Dataset, and a Web UI to manage and build all of those in one place.

Creation Process

I simply created a datamart with the data source as an excel file. The general process is as follows. We will have a better understanding of the advantages of the Datamart.

Note: only Power BI Premium Gen 2, Premium per capacity (PPC), or Premium Per User (PPU) workspace support create a datamart.

As shown below I have chosen to create the datamart in the form of an excel file, which is the initial creation interface, that is similar to the dataflow creation interface.

1. Connect to data source

2. Choose data

3. Choose “transform data” into “Power Query” function

4. Choose “Save”, Datamart and the corresponding dataset will be created.

5. If choose “Datamart3”, we can also  model data.

The above is a relatively simple process of creating a datamart, and we can find that it is similar to the "get data- transform data-model data-create report" process done in the Power BI Desktop .

Can Datamart replace Power BI Desktop?

The answer is definitely negative. Although the Datamart can perform many relatively more important functions in Power BI Desktop as described above, it still has limitations in some parts. I will make a summary about the features which Datamart support and ones that it does not support.

• Power Query : Great part of the functions in Power Query (data source settings, AI insights, scripts, diagnostic related functions are not found in Datamart)

•  Home tab: Get data, Transform data, Enter data, New query, New measure, Manage roles, New report, Model relationship, Incremental refresh

Both two categories are the general functions that the Datamart can achieve, if users cannot enable the desktop version of Power BI, it is perfectly fine to choose Power BI Datamart as a temporary alternative, but we also find that there are still some functions that can only be achieved in the Power BI Desktop, so Datamart can be used as an alternative to the Power BI Desktop, but cannot completely replace it.

Datamart unavailable?

Datamart is powerful, but it can become unavailable under certain circumstances, as shown below.

We therefore need to be aware of these situations:

Situation 1: As previously described, the datamart needs to be in the premium workspace to be available, so if the workspace changes from premium to non-premium, the datamart will become unavailable.

Situation 2: When dataflow updates a datamart and the associated dataset, but because a system lock of the datamart or dataset update is pending, the datamart becomes unavailable.

Situation3: Row-level security (RLS) can be used to restrict data access for specified users to a datamart. If the datamart is set up with RLS and you don’t have the access to it , you will not be able to access in the datamart. Unless you are a member of a workspace, the RLS restriction will not be applied.

The above three scenarios are the common situations when the Datamart will become unavailable. Probably, there will be more. I will keep you updated if I find more.

Conclusion

In short, the datamart has largely optimized the user experience of using Power BI Service. However, it is not a complete substitute for other, for example, dataflow , datasets . We have to make a choice among them to better achieve our specific needs.

Author: Mengting Zhu

Reviewer:  Kerry & Ula