Share via

SharePoint 2010: PowerPivot - Scheduled Refresh to Netezza

  • Article

How to create a Scheduled Refresh to a Netezza Database in PowerPivot:

Configure SSS target application in SharePoint

If you have not already created Secure Store Service (SSS) applications in SharePoint 2010, then refer to the MSDN article to configure it.

In Central Administrator under Application Management, click Manage service applications.
Open Secure Store services and create following SSS target application:

NetezzaProd (i.e. only as an example)

  • Create a SSS application with Group as Target application type and leaving application Page URL to none.
  • Select Username/Password field types.
  • Setup members that need to access this SSS application ID.
  • Once application created, then set credentials with Netezza credentials.

                     
**
Configure PowerPivot Workbook in Excel** 

                Open the workbook in** Excel > PowerPivot > Home > Other Data Sources > Others (OLEDB/ODBC), **
**                click Next and then Build.
**                Under Provider tab, select IBM Netezza OLE DB Data Provider driver.
    
                             

 Note: If the Netezza driver is not listed under OLE DB providers then install Netezza OLEDB drivers to client server (where Excel is installed) and also make sure that it is installed on SharePoint server as well. You might need to ask your Netezza support person to provide drivers, as they might not be available to download publicly.

After configuration the connection string should look like:
Provider=NZOLEDB;User ID=userName;Data Source=serverName;Initial Catalog=dBName;Persist Security Info=True

                 

Save the workbook and upload it to PowerPivot gallery on SharePoint site.

Schedule PowerPivot Refresh in SharePoint

 

Browse to the PowerPivot Gallery > Locate the workbook you want to Schedule a Data Refresh for > Click “Manage Data Refresh”:

          

 For “Data Refresh” option check “Enable”

**         **

** **For “Schedule Details” select “Also refresh as soon as possible”

**         **

 For Credentials > Select "Use the data refresh account configured by the administrator"

             

Unselect "All Data Sources"

             

Here, select "Netezza Prod"

Under "Data Source Schedule:" > Select "Use Default Schedule"
Under "Data Source Credentials:" > Select "Connect using the credentials saved in the Secure Store Service (SSS) to log on to the data source.  Enter the ID used to look up the credentials in the SSS ID box.”

ID: NetezzaProd

             

Click “OK”

Browse to the PowerPivot Gallery > select the workbook you scheduled for a Data Refresh > Click “Manage Data Refresh” to view the results.

         

 The results are displayed as:

        

  Possible Errors:

 If MS OLE DB provider for ODBC driver is selected instead:

In the results pane, below error is thrown:

The .Net Framework Data Provider for OLEDB (System.Data.OleDb) does not support the Microsoft OLE DB Provider for ODBC Drivers (MSDASQL). Use the .Net Framework Data Provider for ODBC (System.Data.Odbc).***

         

Open the workbook in** Excel > PowerPivot > Design > Existing Connections >** (In this case) “Netezza Prod”:

The Connection String: showed the user was using the (Microsoft OLE DB Provider for ODBC Drivers):

             

The connection string was:
Provider=MSDASQL;Persist Security Info=True;User ID=useName;Initial Catalog=dbName;DSN=NetezzSQL

             

To resolve the issue have user select the Netezza driver (IBM Netezza OLE DB Data Provider) and follow the above steps to properly configure and refresh data in scheduled PowerPivot.