How well does it work to have one package use 2 data sources, SQL server/Oracle

[LLPoolJ]

How well does it work to have one package use 2 data sources, SQL server/Oracle?

If this advisable or not?

[MFGF]

It will definitely work.  The big question is how will it perform?  There's no definite answer to this, other than to test it using a representative sample of reports.

MF.

[pratyush]

How well does it work to have one package use 2 data sources, SQL server/Oracle?

If this advisable or not?

Hi

Well its not possible in a single package with two data sources reason when you will create a relationship with any two query subjects it will give errors ... But yes you can use two different schemas of same database in a single package ......... !

But if you really want to go ahead with two datasources then the only solution is Layout references .... ;)

Create two differnet reports out of these two datasources with two packages and call them in another report with the help of layout references ... !

Cheers ........

[IceTea]

Well its not possible in a single package with two data sources reason when you will create a relationship with any two query subjects it will give errors ...

Basically, that is not true. It's possible to have relationships between two data sources - Cognos can handle this. What's up with the performance is you'll see at testing. Query processes "local" for such joins.

[pratyush]

Basically, that is not true. It's possible to have relationships between two data sources - Cognos can handle this. What's up with the performance is you'll see at testing. Query processes "local" for such joins.

Hi

But how its possible at the frame work model ..... ! It won't allow you to have joins .... ! Adding to your point if we use local for processing of Query then there are chances of not getting valid output data ... as it will use the cache ...

What you say .... !

[IceTea]

But how its possible at the frame work model ..... ! It won't allow you to have joins .... !

No, this is *not* true. It's a proven fact, that Framework Manager allows you to define relationships across two Data Sources. I even tried it right now with a short example (DB2 <-> MSSQL) and it works as designed. I'm using FM 8.4 and in my opinion this feature wasn't a *new* feature at 8.3 or 8.4...

The only show-stopper could be - like MFGF said in posting #2 - the performance, but this depends on various facts and has to be tested.

[pratyush]

Hi

So you mean to say its possible by using frame work model.. ! But is it possible to do the same thing by using the SQL or Tabular SQL in Report Studio (basically by passing the frame work manager) also ... !

[IceTea]

also ... !   what?  ???

The thing is... if you refuse to do the modeling of the relationship between the two datasources in your FM model - for whatever reason - you always have the ability to use a FM package with the two datasources (without modeled relationships) in your Report Studio and THEN use the Query Explorer to join two queries with their keys to a new one which contains data from all the two datasources. You CAN write your own SQL anyway, but i don't know where's the need for it. All can be done easily with Framework Manager or Report Studio on-board means within one report, one list...

[MFGF]

Well its not possible in a single package with two data sources reason when you will create a relationship with any two query subjects it will give errors

Hi Pratyush,

I don't like to contradict posters such as yourself when you are trying to help others, but this really does need clarification.

Support for multi-source/multi-database queries within a single report query based on a single package was one of the key design objectives for the architecture now used by IBM Cognos 8.

If you get errors when linking two data sources in either Framework Manager or within the Query Explorer of Report Studio (eg Oracle and DB2, SQL Server and Sybase etc), this is indicative that something has not been correctly defined by the modeller/author and is not a limiting factor of the product.  This functionality is fully supported by IBM Cognos 8, and is being used by many organizations around the world.

Resorting to coding SQL or Tabular SQL is completely unnecessary in this instance - simply defining an appropriate relationship is all that is required.

Best regards,

MF.

[LLPoolJ]

Thanks you guys.  I will give it a try.