Multiple Databases 1

[Swathi37]

Hello,

I have a question. How do I use a table from MS-Access and another table from Oracle in a Catalog (Cognos Impromptu). MS-Access will have an ODBC connection.
Please help
Thank you
Swathi

 

[BruceReed]

You cannot do this directly. I found a thread with more details: thread401-472817

Personally I get around this by "merging" my reports in transformer. I set up Impromtu with differnt catalogs (some with OBDC, some with Access, some with Excel) and then just used transfomer. End result all the data gets "cubed" regardless of the source of the orginal data.

Post if you want some more details on the Transformer/ PP solution.

Good luck
Bruce

 

[Prasad678]

Swathi,
The only way to access multiple databases in Impromptu is thru HotFile.

Hotfile is like a snapshot which contains copy of the data & has to be scheduled to get refreshed.

U have 2 DBs. Generate hotfile of one DB & connect to the other DB thru catalog. Pull this hotfile into the catalog, create joins & go with it for reports.




Prasad
RPrasad1@Chn.Cognizant.com

 

[Swathi37]

Hello,

Thank you so much for all your response. Bruce, I have just started with Cognos. I got no idea about transformers. I got to go through PP and try my luck. Thank you so much.
Prasad, I have used hot files for connecting but each time when I run the report I need to update the hot file(i.e when my Access table gets updated). Thanks a lot for your response.

Swathi.

 

[GJParker]

One way to achieve this is to link the oracle tables via ODBC into the Access database.

HTH

Gary Parker
Systems Support Analyst

 

[BruceReed]

This is my biggest complaint with Cognos, you are not allowed to connect to different databases at the SAME time.

The solutions (above) are work-arounds, you have to find out which one is best for you.

Using Gary's would probably be best for you, but you have to know Access enough to link to Oracle from within Access. However once this is done, it should be no problem.

Using hot files are great, except (as you said) they have to updated also. Making a macro to do this would have to be done.

Good luck
Bruce

 

[Swathi37]

Wow!!! Gary your solution really helped me a lot. Well I could do it very easily when I connected oracle tables via ODBC and including Access and oracle tables in COGNOS. I didnt have to do workaround. Bruce, I find Gary's solution is the best for using multiple databases instead of workarounds. Now I don't need to update anything to run my cognos queries.

Thank you so much.
Swathi.

 

[CanadianTechie]

Swathi,

Gary's solution is indeed a slick way around the "multi database" scenario you describe -- Microsoft Access is well suited as a "gateway" to multiple data sources, and thus getting around the one database limitation of Impromptu.

Further to Bruce's point, keep in mind that Impromptu is one of Cognos' older products -- Transformer/PowerPlay does not have this restriction. Also, from a performance point of view, running Impromptu reports against multiple data sources could cause issues in many environments -- if you are building a dimensional datawarehouse/datamart (which is highly recommended if you are in a large data enivonment and demand high performance reporting solutions) then you can pull in these multiple data sources into staging areas and build your data warehouse -- ergo your single data source for Impromptu. I hear where you are coming from though Bruce...just thought I'd spin it a different way for you...



Pedro Cardoso
A.K.A CanadianTechie

 

[Swathi37]

Thank you so much Pedro. I appreciate your suggestion.

Thanks
Swathi.

 

[CogUser]

Hi Swati ,

I didn't understand you when you say that
"when I connected oracle tables via ODBC and including Access and oracle tables in COGNOS. I didnt have to do workaround"
Can You explain I have a similar kind of recquirement.

Tq