April 2017 Preview Feature Feedback - Querying PowerBI Dataset

[tlovemark]

Over the last few days I've spent some time using the new feature which allows PowerBI reports to directly query published PowerBI datasets as a source.  This ability to "query a query" is seen as fairly high-priority for my organization, as we typically have teams that, while they're all looking at really similar datasets, the specific reports themselves are tailored quite differently for different audiences.  Without maintaining duplicate reports using identical datasets, this ability to "query the query" was largely seen as our only work-around outside of having a 100+ tab report.  A report with so many tabs stretches what I'd consider reasonable usability.

So, to get around this, we are aiming to use a master Dataset in PowerBI that each sub-report can query and make it's own reports from.  This is unfortunately where the limitations of this feature prevent us from really taking advantage of it.

Perhaps the greatest limitation is the inability to update the published PowerBI dataset that each report is using as its data source.  Each time I've made changes to this base dataset and subsequently republished it (adding queries, renaming column names in pre-existing queries, etc), the sub-reports have their connections broken.  Not only are these connections broken, I'm unable to even open the Edit Queries tab as the report owner to attempt to fix it.

This puts us in the position where we can only use this feature if our master dataset is "perfect" at the time of publishing.  I have yet to be on a project (nor have I ever seen one within the industry) where data sources stay the same throughout the entire project lifecycle.  SQL servers move, have names changed, columns are retitled, etc.  Any one of these single events would be enough to break our entire report structure.

Without some way to address the reality that the master PowerBI dataset will need to be updated and re-published throughout the project, we are simply unable to take advantage of this updated feature.  Because of this, our team may roll back to using Excel in place of PowerBI, as this ability to "query a query" is such a great boon for our team.  Until PowerBI can compete with Excel's implementation of this feature, it is hard to justify moving forward to this new tool.

[GilbertQ]

Hi @tlovemark

I have a question around how are you querying the Power BI Dataset from Power BI Desktop?

[tlovemark]

@GilbertQ

Within the April 2017 update for PowerBI, there is a new Preview Feature which enables the ability to specify a PowerBI dataset as a data source.  Here's an article with setup instructions.

Link

Essentially, once a Dataset is published and live up in the PowerBI workspace, you can then create a new PowerBI report which queries this dataset for its connection.  To play with this new feature, make sure the preview feature is enabled.  To do this, File -> Options & Settings -> Options -> Preview Features -> Enable PowerBI Service Live Connection.  This requires a PowerBI restart to take effect, but once done it will allow you to query published PowerBI datasets from the desktop version.

[GilbertQ]

Hi @tlovemark

Thanks for the details, I was under the impression that you were doing something different.

From your scenario where changing the columns or measures causes your reports to fail or break in my experience is the same as it did with SSAS Tabular when you created the SSAS Tabular Model. Essentially having the Power BI dataset in the clould to me, is almost idential to SSAS Tabular, except it is hosted in the cloud.

When previously working with SSAS Tabular (Now Power BI Dataset) I always ensured that I planned out how my model was going to look and function with all the column names and measures.

If I did make a change, before I made a change to the underlying column names or measures, I always took a screenshot or detailed what the reports were currently doing. So that I could then fix the issue if it happened.

One thing was that if you did rename something and it broke your report, you could create it again with the same name, republish and it would work again. I have not tried this yet with the Power BI Dataset.

So whilst you have to do a lot of planning, I would recommend having a DEV, Staging (Test) & Prod Power BI Dataset, to ensure that when something is released to your users they only ever access the Prod Power BI Dataset.

[tlovemark]

Apologies for the delayed response.

When the original dataset is re-published to the PowerBI workspace, the connections within the child reports referencing this dataset break and are unable to be edited/recovered.  This puts the owner of that child report in the situation where the only apparent resolution is to completely remake their child report pointing at the re-published dataset.  This is just way too much maintentance/overhead to keep a set of reports updated.

The Dev/Release/Prod dataset approach is a good suggestion, however due to some of the particulars with our projects, I'm not sure how much we'll be able to leverage that  system.  Our reality is that column names, value formats, and even SQL servers will be changing throughout the time we need to look at this data, so having a data connection that is unable to support edits through its lifetime unfortunately can't really be used by our team.

By the time our data is at the point where it won't be changing formats/source locations, our interest in viewing it has vastly diminished, and our time with that project is likely to be wrapped up.