Design a site like this with
Get started

Power BI – Deployment Pipeline Quick Tips – Setting dataflow Environment sources and Publishing (Direct Query) Datasets containing multiple Datasets

You need Premium or Premium Per user to work with Deployment Pipelines

This happens right at the beginning of the Deployment Pipeline process when you have just added Dev to the Pipeline and you need to deploy Test and Prod

Tip – Changing Data source Rules for Dataflow

You now need to deploy your dev dataflow which is connected to the dev database into Test. You cant change the data source rule until you have a data source to work with.

After deploy, the test dataflow is still against the dev data source (Azure SQL database)

Click Test Deployment Settings

Deployment Rules – Click on your dataflow

Data Source Rules – Change This (Your Dev Details) to this (Select and choose your Test SQL Server and Database)

And Save

The tip here is to then deploy your dataflow Dev to Test again. Only then will it use the new settings.

To check go back to the workspace and go to settings for the dataflow

Deploying Datasets that contain multiple data sets

This is specific to setting up Power BI With the Following Option

With this option set you can create smaller data sets, probably based on a star schema. Then if required you can connect to another data set. And then connect to more data sets and data. Without this option you can only connect to one data set.

This has changed from a Direct Query Connection (The standard way. 1 Data Set Only) to Live Query Connection (Analysis Services and Multiple data sets)

Tip- Move your hybrid data set after the original data sets

So here, what we can do is move the dataflows, and datasets A B and C at the same time.

Once completed move Star A B and C so it goes after the schemas its based on

Then do the report.

If you try and do them all together you will get errors.

So these are just a couple of tips to look out for when setting up your Pipelines for the first time. And if you use the setting that allows you to connect to multiple data sets.


Power BI – App Viewers can’t see the data in the report

We recently had an issue where a shared dataset (pbix) had been set up over a SQL Database.

This was then published to Power BI

A new pbix was created.

Power Platform – Power BI datasets was chosen and the shared dataset was selected. Then reports were created and published to Service.

An App was set up and a user was added to view the report.

However when they came to view the report, they would see the report but not the data. All they had was messages about not having access to the data.

At first we struggled to understand what the problem was and then it started to add up.

Previously we had worked on a project with dataflows and multiple datasets being used for one report. So we have the following ticked

This worked great for this specific project. We were in Premium. There were dataflows.

However, this project is just a test report, not set up in Premium and without dataflows.

The above setting is a blanket setting that sets every pbix to you create from Live Query to Direct Query

Live Query is where it live connects to just one data set only and then when you publish your report over the data set it uses that initial shared dataset and doesn’t create a new data set because the DAX, model etc. is all set up in that specific data set.

Direct Query is a slight change. You Direct Query the data source (the data set) and crucially you can also direct Query other data sets, even other data sources like data bases and flat files all together. But that Shared Data set is also direct querying its data source.

Direct query is a good one for real time analysis from a transactional database. But many DAX expressions aren’t available over Direct Query straight over a database. For example, time based intelligence DAX. So the reports are much simpler in Power BI. And more complex to set up at the database end for the users.

In this instance, the reason we have issues is because there is no dataflow at the start of the Power BI process. 

If you are using Direct Query over a dataflow, the data is imported into Power BI into the dataflow. The dataset Direct Queries the Dataflow.  Your users are then added to the workspace App and they can see the data because they have access to the dataflow.

Without the dataflow, your data set is calling data directly as Direct Query.  Which is essentially where Power BI always calls from the data base and not from the Power BI Columnar data store.

So the users were opening up the App, and trying to access data straight from the database because there is no dataflow holding the data. Because the user doesn’t have access to the database, there is no data to be seen.

So the issue here I think is that Power BI should be allowing us to switch this option on and off, depending up the choices we make on set up. Not just have it as a blanket option over ever single report like it does now. 

Without dataflows you want to Live connect to the shared dataset. Not Direct Query right down to the datasource.

With a dataflow its fine to Direct Query because the users have access to the dataflow data in the workspace

Power BI March 2021 Updates – Direct Query for Power BI datasets and Analysis Services, Testing the new fixes.

Direct Query for Power BI datasets and Analysis Services feature has currently been in preview for a couple of months

Switching this feature to on, should hopefully allow us to create reports over multiple data sets, which will allow use to have smaller datasets, maybe just over a single model. These can then be connected together in the report

And we couldn’t wait to try it out.

We had two data sets. And for one report we wanted to connect to both datasets so we could create reports over the two, without having to create an entirely new larger data set.

However we ran into issues which ended up rendering this unfit for purpose

  1. None of the formatting was remembered from the original datasets
  2. All the folders had disappeared that had been set up in model view
  3. All the Sort by items had gone.
  4. Both models connected to a data dimension, but actually removing one of the date dimensions (from the second model) and connecting the second model to the first models data dimension didn’t seem to work very well.

We have lots of use cases where this would be perfect. so in the March update, it is mentioned that the team have been working on this. Lets have a look at see if this is now a viable option for us.

The source data sets

Data set 1

We have some folders for the measures

In the date dimension, month has the sort order of Month No

Seasons has the sort order of Seasons order

And we have a measure set as a Percentage (An example of formatting)

Data set 2

We have again, Folders, Sort orders and Formatting.

Both datasets have a date dimension

lets look and see what happens if we use Direct Query for Power BI datasets and Analysis Services

Remember to turn it on as a preview feature in options and settings

Get data

Choose a dataset (That has been published to an app workspace

Go to model view and remember to upgrade the model view

Issue with layout views

At this point, one of the key issues is that in the shared dataset pbix file, the developer has created multiple layouts in model view to make it easier to work with.

In the report pbix file all the layouts are lost and we are left with the All tables layout.

Not good because the report developer has to redo this work again in this report file

So here is the first model

Import data set 2

we can now get data from another Power BI dataset even though we are connected up to a dataset. Previously you could only connect to one data set.

What does this mean?

With one data set its a live connection, in the same way as you would live connect to analysis services. with Live connection there is no local model.

Direct Query. Direct Query needs a local model adding into the report so in this case, a dataset also gets published to Power BI Service, along with the report.

From this in Power BI Service

To this

Select Add a Local Model

And select the next data set

In this case we are happy with the security because both data sources are within the same workspace and we trust the owners and admins in this workspace.

Click OK

As the report developer I am now looking at quite a complex model in all tables.

The colours denote the two data sets.

Now we need to establish which tables are in twice.

Immediately we can see date and date 2 and these need conforming

You cant delete a table. All you can do is hide it in report view.

In this case date 2 is being hidden (Hide in report View)

And a join is being created between the metrics table in data set 2 and the date table in dataset 1

Remember that you will need to go over your model. Find every table that is being used across the models. Hide duplicates and create relationships

Check for issues

The date table in data set 1 has the above Sorts applied and they are still working in the report

The measures folders are still available in both dataset 1 and 2

However Formatting has been lost on the Percentage measure.

So this leaves us with one major issue across datasets, and we can’t reset all the formatting within the reporting pbix file

As it stands then this is currently not fit for purpose and just needs another extra tweak. Its a real shame because we were really hoping to use this very soon. Hopefully Power BI Sorts it out in the next release.