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
