Connecting BusinessObjects (IDT) to Amazon Redshift

[Former Member]

We're running a PoC trying to demonstrate running an on-premise BusinessObjects BI4.0 platform against an Amazon Redshift back-end.  My understanding is that this should be possible, but we're running into stubborn error with little commentary available on the web.

BObj version BI4.0 SP4 Patch 5

PostgreSQL version 8.04.02

The problem is as follows...

• Using the Information Design Tool, we can create a Relational Connection that tests successfully.
• We can then define a Data Foundation.  But if we try to view any data through that Data Foundation, i.e. if we do anything that generates a query against the back end, the query will execute successfully once, but all subsequent executions throw the following error.
  
  Error executing query
  Error: Error while refreshing the data prover.
  
  Cause of Error
  ERROR: current transaction is aborted, commands ignored until end of transaction block; No query has been executed with that handle

Our suspicion is that this is a PostgreSQL AutoCommit issue.  In SQL Workbench, we can recreate and resolve this error with the AutoCommit flag.  However, in the IDT connection we've not been able to effectively leverage the AutoCommit command.  We've tried "SET AUTOCOMMIT = ON" as a ConnectInit parameter value, but with no luck.

Anyone have any bright ideas?

[former_member191664 · Accepted solution]

See if this will help by

1. edit C:\Program Files\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\odbc\odbc.sbo

2. add

<Parameter Name="Transaction Mode">AutoCommit</Parameter>

3. Save and quit

4. Re-Launch IDT

Hope this helps,

Jin-Chong

[Former Member · Accepted solution]

Jin-Chong,

Thanks for the suggestion.  I tried a couple of variants of this in the odbc.sbo file, but without any luck so far.  I'll try a few more and let you know the results.

Though this raises a slightly different question.  Do you know why there is no postgresql.sbo file?  I would have thought that this is where I should have made such a change, but there is no such file as there is with other DB types.

Thanks,

Josh

[former_member191664 · Accepted solution]

Hi,

Check out C:\Program Files\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\odbc\greenplum.sbo (GreenPlum 3/PostgreSQL 8).

Hope this time it will work by adding <Parameter Name="Transaction Mode">AutoCommit</Parameter> to this greenplum.sbo.

Regards,

Jin-Chong

[Former Member · Accepted solution]

Tweaking greenplum.sbo fixed it.  Weird.  Thank you!!

Josh

[Former Member · Accepted solution]

Thanks for this - it also fixed an error I was having trying to refresh values in IDT. The error I recieved was:

ERROR: Invalid protocol sequence 'P' while in PortalSuspended state.

[Former Member]

Now here's a new twist on this question.  I was able to successfully connect the IDT tool into an Amazon Redshift DB, and build and publish a simple Universe to the BObj BI repository.  However, when I try to build a simple Webi report on that Universe, I get the following ODBC error.

"Database error: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified. (IES 10901) (WIS 10901)"

This is the typical message we'd expect when we have a mismatch between our client and server DSN or drivers, and we're familiar with this kind of issue.  But to the best of our knowledge, we're not committing one of those basic errors... the client server drivers and DSNs match.

PostgreSQL driver version = 9.01.02

Any ideas what else might be causing this problem?

Thanks,

Josh

[Former Member]

Hi Josh - Curious to know if you finally managed to make WebI work with Redshift. Thanks!

Regards,

Brian

[Former Member]

i'm also curious...

[Former Member]

My apologies... I didn't see these questions until now.  The short answer is, yes, we did ultimately get Webi to work against AWS Redshift.  The problem was never with Webi but rather had to do with how you configure the connection of your Universe to address the auto commit issues.  The steps described previously in this thread were helpful.  I also found that I needed to set the Connection Pool Mode to "Disconnect After Each Transaction" in order to fully resolve AutoCommit issues.