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
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
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.
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.