Connecting PowerBI Desktop to Business Central On Prem Demo

(0) Share

Report

Posted on by Peter Jones - BIDA

104

Hi All,

we are newbies to the task of connecting PowerBI local (desktop) to a demo BC210 instance on the same machine.

We are just experimenting with getting PowerBI to connect to BC. We are not users of BC and we don't have "tech support" for BC.

We have followed what we think are all the correct instructions.

(I won't bore you with the list of things we have done. We think we followed the instructions closely but we are not experts in doing this.)

So we are wondering if there is a log file somewhere than when powerBI says the connection failed the log might give a clue as to why it failed.

We don't want to waste anyone elses time on this because we are such newbies.

We think we should learn how to do this by our trial and error.

We have gone through posts like this one and many other posts a well.
(+) Unable to connect Power BI to Business Central on-prem using basic auth - Dynamics 365 Business Central Forum Community Forum

So if you know where we should look for clues as to what we have set up incorrectly?

We would really appreciate any tips.

Thanks in advance for any assistance you are willing to offer.

Best Regards

Peter

Categories:

Other

All responses (13)

Answers (1)

Verified answer

Peter Jones - BIDA 104 on at

Like (0)

Report

Connecting PowerBI Desktop to Business Central On Prem Demo

Hi All,

this is an old post now but I thought I would report that we got this to work.

We found a guy on the web with a video that showed the details.

This is the video.

(22) How to Connect Power BI directly to Dynamics 365 Business Central on premise - YouTube

Since this was an old video we contacted Mohamad and set up a VM for him on Azure.

He then recorded the process and created a professional quality video and support materials to do this on BC210.

Of course, we paid him for his time and we can confirm that his solution works.

It creates a named user for NAV and connects PowerBI to the NAV User name in the BC Instance.

So if you would like to do the same please feel free to contact Mohamad via his YT channel and discuss if you would like to buy his video and materials for doing this.

I don't know why, but the process he created for us based on the above video is very different to what we find in the documentation.

But we do have this working and it's pretty easy to replicate.

Best Regards

Peter
Peter Jones - BIDA 104 on at

Like (0)

Report

RE: Connecting PowerBI Desktop to Business Central On Prem Demo

Hi Inge,

Well I have had enough for tonight.

While I have been working on this I have realised there are two different things "connecting PowerBI to Business Central" means.

1. Getting PowerBI to run inside BC from inside the "PowerBI reports" window.

2. Getting PowerBI to connect to BC OData sources from the PowerBI app.

Right now I would take getting either working! LOL!

So, I have been able to read OData from PowerBI if I put the credentials into the web browser when it asks.

But if I try and use the PowerBI "Get Data" function when it asks for userid password I get the authentication message below.

I have not been able to get even close to getting it working from inside PowerBI which is also something we want to do.

The instructions are waaaaay to hard to follow and the lack of messaging does not help.

I think that it would be a really great idea for someone to make a video on how to do this.

If we are able to get it to work we might do one ourselves.

It will get a lot of views and establish credibility that's for sure. LOL!

I will try this again when I have time....we have spent more than 20 hours effort trying to get this to work which is a lot really.

Thanks for your help too Inge.

I will see how we go when I have some more time to work on this.
Peter Jones - BIDA 104 on at

Like (0)

Report

RE: Connecting PowerBI Desktop to Business Central On Prem Demo

Hi Inge/All,

I am on this page.

learn.microsoft.com/.../register-app-azure

"Registering Business Central On-Premises in Azure AD for Integrating with Other Services"

Redirect URI Set the first box to Web to specify a web application. Enter the URL for your Business Central on-premises browser client, followed by OAuthLanding.htm, for example: MyServer/.../OAuthLanding.htm or cronus.onmicrosoft.com/.../OAuthLanding.htm. This file is used to manage the exchange of data between Business Central on-premises and other services through Azure AD.

Earlier, when I tried to enter

localhost/.../OAuthLanding.htm

BIDAAZDEV02/.../OAuthLanding.htm

20.224.189.xxx/.../OAuthLanding.htm

I got the message that no wild cards can be entered into the URI.

When I entered

20.224.189.xxx/.../OAuthLanding.htm

It allowed me to save the name.

So my question here is, do I need to install SSL on my BC demo server?

Also, on a related point.

Using the login of the windows server sysadmin that was used to install the BC instance I created a user called BIDACOMMON.

This is linked to an email called bida.common that we use as an internal shared email address when we need to. It's a real O365 subscription.

From the admins account I created a web service access key.

The instructions say sign in as the new user.

But BIDACOMMON was created as external.

Even when I change it to "full user" when I log out and try and log in again it always goes to the sysadmins login like it remembers it.

Is there any video/blog post anyone knows that shows me how to log into this second user?

Thank you in advance for any assistance you are willing to offer.

Best Regards

Peter
Suggested answer

Inge M. Bruvik 32,744 Moderator on at

Like (0)

Report

RE: Connecting PowerBI Desktop to Business Central On Prem Demo

Keep up the good work!
Peter Jones - BIDA 104 on at

Like (1)

Report

RE: Connecting PowerBI Desktop to Business Central On Prem Demo

Hi Inge,

Ok, I have had some success. I entered

Set-NAVServerConfiguration -ServerInstance BC210 -KeyName ODataServicesEnabled -KeyValue "True"

Restart-NAVServerInstance -ServerInstance BC210

And then when I clickedon the odata link it allowed me to use the administrators userid/passward for the machine and actually get some data back.

So I don't know why that service was turned off. I saw it was defaulted to enabled and I saw true "close" to the setting and misread it as enabled = true.

So one step forward.

Its an ok way to spend a snowy sunday afternoon. LOL!

I will keep at it.

Best Regards

Peter
Suggested answer

Inge M. Bruvik 32,744 Moderator on at

Like (0)

Report

RE: Connecting PowerBI Desktop to Business Central On Prem Demo

Good. Lets hope that fix the issue.
Peter Jones - BIDA 104 on at

Like (1)

Report

RE: Connecting PowerBI Desktop to Business Central On Prem Demo

Hi Inge,

Oh, I see OdataServicesEnabled is set to false.

So I will try and set that to yes.

I will see how I go.
Peter Jones - BIDA 104 on at

Like (0)

Report

RE: Connecting PowerBI Desktop to Business Central On Prem Demo

Hi Inge,

yes. we are on step 2 of that page. (see below).

At that step we can not even seen the OData urls on the demo machine we have.

We have put the BC demo instance on an Azure VM in West Europe and we have the fixed IP address available to us.

I believe the OData web services are enabled because it shows the URLs in BC.

I have created a new rule in the windows firewall to allow an input port 7048 using TCP to accept all input.

We are trying to use Nav User Password connections because this is just a test demo machine and we have not installed https.

I have been trying AAD today and it seems we must have https for the URI.

So I have just given up on that.

So yes, I am used to following instructions as I have been in IT 40 years.

But on this one I am stuck it seems.

"Enable OData web services and the ODataV4 endpoint.

OData web service must be enabled on the Business Central Server, and OData port opened in firewall. For more information, see Configuring Business Central Server - OData Web Services.

The local server must be accessible from the Internet."

Best Regards

Peter
Suggested answer

Inge M. Bruvik 32,744 Moderator on at

Like (0)

Report

RE: Connecting PowerBI Desktop to Business Central On Prem Demo

Have you tried to go through this documentation?

learn.microsoft.com/.../admin-powerbi-setup
Peter Jones - BIDA 104 on at

Like (0)

Report
RE: Connecting PowerBI Desktop to Business Central On Prem Demo

Hi DAniele,

I have had a chance to get back to this problem now.

I have been able to get into the BC Admin command line and show the settings for ODATA.

Inside our test BC instance I have got the ODATA Page and you can see the addresses.

I have gone into windows firewall and allowed any application to connect to the ODATA port for input.

But when I click on, say the chart of accounts link, I get can't reach the page.

So it seems we have still not even gotten to the point of being able to see the ODATA web page.

This is the step I am trying to do and it seems we can't get this right yet.

"Enable OData web services and the ODataV4 endpoint.

OData web service must be enabled on the Business Central Server, and OData port opened in firewall. For more information, see Configuring Business Central Server - OData Web Services.

The local server must be accessible from the Internet."

Is there any video or any tips anywhere in more detail that show what to do?

Thank you in advance for any assistance you are willing to be.

Best Regards

Peter