KB255429: Column Data type changes from Float to Date in Local Temp table in MicroStrategy 9.x and 10.0

SUMMARY
This issue has been classified as a defect by Strategy. The impact is limited to Strategy 9.x and 10.0. A couple of workarounds are available in the current document. A code fix for this issue is available in Strategy 10.1.
SYMPTOM
In Strategy 9.x and 10.0, Float data type is used for a metric column on the initial execution of the report as shown below:

CREATE LOCAL TEMPORARY TABLE #ZZMD00(
                        DAT_CODE_DATE    DATE, 
                       WJXBFS1       FLOAT) NO LOGGING

After runing a different report which contains a metric with expression defined using "CurrentDate()", then running the original report, the SQL generation is impacted. It is seen the Float data type is changed to Date/DateTime when creating the Local Temp table as shown below:

CREATE LOCAL TEMPORARY TABLE #ZZMD00(
            DAT_CODE_DATE    DATE, 
            WJXBFS1       DATE) NO LOGGING

 
Notes

• This issue is exposed only in 3-Tier.
• The SQL is correct in 2-tier project.
• This issue is independent of caching i.e., even when the report does not hit cache, this issue is seen.
• Only restarting the Strategy Intelligence Server corrects the behavior.

STEPS TO REPRODUCE
The following steps are based on the Strategy Tutorial project:

1. Create a metric called M1 with expression "CurrentDate()".
2. Create report called R1 with attribute Year and metric M1 on template.
3. Save and execute the report.
4. Create another report called R2, put attribute Year and Quarter in template.
5. Add report filter: Set of Year Rank Cost top 1.
6. In VLDB setting, set Tables --> Table creation type to "Explicit table".
7. View SQL of R2, it is noticed "DATETIME" is used in temp table:
   

   
   create table ZZAM00 (
   YEAR_ID SMALLINT,
   WJXBFS1 DATETIME)

   
   

CAUSE
This is a known issue with Strategy 9.x and 10.0.0.
ACTION
The issue has been fixed in Strategy 10.1 Secure Enterprise Platform. Upgrade to this version to take advantage of the fix.
The Strategy Internal Reference Number for the issue discussed in this technical note is US21638.
 
WORKAROUND
Modify metric to use “Date(CurrentDateTime()))” or “Max(ApplySimple("SYSDATE", 1)){~+}” function instead of using “CurrentDate()”