After 14 years using Microsoft On Premise BI Tools (SQL Server, Reporting Services, Integration Services and Analysis Services) Its time to embrace Business Intelligence in the cloud.
Category: Direct Query for Power BI Datasets and Analysis Services
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.
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
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
None of the formatting was remembered from the original datasets
All the folders had disappeared that had been set up in model view
All the Sort by items had gone.
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.
Debbies Microsoft Power BI, SQL and Azure Blog 