Combining two stored procedures into 1 query subject in Framework Manager

[civicman4]

I created two stored procedures, one for the East region of our company and one for the West, and I want to join them together into one query subject in Framework Manager.  The reason why I did this is because our East and West databases are on separate servers and I want get around the performance issue of going across servers to get data.  Both SP's have the same columns and fields and they also have the same parameters (which is a date range).  Essentially it should look like one package which returns one set of results but on the back end it calls out to two SP's.  Is it possible to do this?

[PRIT AMRIT]

After importing these two SP's, have tried joining these query subjects based on the common column and creating another Model Query Subject out of it?

[FM]

However you do this, you are forcing Cognos to become an EII (Enterprise Information Integration) product. Something it is not designed to do well.

Combining data from different data sources is better done with a product such as Virtual View Manager.

Even better, is if your databases are oracle. You can implement a dblink, and create one stored procedure in one database that will call that other stored procedure from the second database, and return the union of the result set to the customer.

To them, it's transparent.

Otherwise, you VVM. otherwise, depending on the size of your resultset, performance won't be good.

[cognostechie]

I created two stored procedures, one for the East region of our company and one for the West, and I want to join them together into one query subject in Framework Manager.  The reason why I did this is because our East and West databases are on separate servers and I want get around the performance issue of going across servers to get data.  Both SP's have the same columns and fields and they also have the same parameters (which is a date range).  Essentially it should look like one package which returns one set of results but on the back end it calls out to two SP's.  Is it possible to do this?

Yes, you can do this but instead of joining, creating a union of both query subjects into a third query subject is what you need. Join will add the fields, union will keep the no. of fields same but add the data from East and West in rows.

That being said, FM's idea of doing this in the DB is better for performance.