Depending on your Oracle database configuration, you may find that your Oracle schema names vary across your environments (DEV/QA/PROD).  This can be a problem for Cognos Framework Manager, which assumes a single standard schema per data source connection.  What’s worse is that your user name and password can be changed easily from one environment to another (since these elements are defined in the Cognos portal data source connections) but not the schema (which is defined in Framework Manager and then published).  There are a few different approaches that can be taken to handle this.  The first is Oracle synonyms.    These can easily resolve your schema name challenge at the database level, but can create headaches for your DBAs.  Depending on the DBA policies of your organization, this may or may not be an option.

A second option, although not elegant, is to simply publish your model for each environment, changing the schema in the data source connection as you go.  Although this will work, it feels more like a work-around than a solution.

You can also choose to manage them by using Parameter Maps in Cognos Framework Manager.  If you choose to manage them in FM, the first step is to determine your environment.  This can be done by creating a query subject that will determine your database connection or database server.  A very useful Oracle function for this purpose is SYS_CONTEXT,  which can tell you the server name, the database name or a variety of other database environment identifiers.  It is also a widely available function, which means you should be able to call it no matter what Oracle account you are using.

Your Environment Query should look something like this:

You can then set up a Parameter Map to read your Environment Query.  Base your Parameter Map on your defined Environment Query, using the SCHEMA column as the key and the SCHEMA_VALUE as the value.

Once you’ve defined your parameter map, you need to make two final changes.  First, remove any Schema definition in your Data Source properties.  Do not attempt to call your Parameter Map schema value here.  If you try, you will get a recursive call error as this code will attempt to run for your Environment Query, which needs to run to get your schema value in the first place.

Next, add your parameter value to each Query Subject, except for your Environment Query:

Now you should be able to import and export your data model at will across your database environments without making schema changes at each turn.