IMPROMPTU HARD CODE DATABASE INTO CATALOG

[benbotektips]

Here's the problem:
I have a production catalog and database, let's call them PROD. The catalog pulls in tables from the PROD database defined in the databases section.
I get projects to parallel develop on another database. So, I copy the PROD catalog, rename the copy to TEST, and point its tables to the test database, also defined, let's call it TEST.
The only way to copy calculations and formatting for the TEST reports, is to open the PROD reports (and catalog, especially need to look at the SQL) in one window, open the TEST ones in another window, and copy.
From time to time, I'll close a report in test, which leaves the catalog open. I'll open another report in PROD, which has its catalog defined as PROD, and view the report. Later, I'll find out that since I didn't close the catalog for TEST, the PROD reports and catalog are actually pulling in data from the TEST tables!!!
Of course, this is another Cognos bug. If a developer points a report to a specific catalog, naturally they'll want the report to always bring in data from the corresponding database too. How can I lock, or hard code the database in the table section of the catalog, so that PROD catalog will always pull data from PROD database, and test catalog will always pull data from TEST database, unless I go to the 'report-general' menu, and switch the catalog for the report? In other words, how can I force a logical association, PROD reports = PROD data, TEST reports = TEST data, with the only override being to switch the catalog?

 

[griffindm]

Ben,

You are correct that if the catalogs are similar enough structurally, Impromptu will allow you to open a report with a different catalog than the one specified in the report. Worse, it will save the new catalog association to the report if the report is saved with the new catalog open.
I do not believe Cognos regards this as a bug, but rather an 'undocumented feature'. It does allow for easier migration of a report to a new catalog. You do have to be careful about closing a catalog and allowing the report to open the catalog for you though.

You have two options when changing physical databases. You can either setup two logical databases (PROD and TEST) and toggle between them within the catalog, or you can change the physical database connect string in a single logical database definition on your computer. The former will persist for any user of the report if they have the TEST database defined in their ini file if they use that same catalog. The latter only affects the computer that the connect string is reset on. I use the latter exclusively when doing test development myself.

Hope this helps,

Dave Griffin



The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[benbotektips]

Dave,
I'm pretty sure the new catalog association saving will happen even when u don't save any changes to the report , I've gotten reamed when simply having my TEST report open, closing the report, naturally saving its changes (but not closing the catalog), then opening the PROD report to browse (which I mistakenly thought would hard-code the PROD data tables).
The PROD and TEST databases are both defined, and as you correctly point out, it'll save the association as whichever database is open when the report is run. The goal is to NOT allow toggling between the databases, and I must have both physical databases defined on my computer, to do testing of the report fields for rollout of a new database with the same reports, for example. So, is there any trick to secure the connection, PROD database will always and only use the PROD database definition, TEST will only use TEST? This will still allow upgrading of the PROD reports to the rollout (new) database, as you can switch catalogs. But what I need is to not allow a catalog to switch (or toggle) databases. Failing that, how about setting up a message box or prompt when the catalog's database is toggled, so at least I'm getting a clue that the PROD catalog will be doing what I don't want, pulling TEST data?
It'd seem to me this is a problem someone has written a macro for, if not coming up with some trick.
One thought: would accessing the reports via a remote connect (like win2000 'remote desktop connection') make any difference?
Hmmm, indeed, it is a puzzlement...

 

[TinaCMC]

Has anyone written a macro within Impromptu to change data bases based on a picklist or user input? I am working with multiple production data bases and users need to be able to access each one.

 

[griffindm]

Ben,

You are probably correct about not even saving being required. Prior to version 6 this would not happen, but in version 6 Impromptu began overwritting the OS file time_stamp even when an explicit 'Save' was not being done. I immediately marked all of our production reports as read-only to avoid this.

In your case I would save two versions of the catalog, with different file names. Each would have the Tables Database pointer to the different databases. As long as the user has both logical databases defined it should allow persistent switching between the test and production databases with the same report as long as the specific catalog is opened first!

You could automate this in a macro, giving the user a choice between the two catalogs prior to opening the catalog and then the report.

Let me know if you need more info.

Regards,

Dave Griffin

p.s. It's great to see a new forum member like yourself contributing so much to others. Keep up the good work!



The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[benbotektips]

Dave,
I understand your scheme; in fact how I have my setup is a complete parallel world, duplicate reports running off a catalog with a different name, pointing to a different database, on a different server no less.
Where I get slammed is the scenario where I'm looking at both reports (the production & its test clone) in 2 windows, then I close the production window; I then close the test report only, leaving the window open (and therefore the catalog). I then do something else; hours later, I'll come back to the open Impromptu window, and run
a production report, not realizing the catalog is still pointing to test data. In fact, our users simply don't have the test database access; they know something's wrong when they run a report and get the Oracle message 'TNSNAMES ENTRY NOT FOUND'.
I realize Cognos didn't enable automatically closing the catalog when closing the report, for 'convenience'. Is there a way I could force this? Think of it like a locking fire door; when I leave a theater thru a street entrance, I can get back in rite away; when I leave thru a fire door, I can't. That's what I want: the catalog to close when the report does, or to lock the database to the catalog. If I'd need to switch databases (like, migrating test to production), I'd simply copy the test catalog when I'm ready to go live, change the database in the copy, then replace the production one. Not getting yelled at for an inadvertent database switch is worth the extra minute of effort.
If I mark the catalog as read only, I can't make changes to the tables, joins etc. No way to just bond the database picked to the specific catalog?
P.S. Happy to contribute here, this forum rox! I'm trying to pass on knowledge as well as absorb.

 

[griffindm]

Ben,

If you are the only user running reports in the test database, why not just create a batch file to overwrite the cognos.ini file from a pair stored copies, 1 pointing to production, 1 pointing to test. This is what I have most of the developers here do in this scenario. That makes the effort to toggle less painful. You still have to remember to do it.

If you want to go the route of having test and production catalogs, then an alternative to closing the report when you are done is to close the catalog instead. This also closes any open reports and ensures you are not 'carrying over' a catalog association to a different report. Just a matter of changing your routine.

Hope some of this helps, even if it's only knowing someone else feels your pain.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[dagwoodnil]

Dave,

I'm not sure what you mean when you create a batch file to overwrite the cognos.ini file. Do you mean to use the batch file to overwrite the 'ini' file based on the catalog you want to use.

The project i'm am currently on have three parallel database (fix, test and production) which means three catalogs. our way of dealing with multiple catalogs is to name them the same name (ie name.cat) and filed them in different folder. Whenever we make changes to the fix catalog, the other two catalogs are also modified. Other developers get the latest copies through a version manager. It is kind of a pain whenever we modified the catalog. However, we don't need three different reports each pointing to a different catalog. The report retrieve information based on whichever catalog is opened.

This was the best solution we came up with.

 

[griffindm]

Ben,

The cognos.ini file contains the connect strings that link the Impromptu logical database to the physical database. (In some subsequent version this file is to become obsolete and be replaced by the cer.ini file). By saving two versions of this file, one for each connect string to the production and test database, you can use a batch file to overwrite the existing ini with either of the saved copies, which changes the database without caring which catalog is opened afterwards. The only issue is that you cannot view reports from both production and test at the same time, which I think you mention that you need to do.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[benbotektips]

Dave & co.:
Thanx for all the advice. I don't want to touch the ini, espceially if it means I can't open prod & test at the same time. I guess the least annoying way is to close the catalog when done with a report. It's the safest way, and just a matter of retraining reflexes - like closing your mouth when chewing gum . Hopefully, no more switched databases.