Search This Blog

Saturday, May 18, 2013

Connecting to PDW from PowerPivot

PDW v1/v2

When connecting to a PDW from PowerPivot, there might be some confusion about what to enter as the server name.

Confusion might even start at the very beginning when choosing the proper external data connection. After opening PowerPivot, you might assume to find the PDW connection behind the “From Database” ribbon icon. But you have to choose “From Other Sources” instead:

image

In the following dialog, choose “Microsoft SQL Server Parallel Data Warehouse” as the database type:

image

Next, a dialog opens to enter the connection details.

While you can choose any name you like for the connection name, the server name may be confusing as you usually enter a host name or IP address here. But for the PDW you have to enter the path to an IDS file containing the connection information:

image

How does such in IDS file look like? Let’s take a look at an example:

[Provider]
ProviderName=Microsoft SQL Server MPP OLE DB Provider
clsid={7D5C1E01-747C-4f39-8BEF-A88133706917}
[DSNInfo]
Description=MyPDWConnection
[Properties]
Host=192.168.27.23
Port=17001
Database=AdventureWorksDW
UseLDAP=0
DistinguishedName=
Encrypted=0
LoadBalancing=0
ConnectionRetryCount=0
ConnectionRetryDelay=3
AlternateServers=
DriverCompatibility=0
LogonID=mylogin
Password=mypassword
StatementFailover=0

Be sure to enter the correct IP address and port (default is 17001 for the TDS and 17000 for the Sequelink, but TDS is much faster), as well as the correct user/password.

4 comments:

  1. Where should I look for the .ids file? I can't locate it anywhere on my computer.

    ReplyDelete
  2. Hi Jess, The .ids file is no longer necessary. You can now simply connect to the PDW (v1 AU 3.5 or PDW 2012) using a SQL Server connection. For the server name just specify the cluster ip address of the PDW with port 17001 for example, 10.0.17.240,17001.

    ReplyDelete
  3. Hi Hilmar, Do I need for download and install separate 'Microsoft.SQLServerMPPOLEDBProvider' Please suggest ?

    ReplyDelete
  4. Hi Nagendra, The post was about an earlier version of PDW. You can connect to all modern appliance via SQL Server Native Client (make sure to use SNAC 11.0 for PDW 2012). No extra OLEDB provider required.

    ReplyDelete