SYMPTOM:
In Strategy Developer 9.x and 10.x, database instance table prefix is not used in the creation of datamart tables. Table prefix in a database instance can be set by going to Database Instance editor and then going to the Advanced tab as shown below:
After setting the database instance table prefix, if the user creates a datamart report, the datamart table that is created in the process does not use the above table prefix as seen in the following SQL below:
create table DatamartTable (
COUNTRY_ID BYTE,
CUST_COUNTRY_DESC TEXT)
insert into DatamartTable
select a11. AS COUNTRY_ID,
a11. AS CUST_COUNTRY_DESC
from a11
CAUSE:
This is working as designed. Datamart tables have their own table prefix which can be specified in the Report Editor under Data > Configure Data Mart > Advanced, as shown below:
The database instance table prefix is only used when jobs such as report execution and element requests are run for projects associated with that particular database instance.
Notice when the datamart table prefix is used the SQL changes to the following:
create table TestDatamartTable (
COUNTRY_ID BYTE,
CUST_COUNTRY_DESC TEXT)
insert into TestDatamartTable
select a11. AS COUNTRY_ID,
a11. AS CUST_COUNTRY_DESC
from a11
