Best practice while connecting Power BI to Business Central On-premise?
I have a requirement to get data from Business Central into Power BI .
One option is to use SQL server database power bi connector. Business Central tables have multiple extensions (other than the base extension) and data is stored across different multiple companies.
My first thought is to use SQL JOIN to join the table extensions and SQL UNION to bring together all the company data into one Power BI table. This becomes cumbersome when there are many companies and many table extensions.
Is there a easier way to do this ? Could anyone advise?
I considered using "Dynamic 365 Business Central (On-Premises) " power bi connector.; but i am told that the Odata URL needed for this is anyway BC Company specific.
What is the best practice related to connecting Power BI to Business Central (On-Premise) -
- Using 'SQL server database' connector
- Dynamic 365 Business Central (On-Premises) " power bi connector ?
What are the advantages and disadvantages of the two options?
Hi @KV_PowerBI , we use the dataflows to separate the data load & transformation per company from the load into the later data model. We assume that the individual steps can then be better maintained.
I think a large part of the tasks in your case take over the SQL views.
As far as I know, the option values (enum values) are stored in the application code of the extension. This is stored in a blob column and would have to be extracted from the source code first. We are currently translating the relevant option values manually in Power Query.
If you find a better solution, please let me know.
Did I answer your question? Please mark my post as solution, this will also help others. Please give Kudos for support.