This is my first time posting so apologies if I get it wrong!
I wanted to know if Enterprise DNA were going to cover how to create financial reports by connecting directly to source transactional systems like SAP etc. I want to know what level of efficiency clients can expect from these types of solutions and if they will have to create data dumps and then PBI will pull from these data dumps or can we be taught how to go directly to source?
Look forward to your replies and sharing your thoughts on this.
Just a quick follow-on. If any of the forum members have also had experience of delivering a solution of a direct connection to the source systems - it would be great to understand your experiences and how you dealt with it.
At the present time there is no plans for something specifically on SAP systems.
With our training I try to keep it slightly broader and general to fit the majority of members needs.
Also there is more a analytical focus on the front end of Power BI rather than the connections side of things. This may change though in the future as more and more content is produced.
I have seen SAP connected to though, but the tables directly from SAP are never in a good shape initially and a bit of work needs to be done in the query editor. But that’s ok, it can be done.
I think overall if you’re using Power BI you will see huge efficiency gain no matter where the data comes from. It’s the best tool out there by far at the moment when you look at the entire package.
I do Power BI reporting directly against source systems, including our accounting/ERP system. I use Power BI Report Server on-prem, so I am not dealing with data gateways, but other aspects will apply regardless.
The first thing to note is that I do not read from the source at run-time. I always use scheduled refreshes. This is in part to PBIRS having limited support for direct queries and also because Sam noted in various trainings that certain measure requirements do not work if the data model is not pre-loaded. A second benefit of scheduled refreshes is that they can occur off-hours when systems are not under heavy load.
The second item to note is that I typically, but not always, have the Power BI report load data from a SQL view defined in or attached to the data source. FYI, I use SQL Server, and have the ability to create whatever views I need. What this does is hide the details of the source system from Power BI by effectively creating an API. If the source system changes, and the ERP system is always getting updates to its data structure, I only have to ensure that the SQL view columns are correctly populated. Power BI never has to know about the changes. You can also use the views to do basic transformations of the data values or data types.
In the rare cases where a SQL view is not warranted, I simply write a SQL query myself and use that as the data source query in Power BI. This is particularly helpful when there are lots of small dimension tables.
Finally, in some cases, such as the ERP system data which goes back to 1992, I use pre-defined dimensions such as the last 10 fiscal years to filter the import of the fact table data. This would be extremely useful in the Power BI service with data gateway, since you are limiting the amount of data that has to be retrieved before any processing can occur.
Thanks Sam and John for your comprehensive replies.
@John - it sounds like you generally connect to some form of data warehouse, which is the layer between the ERP source system and PBI. If that’s the case it could be a good solution especially when many clients will not easily grant direct access to transactional systems.
