Multiple data sources

[vmcharca]

I need to query between tables from 2 different databases.  I have already created the join but the problem is when I query against it Cognos will do a stitch query.   This is really bad for performance since this tables are huge!  I am also connecting to a live data source.   Any ideas on how to make it create just one query and apply an inner join?  I am doing this in Framework Manager.  also I have tried typing the sql to join between the 2 tables from database a and b and then creating another link between the new table and a third table in database b but still does the same.

[blom0344]

Content manager data source needs to be the same for each connection. This allows for direct joining.  A good alternative is to define a view in one database over  both tables.  This is trivial in SQL server (you do not state which database you are working with..) An imported view will push everything down to the database and that is an optimal solution..

[vmcharca]

I am using Sybase.  connecting to 2 databases in the same server.

"Content manager data source needs to be the same for each connection"  can you please expand on this?  The view solution wont solve my problem since there are many tables in each database that are able to link to tables in the other database.

[blom0344]

a datasource can have 1 , 2 or any number of connections. Each connection can point to a specific database. So, with 2 connections (each for 1 of the databases) that share the same content manager datasource, Cognos can optimize joins on the database side:

Further reading:

http://www.ironsidegroup.com/2010/08/01/best-practices-in-cognos-8-framework-manager-model-design-part-2-%E2%80%93-advanced-modeling-issues/
just curious: how did you set cardinalities between tables?

[vmcharca]

brilliant! I made it work by using the same data source contenct connection.   Thanks for your help.