There has been a lot of hype around the new feature in the Power BI Service where you can connect directly to the Dataset in the Power BI Service

There have also been quite a few blog posts on how to connect and leverage the datasets in the Power BI Service, what I am going to focus on is around the tips and potential pitfalls to be aware of when using the dataset in the Power BI Service, which could make this experience as efficient and beneficial as possible.

Quick Overview of the Power BI Dataset

In my opinion the Power BI Dataset is very similar to SQL Server Analysis Services Tabular Models. Which I have been fortunate to having worked with the Tabular Models for quite some time.

This is due to the fact that you first have to first plan and look at all the requirements
before building your Tabular Model or in this instance the Power BI Dataset.

Without having a solid plan in terms of your fact tables, dimension tables, column names, measure names, hierarchies this could result in having to change the Dataset, which in turn could lead to reports failing or ceasing to operate due to changes being made.

Tip – Changing dataset columns or measures

One of the biggest things that I experienced in the past was when there was a change to the dataset columns or measures. This would result in the report failing to load or the particular visual failing to load.

In the steps below I will show what this looks like when it fails and then how to resolve the issue.

What happens when the dataset column or measure changes

What I have done is uploaded a Power BI Desktop file to the Power BI Service and gave it the name of DATASET – Data Gateway Refresh. This is so that I know that this is my Dataset that I want to connect to and work with.

I have then connected a new Power BI Desktop file to my Power BI Dataset to DATASET – Data Gateway Refresh as shown below.

I then created a simple report and uploaded it into the Power BI Service as shown below called Data Gateway Report

As you can see from above I have used a measure called [Total Value] and a column from my Date table called “Calendar Year“. As well as the overall Total Value which also uses the [Total Value] measure.

Now as explained previously if I was required to change the measure from [Total Value] to [Total] and I did this in my Power BI Dataset Model and upload the changes to the Power BI Service.

So in my Power BI Dataset Model I changed the following as shown below:

To the new measure called [Total]

I then uploaded this to the Power BI Service. I did get prompted to replace the existing dataset to which I clicked in Replace.

I then went into my report previously created Data Gateway Report from the DATASET – Data Gateway Refresh, and clicked on Refresh, and what I got was shown below.

When I clicked on See details I got the following as shown below.

The error description I must say is a lot better than it used to be in the SSAS Tabular Models. As you can see from above it is referring to a field that needs to be fixed. Along with that it is giving you the field name that it no longer can find. And with my example this is called [Total Value].

How to resolve or fix a column or measure name change

The first option to resolve this issue is I went back to my Power BI Dataset and renamed the measure or column to what it previously was.

As with our example I then go back to my Power BI Desktop file where I have the dataset and rename it back from [Total] to [Total Value].

I then re-published the dataset to the Power BI Service.

I then once again go back to my report previously created Data Gateway Report from the DATASET – Data Gateway Refresh, and clicked on Refresh, and what I got was shown below.

This is by far the quickest and easiest way to resolve the issue.

The second and more timely and longer solution is if for some reason you must keep the new measure or column name (could be something as simple as a typo) then what you will have to do is to then re-create the reports again. This can be rather time consuming but I have experienced this in the past and have had to rebuild the reports.

Pitfall – Deleting dataset

A potential pitfall is if I decided for some reason that I needed to delete the dataset. Possibly because there is a requirement for it to be renamed something else, or for some other valid reason.

The pitfall is what when I deleted the associated dataset it not only deletes that dataset, but also the existing reports and dashboard tiles that are connected to that dataset.

As with my example, below you can see the existing Dataset and the report that it connects to using the new feature in the Power BI Service called View Related, which shows us all the related content to the DATASET – Data Gateway Refresh

Now when I go and delete the Power BI Dataset called DATASET – Data Gateway Refresh, I do get prompted with the following shown below.

I have highlighted that it does indicate that it will delete all reports and dashboard tiles.

Once I have clicked Delete, not only does it delete the dataset but also my report previously created call “Data Gateway Report”

How to resolve a deleted Dataset

This could result in a potential issue where users of the existing report can no longer access or view the report. A workaround that I would do is to re-upload the dataset, as well as I would re-upload the Power BI Desktop file which contained the report that connected to the dataset. (As there should always be the original Power BI Desktop file for the Dataset and the Report)

Conclusion

Whilst the new Power BI Service dataset is a great new feature. As well as improve over time. It is also important to know what tips and pitfalls could make this experience as efficient and beneficial as possible.

I would be interested to hear if anyone else has any tips or pitfalls when using the Power BI Datasets?