KB20747: Reports against a Teradata warehouse with national character columns omit the CHARACTER SET UNICODE modifier for Unicode columns in MicroStrategy SQL Generation Engine 9.x

SYMPTOM
A Strategy 8.x project based on a Teradata data warehouse has been upgraded to Strategy 9.0. Some columns in the warehouse contain Unicode data; accordingly, the database instance is configured with the "Hexadecimal Character Transformation" VLDB Property to render all quoted strings using hexadecimal codes.
 

 
If the Intermediate Table Type VLDB Property is "Permanent table" or "True temporary table," the Strategy SQL Generation Engine will use explicit "create table" statements in which the data type for each column is specified exactly. If the database uses a regional codepage as its default, but some columns contain Unicode data, intermediate passes need to identify those columns with the tag "CHARACTER SET UNICODE." Prior to the upgrade, the tag was applied to all Char or VarChar columns.
 
create volatile table ZZMD01, no fallback, no log(
   QUARTER_ID SMALLINT,
   QUARTER_DESC0 VARCHAR(255) character set unicode,
   COUNTRY_ID SMALLINT,
   CUST_COUNTRY_DESC0 VARCHAR(100) character set unicode,
   WJXBFS1 FLOAT)
primary index (QUARTER_ID, QUARTER_DESC0, COUNTRY_ID, CUST_COUNTRY_DESC0) on commit preserve rows
;insert into ZZMD01
select a13.QUARTER_ID QUARTER_ID,
   max(a15.QUARTER_DESC) QUARTER_DESC0,
   a12.COUNTRY_ID COUNTRY_ID,
   max(a14.COUNTRY_NAME) CUST_COUNTRY_DESC0,
   sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from DAY_CTR_SLS a11
   join LU_CALL_CTR a12
      on (a11.CALL_CTR_ID = a12.CALL_CTR_ID)
   join LU_DAY a13
      on (a11.DAY_DATE = a13.DAY_DATE)
   join LU_COUNTRY a14
      on (a12.COUNTRY_ID = a14.COUNTRY_ID)
   join LU_QUARTER a15
      on (a13.QUARTER_ID = a15.QUARTER_ID)
where (a14.COUNTRY_NAME_SCH = '897F73ED7259'XC
   and a15.QUARTER_DESC like '005100340025'XCV)
group by a13.QUARTER_ID,
   a12.COUNTRY_ID
After updating the tables, however, Char and VarChar columns retain the "CHARACTER SET UNICODE" modifier, but this specification is missing from columns that actually contain Unicode data. Report results may therefore be incorrect.
 
create volatile table ZZMD01, no fallback, no log(
   QUARTER_ID SMALLINT,
   QUARTER_DESC0 VARCHAR(255) character set unicode,
   COUNTRY_ID SMALLINT,
   COUNTRY_NAME0 VARCHAR(50), -- should also be character set unicode, but is not
   WJXBFS1 FLOAT)
primary index (QUARTER_ID, QUARTER_DESC0, COUNTRY_ID, COUNTRY_NAME0) on commit preserve rows
;insert into ZZMD01
select a13.QUARTER_ID QUARTER_ID,
   max(a15.QUARTER_DESC) QUARTER_DESC0,
   a12.COUNTRY_ID COUNTRY_ID,
   max(a14.COUNTRY_NAME) COUNTRY_NAME0,
   sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from DAY_CTR_SLS a11
   join LU_CALL_CTR a12
      on (a11.CALL_CTR_ID = a12.CALL_CTR_ID)
   join LU_DAY a13
      on (a11.DAY_DATE = a13.DAY_DATE)
   join LU_COUNTRY a14
      on (a12.COUNTRY_ID = a14.COUNTRY_ID)
   join LU_QUARTER a15
      on (a13.QUARTER_ID = a15.QUARTER_ID)
where (a14.COUNTRY_NAME_SCH = '西班牙'
   and a15.QUARTER_DESC like '005100340025'XCV)
group by a13.QUARTER_ID,
   a12.COUNTRY_ID
CAUSE
This behavior occurs because the second option for the Hexadecimal Character Transformation VLDB Property, using hexadecimal codes for all quoted strings, remains in Strategy SQL Generation Engine 9.0 for backward compatibility with projects that require Unicode support against Teradata but which do not distinguish between Unicode and non-Unicode columns in the Strategy metadata.
 
When the Strategy SQL Generation Engine writes SQL for an attribute form, fact or metric, it looks to an object in the Strategy metadata called a "column alias" to discover the data type expected in the database. A column alias option exists in the editors for attribute forms, facts and metrics.
 
The second Hexadecimal Character Transformation option looks for column aliases whose type is specifically Char or VarChar. When this option was implemented, NChar and NVarChar data types were not identified separately. The physical data types in the warehouse were mapped onto Strategy column aliases with Char or VarChar types, respectively; thus Strategy SQL Generation Engine 8.x has no way to know if a physical column in the warehouse is Char or NChar.
 
The SQL generation logic for the second option remains exactly the same in Strategy 9.0 as in previous versions. Therefore, it does not recognize NChar and NVarChar as data types where Unicode data are expected.
 
ACTION
For Teradata warehouses with Unicode columns, one of the following paths may be chosen:
 

• Projects whose table structures have not been updated in the warehouse catalog may continue to use the second Hexadecimal Character Transformation option. Provided that the warehouse table representations in the Strategy metadata do not include NChar or NVarChar column aliases (regardless of the physical column types in the database) will continue to generate SQL matching that of Strategy 8.x.
• Projects whose table structures have been updated will not generate correct SQL using the second option. In this case, Hexadecimal Character Transformation should be disabled entirely (first option). Strategy will then include the "CHARACTER SET UNICODE" modifier for NChar and NVarChar columns only, and embed the Unicode characters directly into the SQL without using hexadecimal codes.

After updating table structures and disabling Hexadecimal Character Transformation, the same intermediate pass will generate the following SQL, which will execute correctly against Teradata.
 
create volatile table ZZMD01, no fallback, no log(
   QUARTER_ID SMALLINT,
   QUARTER_DESC0 VARCHAR(255),
   COUNTRY_ID SMALLINT,
   COUNTRY_NAME0 VARCHAR(50) CHARACTER SET UNICODE,
   WJXBFS1 FLOAT)
primary index (QUARTER_ID, QUARTER_DESC0, COUNTRY_ID, COUNTRY_NAME0) on commit preserve rows
;insert into ZZMD01
select a13.QUARTER_ID QUARTER_ID,
   max(a15.QUARTER_DESC) QUARTER_DESC0,
   a12.COUNTRY_ID COUNTRY_ID,
   max(a14.COUNTRY_NAME) COUNTRY_NAME0,
   sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from DAY_CTR_SLS a11
   join LU_CALL_CTR a12
      on (a11.CALL_CTR_ID = a12.CALL_CTR_ID)
   join LU_DAY a13
      on (a11.DAY_DATE = a13.DAY_DATE)
   join LU_COUNTRY a14
      on (a12.COUNTRY_ID = a14.COUNTRY_ID)
   join LU_QUARTER a15
      on (a13.QUARTER_ID = a15.QUARTER_ID)
where (a14.COUNTRY_NAME_SCH = '西班牙'
   and a15.QUARTER_DESC like 'Q4%')
group by a13.QUARTER_ID,
   a12.COUNTRY_ID
If the database's default character set is Unicode, but some columns use a non-Unicode character set, it may be preferred to generate explicit "create table" statements without the CHARACTER SET UNICODE suffix for Unicode columns, instead specifying the localized character set for the specific columns that require it. A pair of VLDB Properties control the syntax: Character Column Option (which appends a descriptor to Char/VarChar columns) and National Character Column Option (which does the same for NChar/NVarChar columns). The default for Teradata is:
 

• Character Column Option = (empty)
• National Character Column Option = CHARACTER SET UNICODE

If the database uses Unicode by default, the properties could be reversed to specify the character set for columns in the Strategy metadata with the datatype Char or VarChar, and retain the default for NChar/NVarChar column aliases.
 

• Character Column Option = CHARACTER SET Latin1252_0A (or whichever other character set is required)
• National Character Column Option = (empty)

These settings are available in the VLDB Property editor for the Database Instance, under the Tables folder (in the same location as the Hexadecimal Character Transformation VLDB property).
 

 
Note: To report successfully against Unicode data in Teradata, the data source name (DSN) must be configured to use UTF8 as the session character set.
 

 
Further reading
Consult the following Strategy Knowledgebase article for more information on the support of national character data types in Strategy SQL Generation Engine 9.x.
 
KB20746 (KB5200-9X-0009): New feature in Strategy SQL Generation Engine 9.0.0: Support for National Character data types