Best practice while connecting Power BI to Business Central On-premise?

[KV_PowerBI]

Hello,

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

OR 

- Dynamic 365 Business Central (On-Premises) " power bi connector ?

What are the advantages and disadvantages of the two options?

Thanks 

[mwegener]

Hi @KV_PowerBI ,

I still prefer the SQL Connector, as I am not convinced by the performance of Odata.

We are thinking about a similar scenario right now.
In the first step we want to join the tables per company and load them with a Power BI Dataflow per company.

Afterwards, the tables from the individual dataflows are to be combined into a Power BI dataset.

I think with some parameterization and a PowerShell script we can replicate the dataflows across multiple clients.

Through naming conventions and M functions, we will then be able to comfortably connect the dataflows in the dataset.

Regards

[KV_PowerBI]

Hello @mwegener 

Thank you your response and sorry for delay in replying !  I have been doing some digging and I also feel SQL connetor may be a better option.

I have not worked with Power BI dataflows but I am more familiar with SQL. 

I am thinking more in terms of

1) using SQL views for the joining the extensions per company. 

2) The SQL 'join' Views will then be individually created for each company.  (main disadvantage. Probably can be automated with SQL Stored procedure ?) 

3) The use parameters in Power Query to connect to the SQL views (per company) as required. 

Are there any specific advantages of using Power BI dataflows ?

Another problem I have come across with the SQL Connector route (to Business Central) is as below  (I should probable create another thread for this) : 

Business Central uses  enum datatype to store option values. These are stored as integers in SQL DB. 

- How to get the Enum values in SQL ? My idea is to get the Enum values and create a SQL table/view to store/maintain these Enum values) 

- Where can I even see all the enum values in Business Central ? 

Have you come across this issue yet / or found a way out of this ?

Thanks and Regards

[mwegener]

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.

Regards

Marcus