What's the best way to connect to an on-premise NAV instance?

[CrossFlowX]

I am looking to connect to our NAV 2017 which is being run on-prem. I want to pull all data to make it available in Microsoft Fabric to various report builders etc. and keep that data up to date.

In general I've seen two distinct possibilities to connect to NAV. The first possibility involves using OData feeds while the second possibility is to connect to the underlying SQL Server directly.

From my understanding both solutions have some limitations. SQL Server can't access flowfields so any relevant flowfield calculations would have to be recreated. Permissions also work differently on SQL Server vs. Odata (Odata using NAV permissions while SQL Server uses SQL Server permissions). On the other hand connecting directly to the SQL Server is supposed to be faster. 

Is there any specific reason to use one above the other or does it come down to personal preference? 

[jdesai]

Hi @CrossFlowX 

I am also looking to connect NAV on prem database to source data into fabric. Did you find a way ?

Thanks & Regards,

JD 

[DallasBaba]

@CrossFlowX  you can reinstall NAV 2017 to reset everything back to default. Then, Install On-premises Data Gateway (personal) and Disable some pages and queries on NAV Web Services that might have some error content (e.g., custom fields). 

Note: SQL Server can’t access flowfields so any relevant flowfield calculations must be recreated.

The choice between OData feeds and connecting to the SQL Server directly comes down to your specific needs and preferences. If you need to access flowfields, then you may want to consider using OData feeds.

Here are some steps to connect to NAV 2017 on-premises using OData feeds:

• Install the On-premises Data Gateway (personal).
• Open Power BI Desktop and select “Get Data”.
• Select “OData Feed” and enter the URL of the OData feed.
• Enter your credentials and select “Connect”.
• Select the tables you want to import and click “Load”.

This is just one way to connect to NAV 2017 on-premises and make it available in Microsoft Fabric.

I believe this YouTube video should be helpful for you.

Please click Accept as a solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item.

If the content was helpful in other ways, please consider giving it a Thumbs Up.

Best Regards,

Dallas.