What are the pros and cons of using an ODBC vs OLE DB connection? I created both of these connections in designer pointing to the same database using MS SQL Server 2005. We are going to be re-pointing all of our Universes to use this new database and are trying to decide which connection to use. We are on XIR2 SP2 and are planning to upgrade to XI 3.1 soon. Are there any known issues or differences with this in XI 3.1?
One issue that I have seen in XIr2 with OLE-DB Connections is that if a user cancels a WebI query during execution, it will wait for the Database to finish the SQL before releasing control. ODBC Connect did not have this issue. If Client tools (Designer/DeskI, etc…) are being used, and you use ODBC connections, then these need to be managed on the desktops as well.
My preference is ODBC, even though it requires a little more management.
Thanks you for your responses. You both made good points. I’m still researching this topic before making a decision. Any additional infromation that you could think of would be greatly appreciated.
Clark, do you know whether this behaviour differs between service packs, If so, I would consider it a bug.
However, seeing as BOXI cannot even manage its own connectivity effectively, what with keeping alive expired sessions and the like I cannot really be surprised…
Did you run a trace against the BO user account on your SQL Server to prove this behavior.
Obviously, the advantages of using OLEDB are simpler admin and speed.
I have always used OLEDB whenever I can, although I would reconsider with regards to your comments, especially when managing / advising on a large deployment, with a lot of adhoc user activity.
We used to use ODBC for all of our old Crystal reports, but then had to change to OLEDB.
The reason was that somehow our server would no longer let us make any more ODBC DSN entries. I don’t know if we hit a maximum number (there were only about 20-30 or so if I recall) or if some service pack caused the problem.
@XXX
I’m not sure I understand your question…when you say Windows Authentication, are you refering to SingleSignon? Designer lets you choose from 3 authenticaion methods, Business Objects, Single Signon or Designated UserID/Pass. (At least in XIR3, maybe it’s different in other versions?)
And which configuration option are you refering to? most of it is pretty standard: servername, UserId, Password etc… There aren’t really any configuration options to speak of.
Most of our reports are Crystal Reports although our use of Web Intelligence is growing. We don’t use DeskI.
We use ODBC connections for any universe that has Crystal Reports going against it that are SQL Server databases. The reason for this is we have a standard that all queries passed to our SQL Server databases must have either the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED passed in or must use the With (NoLock) table hint. There is a registry hack that must be done for this to work with Crystal Reports using ODBC connections. I have not been able to find a way for this to work with OLEDB connections.
If we have a universe that is used only for Web Intelligence reporting over SQL Server databases, we will use OLEDB because adding the statement to the OLEDB.sbo file is sufficient to pass the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.
I have only tested these in our current version. I have not done any testing yet in XI3.1.
XXX - To address your question a little, we use database authentication for all of our reporting access. This really comes down to a control issue. Business Objects is a control point for access to the data. By having a specific login that is used by Business Objects and shared by those who have access to Business Objects, if gives us better control. It also helps that we know any activity for that user ID is for reporting purposes and not for anything else.
I myself would prefer to use OLEDB also as it is easier to administrate if we could.
One other advantage to using ODBC is that since a local ODBC connection must be set up for Crystal Reports, the developer can actually point their ODBC at a different database than the one that is set up on the BOE server. This allows them to easily see a different set of data when testing their report.
(BOB member since 2007-02-28)
(BOB member since 2005-01-06)
.
(BOB member since 2005-01-06)
(BOB member since 2007-09-04)