Forum Discussion
Why do you hate Live Data Source users so much?
Yeah. We use Livequery into SQL DB's for some "realtime" dashboards that people refresh every few minutes (think call centre data) but they are incredibly brittle because you're plugging directly into a vendor schema and whacking the secondary on a prod DB for good measure!
We much prefer using LiveQuery against SSAS Tabular models. This enables us to implement some business logic and to generally tidy up the relational schema into something a bit more approachable. Exposing these in PBI seems to make the datasets far more click and drag for our end users, up to a point of course. Most of our Tab models are refreshed periodically with the PBIX files doing live queries off the Tab models.
We do have a couple of Tab models that are wired as direct queries to the underlying SQL DB data sources. So PBIX live query to SSAS Tab that's a direct query to a SQL DB. Debugging that when it decides to behave badly is as much fun as it sounds.
Implementing SSAS Tabular is a faff, the Tabular Editor doodad makes it slighly less painful from a dev point of view, but that's kind of the nature of curated data sets. It does mean we can flex stuff as the DB schemas and business logic change, which they do with depressing regularity.You can of course build SSRS reports off the SSAS tabular models. Writing queries in DAX is a complete hoot cos its so much more approachable than MDX (special sarcasm font required there)
It's also worth remembering that old fashioned SSRS shared datasets can be accessed as oData endpoints thanks to the PBI-SSRS REST API (at what point do you get a critical mass of acronyms that implodes?) and you can of course connect to these from a PBIX. If you use these you can create re-useable data sets and implement another method of abstraction for the data. Though you then have to think about refresh of the SSRS dataset then the downstream refresh of the PBIX before your data gets to the surface the user can see. Debugging these dependencies is a car crash of half baked error messages. Other than a SSAS tab model it seems to be the only method I can find of re-using the same dataset in multiple PBIX files.
The problem is of course that with the "data mashup" approach that PBI encourages you get a lot of speed when it comes to prototyping and data discovery. The blowback is that you're wrapping the data (and potentially the ETL) up inside your visualisation which completely shafts any attempts to separate the duties and or have clean, well documented, easily supported solutions. Anyone who's even remotely interested in data quality or master data management is, quite understandably, going to barf at this approach.
On the plus side I'll be in a job for life as people seem to be able to create this cruft far quicker than i can unravel it and "fix" it.It's an indoor day job without too much heavy lifting so it's not all bad :-)
regards
Steve
We have a number of SQL Enterprise (with Software Assurance) licenses after an upgrade, we are heavy user of Reporting Services so we finally decided to investigate PowerBI Report Server (on premises) because it comes with our licenses.
Our users don't really like SSRS Report Builder very much but they hate having to write SQL queries even more as our databases have awful schemas and the queries are complex and long so we have a seperation of duties. We have people who write the SQL queries and publish the datasets onto SSRS and then people who just use them to build their reports.
Curated datasets allow for caching, reduced redundancy, improved performance and can be written by the smaller number of database experts whilst the many more Report builders can just use them without thinking about the details. As far as I can tell, you just can't do this with PowerBI Server (on premises). You can't publish data sources to a on-premises server, can't seperate the report from the data source in the same way.
As far as I can tell, you have to bundle the live data source with your report (because who cares about seperation of presentation and model, right?). This leads to versioning, caching and performance issues and prevents seperations of duties like described above and I can't reuse datasets in SSRS either.
I cannot fathom why this product can import data from everything from SAP HANA to Github but not from SSRS, the product it's basically built into, or at least make it's equivilent that doesn't need the Live services and a on-premises connector. Is what we're doing not a common workflow? Why isn't this available on the on-premises product?
Anyone have any links to any relevant PowerBI Ideas that we can vote on?
Yeah. We use Livequery into SQL DB's for some "realtime" dashboards that people refresh every few minutes (think call centre data) but they are incredibly brittle because you're plugging directly into a vendor schema and whacking the secondary on a prod DB for good measure!
We much prefer using LiveQuery against SSAS Tabular models. This enables us to implement some business logic and to generally tidy up the relational schema into something a bit more approachable. Exposing these in PBI seems to make the datasets far more click and drag for our end users, up to a point of course. Most of our Tab models are refreshed periodically with the PBIX files doing live queries off the Tab models.
We do have a couple of Tab models that are wired as direct queries to the underlying SQL DB data sources. So PBIX live query to SSAS Tab that's a direct query to a SQL DB. Debugging that when it decides to behave badly is as much fun as it sounds.
Implementing SSAS Tabular is a faff, the Tabular Editor doodad makes it slighly less painful from a dev point of view, but that's kind of the nature of curated data sets. It does mean we can flex stuff as the DB schemas and business logic change, which they do with depressing regularity.You can of course build SSRS reports off the SSAS tabular models. Writing queries in DAX is a complete hoot cos its so much more approachable than MDX (special sarcasm font required there)
It's also worth remembering that old fashioned SSRS shared datasets can be accessed as oData endpoints thanks to the PBI-SSRS REST API (at what point do you get a critical mass of acronyms that implodes?) and you can of course connect to these from a PBIX. If you use these you can create re-useable data sets and implement another method of abstraction for the data. Though you then have to think about refresh of the SSRS dataset then the downstream refresh of the PBIX before your data gets to the surface the user can see. Debugging these dependencies is a car crash of half baked error messages. Other than a SSAS tab model it seems to be the only method I can find of re-using the same dataset in multiple PBIX files.
The problem is of course that with the "data mashup" approach that PBI encourages you get a lot of speed when it comes to prototyping and data discovery. The blowback is that you're wrapping the data (and potentially the ETL) up inside your visualisation which completely shafts any attempts to separate the duties and or have clean, well documented, easily supported solutions. Anyone who's even remotely interested in data quality or master data management is, quite understandably, going to barf at this approach.
On the plus side I'll be in a job for life as people seem to be able to create this cruft far quicker than i can unravel it and "fix" it.It's an indoor day job without too much heavy lifting so it's not all bad :-)
regards
Steve