Home > Uncategorized > Using ODBC Connections in PerformancePoint 2007

Using ODBC Connections in PerformancePoint 2007

PerformancePoint is designed to work with multi-dimensional data sources (OLAP Cubes), however, it is possible to create ODBC Data Sources if necessary.

Such an example, may be to connect directly to a SQL Server data warehouse to create KPIs, especially if its to quickly knock up a demo. 

However, there is no wizard to create these connections and a connection string must be provided. There are a few things which must be done correctly before you will get a nice ‘Connection Successful!’ from the Test Connection button:

1. Ensure the account being used to connect to the data source has at least read access.  This account will either be:

PPSMonitoringWebService Application Pool account (if server connection is in service account mode)

Current logged on user (if server connection is in per user mode)

2. Ensure your connection string is in the ODBC Driver format AND assumes a trusted connection (you cannot supply hard coded user id/password when using SQL Server). This is critical as PerformancePoint is very strict about the format of this connection string.

Best thing to do is go to www.connectionstrings.com and pick one from the ODBC driver section e.g.

SQL Server 2008

Driver={SQL Server Native Client 10.0};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

3. Finally, make sure there are no extra spaces in the driver string as this will also mess things up!

The typical error you will receive when testing the connection will be:

The PerformancePoint Server could not connect to the specified data source. Verify that either the current user or application pool user has Read permissions to the data source, depending on your security configuration. Also verify that all required connection information is provided and correct.

If you see this – triple check the points above!

Advertisements
Categories: Uncategorized
  1. Mark Macrae
    June 18, 2010 at 1:28 am

    Hi Andrew

    This sounds like it could be permissions related as Dashboard Designer reports it cannot find the data source when the access account doesnt have permissions on the data source.

    Is PPS configured to use pass through security or the PPS App Pool account? Either way it is likely that the account used via SharePoint is different to the PPS Preview.

    Additionally you could be suffering from a double hop issue if your PPS server is separate from your SharePoint environment. You will need to configure Kerberos if this is the case.

    Feel free to ask for more info.

  2. Andrew Karcher
    June 16, 2010 at 5:04 pm

    Mark,

    Did you ever run into any issues with the connections once they are published in a SharePoint Web Part? I have a KPI that is pulling information from an ODBC connection. It works fine when I am viewing the data in the PPS Preview site. However, when I add it to a SharePoint site in the Web Part it fails with the following error: “System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified”

    Any ideas would be helpful

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: