CONTENT
Sometimes it is desired to use tables with the same name but different structures from different schemas as different tables in Strategy. This article covers two different methods to implement this functionality in Strategy.
Currently in Strategy tables are identified by their names and their names alone. This means it is not possible to have two different tables with the same name and different structures from different schemas (supporting multiple tables with the same name and same structure from different database instances is possible with the multi-source feature). There are however two workarounds that can be used to support this behavior in Strategy. Each workaround has different benefits and caveats.
Option 1:
The first option is to change the table name itself. This can be done inside of Strategy for databases that support using table read SQL to import tables from the database. The main goal here is to make the table names unique and functional for the different tables in the database. The simplest way to do this is to use the string function concatenate to combine the table prefix, a period and the table name into a single value. This means the table read SQL will select a function similar to the below instead of just using the table name column.
concatenate(schemacolumn, '.' , tablenamecolumn)
The particular syntax here will depend on the database platform in question and the naming convention for the columns in question. Some databases call the schema the owner, name space, table space, etc. This SQL is not something Strategy support can provide.
Once this SQL is changed, more work has to be done. The column read SQL must also be changed so the where clause which filters on table names can be used with the new table name structure. The new where clause will need to look like the below.
where concatenate(schemacolumn, '.' , tablenamecolumn) in ('#table_list#')
With this change importing tables should now work successfully. One additional step must be taken. VLDB properties and prefixes must be set for the tables so that the schema is not separately included as an additional prefix as it is already included in the table name itself.
This workaround scales well to new work as all the difficult work is done up front. It's caveat is that if applied to an existing environment it would require remapping all attributes and facts to the newly created tables with prefixes concatenated into their names. It also requires knowledge of the database syntax for concatenate to write the custom table and column read SQL.
Option 2:
The second option is to import only one set of prefixes through warehouse catalog for the tables. Other versions of the table from other schemas will instead be mapped through logical views in Strategy. Logical views can be defined with any custom SQL statement and choose to include or not include () around the custom SQL. This means the table name with the desired alternate schema/prefix can be placed directly in the custom SQL without the () included. This new logical view can then be added to existing attributes and facts as desired.
This has benefits in that it can be used to add tables easily to an existing environment without having to change existing used tables. The caveat is that each and every table added in this manner will have to be manually mapped for every column it has. This does not scale well to large numbers of tables.
CONCLUSION
In conclusion options do exist to support tables with the same name from different schemas/name spaces however no option is perfect. The best choice for an environment must be chosen based on the needs of the environment.
KB483096
