Need to connect to GreenPlum Database

building semantic layer
1

Hi ,
We Need to connect to GreenPlum Database in SAP BI 4.0,
i dont see any specific driver to set up a system dsn for this data source.
So can any one share your experience if you built a universe based on green plum db(UNX using IDT in 4.0)
or even in earlier versions of BO how to create a system dsn for greenplum db,
which driver we need to use.
Thanks.

boputty (BOB member since 2007-05-19)

2

I’ve been connecting to Greenplum from our XI 3.1 platform using the generic PostgreSQL 8.03.04.00 drivers. It has been working well, but we initially had to tweak a bunch of the driver settings to get it to function consistently. From the universe connection side, we set it up as an ODBC3 type connection.

I do know that there is a specific “Greenplum” connection type in designer, but I do not know what drivers are required to use that type.

We’ve also tried the Data Direct Greenplum driver version 6 but what we found with that driver was that Webi Rich Client would suffer from #DATATYPE errors where a numeric value was expected. So the Data Direct drivers seemed to be interpreting the results wrong causing problems for the client tool. So we’re sticking with the PostgreSQL drivers for now.

We just deployed Greenplum 4.0 and are getting ready to cut over to it next week. I’m trying to get EMC to come back with their recommendation for which driver type or version we should be using with the new version.

alpha1145 :us: (BOB member since 2006-01-04)

3

Hi Thanks For your inputs,
do you remember what are the changes you made at the driver settings level.
i am unable to find the free download of this drivers,
do we need to purchase them,
once downloaded we need to install them in the folder where we installed bo or its ok to just install at the default location,
do we need to make any cahnges to the .sbo file.

Thanks alot for your help

boputty (BOB member since 2007-05-19)

4

The generic PostgreSQL drivers are open source and can be obtained here:

http://www.postgresql.org/ftp/odbc/versions/msi/

Note that there are newer versions of the driver (9.x.x.x) but I personally haven’t tested or used anything above version 8.3.4.

We’re running BO on a Windows Server 2003 platform, so the installation is as simple as running the msi installer package. You then go to the ODBC control panel and add an ODBC connection using the PostgreSQL driver which now appears in your list of available drivers. Note that it will install ANSI and UNICODE versions. We use the UNICODE driver.

I’ve attached screenshots of our settings. Note: Your settings may differ based on your database config, but these settings have worked great for us.
Greenplum PostgreSQL Driver Settings.pdf (88.0 KB)

alpha1145 :us: (BOB member since 2006-01-04)

5

We did have to make the following update to our odbc.prm file in order to enable editing of outer join conditions

Problem: Users are unable to edit the outer join conditions on the Greenplum universe (when using universe designer).

Solution: Need to update two parameters in the odbc.prm file on the user’s computer.

  1. Navigate to the following local directory path:

C:\Program Files\Business Objects\BusinessObjects Enterprise 12\win32_x86\dataAccess\connectionServer\odbc\

  1. Open the odbc.prm file in a text editor

  2. Change the values for the EXT_JOIN and the OUTERJOINS_GENERATION to YES.

Final code should look like this:

<Parameter Name="DB_TYPE">GENERIC</Parameter>
		<Parameter Name="SORT_BY_NO">YES</Parameter>
		<Parameter Name="GROUPBYCOL">NO</Parameter>
		<Parameter Name="EXT_JOIN">YES</Parameter>
		<Parameter Name="CONCAT">+</Parameter>
		<Parameter Name="UNION">UNION</Parameter>
		<Parameter Name="UNION_IN_SUBQUERY"></Parameter>
		<Parameter Name="INTERSECT"></Parameter>
		<Parameter Name="INTERSECT_IN_SUBQUERY"></Parameter>
		<Parameter Name="MINUS"></Parameter>
		<Parameter Name="MINUS_IN_SUBQUERY"></Parameter>
		<Parameter Name="OWNER">Y</Parameter>
		<Parameter Name="QUALIFIER">N</Parameter>
		<Parameter Name="COMMA">+' '+</Parameter>
		<Parameter Name="NO_DISTINCT">Y</Parameter>
		<Parameter Name="REFRESH_COLUMNS_TYPE">T</Parameter>
		<Parameter Name="CHECK_OWNER_STATE">Y</Parameter>
		<Parameter Name="CHECK_QUALIFIER_STATE">Y</Parameter>
		<Parameter Name="KEY_INFO_SUPPORTED">N</Parameter>
		<Parameter Name="OUTERJOINS_GENERATION">YES</Parameter>

  1. Save.

  2. Relaunch Designer.

alpha1145 :us: (BOB member since 2006-01-04)

6

Hi alpha1145,
Thanks alot for the valuable info,
really appreciate for your time.

boputty (BOB member since 2007-05-19)