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...
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?
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.
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.
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?
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.