I have searched the forum, and the internet, without finding an answer to my 4 questions below.
Working with a program called Accelo, an ERP, it creates its own PBI app and dataset in the PBI service. The software has basic reporting built into PBI and allows us to connect with PBI Desktop for custom reporting.
When connecting from PBI Desktop the only option is a live connection to the dataset. Once established I can create custom reports however I have found a number of limitations around this.
As such is there a better way to do this? What do you recommend? My main points of concern are the following;
I can not add new tables (no measure grouping, no date table, no supporting tables). The options are grayed out.
What happens if the application changes, renames, or restructures the dataset? I would lose all my design work and measures?
What happens if the dataset is deleted? By the program, or by accident. Is there a way to back this up?
Without being able to add a date table, how can we do advanced time intelligence?
I haven’t worked directly with a live connection (Direct Query Mode), but recently I helped another member who was limited to DQ mode develop what otherwise would be a straightforward solution in Import mode. I was absolutely floored by how limiting the DQ restrictions were. The thread below will give you a good sense of how these limitations played out in the development of a DQ-compatible solution. Also embedded in the thread is the link to a detailed SQLBI whitepaper that discusses all of the limitations of DQ in-depth.
I think all of your concerns stated are valid, and there are many other problematic aspects of DQ in my opinion that you didn’t mention. All of which makes me wonder if it might be possible in your case to do a full data export on a regular basis, and then import that data into Power BI , so that you have the full suite of PBI capabilities available to you?
Thank you Brian, I am glad we are on the same page here.
I would like to automate as much of it as possible. I believe one option would be to use a data warehouse in between the software and PBI. Anything to watch out for here? I have seen a company called Acerys which seems to do this already, but requires a custom API to be written for this application.
I believe live connection and direct query are actually different, although many of the same restrictions apply. I, as well, haven’t worked with live connection, but have worked with DQ … Here’s the first reference I saw (there are many others): https://guyinacube.com/2019/04/18/power-bi-get-data-import-vs-directquery-vs-live/
I create master dataset for my company and the rest of my company use the master dataset to do adhoc reporting here’s my comment:
If I rename the dataset, it will still work. Looks like the power bi desktop use the dataset id to keep the connection
If I delete the dataset or did some modification to existing dataset basically the existing reports may fail.
As of now, adding table to existing dataset is not allowed. if your concern is just time intelligence, depends on the complexity, it may still work.
I also create dataflow for my company since I am the only one with Oracle EBS tables, as the users get more matured, they can use the dataflow instead of dataset.
My suggestion is, if you’re familiar with your ERP system, you may want to work with IT to setup some dataflow and build your own datamodel.
Hi @rhyschappel, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
