Power BI connection to NAVISION on premise - Gateway - How to Step by Step.

[shebr]

Hi All,

I have a Navision instance on a server on premise. We have o365 and Power BI, including Power BI Desktop on our personal machines.

Using the Gateway, how can I set up so that I can connect from my Desktop to the NAV server (SQL or Odata source) through Power BI?

Could someone please advise on the steps involved?

Currently I have installed the PBI Gateway connector on the NAV server, and connected to the SQL database. Unable to connect to the Odata Link. From here, I get a bit lost.

Any help much apprecated.

Thanks

SR

[Seth_C_Bauer · Accepted solution]

@shebr If this is a SQL instance, you shouldn't have any issue connecting to the SQL database from the Desktop. As long as you have permissions granted on the database to do so.

The gateway is only necessary when you deploy your reports to the Power BI Service (app.powerbi.com)

If you have successfully installed the on-premises data gateway, you need to create a data source for the database, and make sure your user account is in the list of "Users" on the data source. This will allow you to publish reports from the PBI Desktop to the Service.

You shouldn't need an Odata link because you have the SQL instance on premises...

[ahmad]

hi @shebr @Seth_C_Bauer

please need your advice, i have the same task, i have installed on premises Gateway on Navision server (domain server) and i'm tryin to connect from app.powerbi.com to NAV using the Odata link(i have tried server name and publick IP) but i'm still getting nothing the power BI stay loading without getting any data.

looking to your advice pls.

Thank you

[Seth_C_Bauer]

@ahmad It sounds like you are trying to connect directly from the Service to the data source. I would suggest you start with the Power BI Desktop. This is the tool you want to create your model and additional calculations. Once you have done that, and built the reports, publish that to the Service (app.powerbi.com)

[Seth_C_Bauer · Accepted solution]

@shebr If this is a SQL instance, you shouldn't have any issue connecting to the SQL database from the Desktop. As long as you have permissions granted on the database to do so.

The gateway is only necessary when you deploy your reports to the Power BI Service (app.powerbi.com)

If you have successfully installed the on-premises data gateway, you need to create a data source for the database, and make sure your user account is in the list of "Users" on the data source. This will allow you to publish reports from the PBI Desktop to the Service.

You shouldn't need an Odata link because you have the SQL instance on premises...

[shebr]

@Seth_C_Bauer thanks for your reply.

I do intend to deploy and share via the Power BI service and so will go ahead with your suggestion, I will come back monday with an update.

Much appreciated.

Regards,

SR

[shebr · Accepted solution]

@Seth_C_Bauer Thanks for your advise and I can confirm that this did work and I was able to share data from the SQL tables for NAV through Power BI. Much appreciated.

With regards to the oData feed, currently there are empty tables that need to be filled before the content pack can become available. That will be my next step.

Thanks.

SR

[jasonjs]

@shebr I am looking to do excaclty what you described below. I am new to NAV. How to you connect the on premise NAV to Power BI? I clicked Get Data > SQL Server Database > but I don't know hwo to find my server and the settings. Any help would be great.

Thanks,

J

[shebr]

Hi,

Apologies for the delay.

So I found out that you must be using all the NAV features for the data connection to be available. I dont know much about the particular features for NAV but I found that we were not utilising those features hence could not get  a direct connection.

The oData connection looks at a particular tabe within NAV, and if this is not populated the feed will not be available.

Hope that helps.

Thanks