Column Names in Data Models are Case Sensitive

[saadkhabir]

We have original Cognos data models linked with Oracle and all column names are in UPPERCASE. After migrating and linking it to Redshift we faced an error saying column names are not matching i.e "ABC" is not same as "abc".

Redshift is case insensitive and natively convert all coumn names to lowercase whereas Oracle is Case Sensitive. While creating reports we used UPPERCASE column names in data models which worked with Oracle but its working with Redshift.

Right now, we have to change Mapping for each column to link make it work with lowercase column names in Redshift.

Is there a way in Framework Manage where we can make data model to be case insensitive and work with both Oracle and Redshift?

[the6campbells]

In the short term, your fastest solution to get going is to write a program which makes changes to the model.xml file.

Specifically, you need to down case the external names property for the data items in the database query subjects.
You also need to change the capitalization of the table/view name in the logical "select * from [datasource].name" queries in those query subjects

This will allow update-query subjects and dynamic SQL statements to pass to Redshift names it will resolve.

Numerous databases support identifiers which are lower, upper or mixed. Should your DDL not delimit names then most often the name will fold to either upper or lower. The drivers from those vendors will advertise what character they use to delimit identifiers, most often it is single quote, back tick or sometimes nothing. The SQL generated from your reports/models will surround column, alias, table and correlation names with the delimiter described by the driver. Many vendors do not provide an option to disable that value.

You are always welcome to start an RFE on the IBM web site asking for options/utilities etc to help you do this.