Monday, June 23, 2008

BI Source systems: DB Connect vs. UD Connect

To connect SAP BI system to source system in purpose of data retrieval you can use apart of other types of source systems following ones: DB Connect & UD Connect. Both of them are basically connection to any database system which is able to provide data for BI system. DB Connect relies on ABAP Stack of WAS besides UD Connect is built on JAVA Stack of WAS.

DB Connect - Enables connections to different relational database management systems and the transfer of data from tables or views from these database management systems into the BI system. Technically it opens another database connection (default database connection is dedicated to SAP kernel which opens a connection to the database on which the SAP system is running) and by this connection data are coming from database to BI system. There is a dependency on database type. There can be two cases: BI system is based on same/different database system as source system. If they are same on BI side (on its application server) there must be installed Database Shared Library (DBSL). If they are different a database-specific client for the particular source database on the BI application server needs to be installed before you can use the DB Connect functions. As SAP system is able to run on different database system a several common industry standard databases are supported. As a source object in database you can use directly database table or view (schemas as well).




UD Connect - Component of BI that, together with the SAP Web AS J2EE connectivity (JAVA Stack), allows you to access virtually all relational and multidimensional data sources. Various drivers, protocols and providers as resource adapters for BI JDBC Connector are available: BI ODBO Connector, BI SAP Query Connector, BI XMLA Connector.

UD Connect (Universal Data Connect) uses the BI Java Connectors as resource adapters for establishing connections to data sources. The data can either be loaded into BI, or accessed directly using a VirtualProvider.

UD Connect source is instance that can be addressed as a data source of BI using BI Java Connectors.

UD Connect source object is multidimensional or relational data store in the UD Connect source, for example, table or cube.

One important prerequisite is there: BI installation must be installed with J2EE Engine (JAVA Stack of WAS) and with BI Java components.



25 comments:

lotto 649 said...

All I can say is nothing because your blog is not interesting to read.

sapper said...

Hi lotto_649, thanks for your comment. Sorry bother you to browse my blog. Why do you mean that? BR

Joffrey said...

Hi!

Thnks for the explanation. Very interesting. Only question is: Why must we specify a RFC destination for a UD connection? What should it contain?

Thanks
Joffrey

sapper said...

Hi Joffrey,

in case of UD it is assumed that DB is running on other server or even on server located in different network/sub network therefore you need to specify RFC destination which is determining host name/IP address of remote computer on which DB is running. Moreover RFC destination contains user credential how to access this remote system etc... check TA SM59.

Cheers,
sapper

Anonymous said...

Dear sapper!

Thanks for the explanation. I have a problem touching this subject. I need to define an MSSQL DB as source to a BI (JAVA stack). BI runs on UNIX! Is it possible to set this definition working whitout installing a new sap app server on a windows?
Best regards
Robert

sapper said...

Dear Robert,

Yes, based on my experience it is possible to set up connection via DB Connect. You need to define new source system in BI back end (TA RSA1) type of DB Connect, DBMS type of MSS (MS SQL Server).

BR, sapper

Anonymous said...

Dear sapper!

Thanks for your reply! Clearing more my problem, I've got a BI on UNIX. In DB connect you need the foreign DB's client program on the BI app server to be able to connect to the other RDBMS. In this case you need MSSQL client software and as far as i know there isn't client for MSSQL on unix (maybe I'm wrong). And I don't want to install an additional BI App server on a windows system where i could install an MSSQL client.
But I found doc for UD connect and with it it is possible to connect with JDBC (and MS has unix JDBC driver) to MSSQL from a unix BI.

Best Regards
Robert

sapper said...

Dear Robert,

yes for DB connect you need to have an Database Shared Library (DBSL) to acess MSSQL since your
UNIX BI system doesn't have this DB type as default DB connection since MSSQL is not running on UNIX.
On some of my project they ran some DBSL on UNIX to connect MSSQL. Unfortunately I wasn't involved enough to remember
name of this driver.
Let me get back to 2nd option: UD Connect. There is a free how to paper provided by SAP on SDN

which describes ho to do it.

BR, sapper

Charlie said...

Dear Sapper,
I have studied some documents but still not very cleared about when to use which type of the connection.
As far I know both of the connection supports linking of SQL DB; so how do I decied which method to use ?
UDC is used for Java-based system; could you define what's mean by "Java-based system" ??

Thanks a lot !!
Looking forward to hearing your reply

sapper said...

Charlie, JAVA based system means any application build on top of any JAVA application server SUN/IBM/ORACLE what ever. Interoperability is much more better since UDC is JAVA based as well.
BR, sapper

Charlie said...

Dear Sapper,
Thanks a lot for your reply; So is there any advantage on UD connect over DB connect and vice versa? or they just the connector for different platform?

Charlie

Joffrey said...

UD Connect goes through the java stack and uses JDBC connectors which offer more flexibility

Todd said...

Does UD Connect encrypt the login credentials?

Thanks
Todd

sapper said...

Hi Todd, it does depend on settings of your system.

BR sapper

Arsenos said...

Hello SAP'ers

i am installed (BI 7.0) on DB2/400 (iSeries) and want to access MSSQL 2005 via UDConnect.

Can somebody provide me the link to the JDBC driver.

Are the JARs still msbase, mssqlserver & msutil.jar for 2005 or is sqljdbc.jar sufficient ?

Unfortunately i can't get help from SAP; their answer on this issue is :As mentioned, our tasks do not include providing suggestions about
configuration & how-to requests !

On top of that, their How To documentation dates from jan 2004 !

Most probably they don't know otherwise it would be quite simple to asnwer.

I took in consideration SAP Note 1109274 and 639702.

Best Regards,
Roger

sapper said...

Hello Roger,

You might use an JDBC-ODBC Bridge Driver as suggested by SAP AG. Notice that it might be a 3rd party product and you have to pay for it. Check out this: http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/bridge.doc.html

Regarding JARs - sorry, I have no idea.

Concerning SAP's responses - don't be surprised, they always try to avoid so called "consulting issues" as sent by customers via OSS. I don't want to apologize them but imagine they have a lot to do solve real issues with their products and not spent time to suggest solutions, etc...

BR,
sapper

Amit Satsangi said...

Hi,
if I have a choice between UDConnect and DBConnect which one should I choose and why? Is there any difference between the speed/ efficiency of one vs. the other. I need to transfer data sitting in tables that have approx. 3-4 million records each.

Thanks,
Jay

sng said...

Thanks a lot. Useful one.

David said...

Hi sapper,

Currently we use DBconnect to extract data from SQL2000 and it all works well. But now we need to connect to SQL2008 as well, I can see the view in BW but cannot edit the datasource. The SQL user has all the permission and the view is under that user.

Is SQL2008 supported using DBconnect? Do we have to update our library or install anything else on our application server?

We are currently on BI 7.0

Any ideas?

Dawood

sapper said...

Hi Dawood, check note 1152240 - Setting Up Microsoft SQL Server 2008 (R2). Seems that SQL2008 R2 is not released for any SAP products.
Anyway have you tried to create new DS, not just edit existing one?

BR
sapper

Dawood said...

Hi Sapper,

Yes i tried creating a new DS but no luck. I am not editing any existing DS, When i create a Source system and then search for tables and views and then select my view then i press the edit DS which is supposed to show the fields in that view.

I think we will replicate the DB down to sql2000 or 2005 and extract the data atleast we know it works that way

Iain Rogers said...

Hi sapper,
Do you know anything about the ability of these tools to do data federation?

Can I create optimal queries against two data sources through DB Connect (or UD Connect)? I'm concerned that using these tools would mean I'd have to read in the whole of one database to merge against another.

Thanks,
-IainR

RITU said...

Hi ! thanks for the explaining DB connect and UD connect. just want to know do we create infopackages in these cases or not . can you pls elaborate , many thanks
Ritu

Princess said...

In db connect data sources changed then it is not replicating properly and we are failed to extract the data.

João said...

Links for MS jar files, for future browsing. :-D

http://msdn.microsoft.com/en-us/sqlserver/aa937724

http://msdn.microsoft.com/en-us/library/ms378526.aspx

http://jtds.sourceforge.net/