Power BI with Business Central

(1) Share

Report

Posted on by SL_GP_BC_123

987

I've connected Power BI desktop to our test Business Central site and noticed that the default API's is very basic. For this exercise we've found general ledger transactions to only have the main GL account and no dimension codes. A scan through the other tables or objects exposed in the API I am not seeing anything else that would have the GL and dimension detail. Is this not built into the API? Without doing any development is there a way to get a better set of tables and objects?

Please advise. Look forward to a response.

Categories:

Dynamics 365 Business Central

I have the same question (0)

All responses (2)

Answers (0)

Suggested answer

texasbbqribs 112 on at

Like (2)

Report

With the existing items, you unfortunately have to pull in the DimensionSetEntries web service and join it to the G_LEntries web service based on the dimension set ID column. You'll need to create additional queries in Power Query based on the DimensionSetEntries object to separate the dimensions.

However, I'm going to get another way for you that will make it easier.

Here's a better way.

Start by creating a new web service. Search for web services.

On the next window you will be able to select many more columns, including the dimensions.

Select Publish.

Voila!

Go into Power BI and select this new object.

You can also create a data set based on the dimension set values or multiple data sets based on them by using the filter option. Then you'll have your dimension code names.

Was this reply helpful? Yes No
SL_GP_BC_123 987 on at

Like (1)

Report

Thank you Texas, that worked, and it will help us now.

A quick follow up question; I see that the new object was published back into the API, but it is under the legacy web services. Is that a depricated feature? We are in the process of building our practice and trying to establish best practices.

Was this reply helpful? Yes No