OData connection to Excel using Power Pivot

Alex_Chow · 2013-12-14

Following the instruction laid out here:
http://msdn.microsoft.com/en-us/library ... 6(v=nav.71).aspx

In particular here:

4.If your Microsoft Dynamics NAV implementation requires that you use a web service access key, you must specify the NavUserPassword credentials as described in the following steps:
a. In the Advanced dialog box, in the Security section, set the Integrated Security field to Basic.
b. In the Password field, type the web service access key.
c. In the UserID field, type the user name for the Microsoft Dynamics NAV user account. For this walkthrough, use NavTest.
d. In the Source section, in the Service Document URL field, type the URL for the OData web service that you verified in the previous procedure, for example, http://localhost:7048/DynamicsNAV71/OData/.

However, when I tried doing this in Power Pivot, it keeps saying error 401 Unauthorized.

Here's my connection string.

Service Document Url=http://dynamicsnav:7048/dynamicsnav70/OData/;Include Atom Elements=Auto;Include Data Services Content=True;Include Expanded Entities=False;Namespaces to Include=*;Integrated Security=Basic;User ID=NAVUSER;Password=********;Time Out=600;Persist Security Info=False;Schema Sample Size=25;Retry Count=5;Retry Sleep=100

Anyone point me to the right direction?

Alex_Chow · 2013-12-16

NAVUSER is setup as SUPER permission in NAV. Created a webservice key. Still no luck...

Alex_Chow · 2014-01-09

Has anyone gotten this to work in the production environment?

This can be a great alternative to the RDLC reporting.

jmglezgz · 2014-02-11

Hi Alex,

I´m working on a BI system for Dynamics NAV 2013 and I would like to use this kind of connection better than SQL ODBC.

The connection (excel + powerpivot) works fine on localhost (a virtual machine on Windows Azure) with this connection string:

http://localhost:7048/dynamicsnav71/Odata

If I try to connect over Internet with Explorer or Chrome works fine too. with this string connection:

http://***.cloudapp.net:7048/dynamicsnav71/Odata

If I try to connect over Internet with Visual Studio 2012 works fine too.

with this string connection:

But it doesn't work over internet with this excel and powerpivot

http://***.cloudapp.net:7048/dynamicsnav71/Odata

Does Somebody find a solution for that?

Alex_Chow · 2014-02-20

Finally got this to work with this string:

Base Url=http://dynamicsnav:7048/dynamicsnav70/OData/;Include Atom Elements=Auto;Include Data Services Content=True;Include Expanded Entities=False;Namespaces to Include=*;Integrated Security=Basic;User ID=navadmin;Password=**********;Time Out=600;Persist Security Info=False;Schema Sample Size=25;Retry Count=5;Retry Sleep=100

The only change I made was to use the login account. I had to use the login and password for the account that was used for Dynamics NAV service.

johanna · 2014-07-14

Dear Alex,

I use NAV 2013 R2 build 36897 and use credential type NavUserPassword to create a OData web service. When I run link "https://win2k12.nav.com:4/TestCronusDB/OData/Company('CRONUS International Ltd.')/LocationList" in browser, everything is fine. I have used my domain account and its web service access key for User ID and Password in Excel. But, when I try to test connection, I got error like below.

============================
Error Message:
============================

Cannot connect to the specified feed. Verify the connection and try again. Reason: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.

============================
Call Stack:
============================

at Microsoft.AnalysisServices.Common.DataSourceBasic.UpdateDataFeedParameters(ConnectionStringBuilder connBuilder, IDataSource dataSource, Boolean checkFeedValid)
at Microsoft.AnalysisServices.Common.AdvancedPage.ClickTestConnection(Object progressControl)

============================

The connection string is like below.

Data Source=https://win2k12.nav.com:4/TestCronusDB/OData/Company('CRONUS International Ltd.')/LocationList;Include Atom Elements=Auto;Include Expanded Entities=False;Integrated Security=SSPI;User ID=NAV\ADMINNAV;Password=**********;Persist Security Info=False;Time Out=600;Schema Sample Size=25;Retry Count=5;Retry Sleep=100;Keep Alive=False;Max Received Message Size=4398046511104

Please help. Thank you.

acyvas · 2015-03-24

Hi,

Try using IP address instead of server name.

OData connection to Excel using Power Pivot

Comments

Categories