Data server changes and switching database vendors
When designing and maintaining metadata models, the modeler must consider changes to the underlying databases or the requirements to change the database vendors altogether.
Each modeling tool provides a way of updating changed metadata. For example, some column names in the database might change, or columns might be added or removed. You want to ensure that such changes are reflected in the model so that errors don't occur.
- Case sensitivity
- Matching names of schemas, tables, or columns
- Compatibles data types
For example, DB2 has the DATE data type which is not compatible with the TIMESTAMP data type in Oracle. The cleaner the match in structure and data type is, the smoother the vendor switch will be. Switching vendors is not an exercise to be taken lightly. Appropriate planning should be in place before implementing.
Framework Manager and data modules require slightly different methods for database updates and vendor switching.
After you reload the schema metadata for the data server source, you can re-add tables to the data module to include changes from the database. If a new column is added, it appears in the data module. If a column is missing, the validation process flags an issue, and you can delete the column from the module. For more information, see Updating columns in a data module and Reloading schema metadata.
Use the Show unused items feature on the data module source to see newly introduced columns in a table. This feature is also useful when you remove columns from a table in the data module, and then want to add them back. In this case, the columns are highlighted in the Sources panel
If a table name changes, import the new table and delete the old one. However, any table view that was based on the old table needs to be recreated.
When you need to replace the data sources altogether, you can do so by relinking the source. For more information, see Relinking sources.
As with Framework Manager, the database structure, naming conventions, and data types must match between the old and relinked source.
When a table column is changed, or a column is removed or added, you can update the data source query subject to reflect the change. For more information, see Updating query subjects.
Once this happens, any model query subjects that are dependent on the data source query subject must also be updated to reflect changes. New columns can be added manually.
When a table name changes, you need to import the table as a new object, delete the old one, and then remap any dependent model query subjects to the newly imported table. For more information, see Remapping objects to new sources.
If you plan to change database vendors or allow dynamic access to different vendors by using macros at run time, ensure that correct values are specified for the data source properties in the model. For example, the Content Manager data source name must match a data source that is configured in Cognos® Analytics, the data source name must point to the intended database vendor, and the catalog and schema properties must be set up correctly. For more information, see Data sources.
When the model was built using a business layer approach to insulate reports from changes to the database, and when there are data type or naming convention conflicts, you can import the new tables and remap the dependent model query subjects to them. Then, delete the old data source query subjects.