cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KV_PowerBI
Frequent Visitor

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

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 

 

3 REPLIES 3
mwegener
Super User II
Super User II

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

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


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

 

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

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.