KB36692: The error "The Analytical Engine cannot process the data due to incompatible data type" is presented when trying to perform incremental refresh on a cube where some metrics only returned null

• Strategy

An error is thrown when trying to perform an incremental cube refresh.

SYMPTOM:

The error below is thrown when trying to perform an incremental cube refresh.

Sample Code/Error
The Analytical Engine cannot process the data due to incompatible data type

This only happens when either the incremental refresh report returns no data or one or more metrics only return nulls for the dataset returned by the incremental refresh report.

An example would be a refresh report containing the following data.

Explicit
create table ZZEA00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11create table ZZAM00 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   rank () over( order by (a11.TOT_DOLLAR_SALES - a11.TOT_COST) desc nulls last) WJXBFS1
from   SUBCAT_SLS   a11 create table ZZAM01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZEA00   pa11

create table ZZEA02 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID,
   pa11.WJXBFS1 WJXBFS1
from   ZZAM01   pa11 create table ZZMQ03 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZEA02   pa11
where   (pa11.WJXBFS1 <= 5.0)

create table ZZEA04 nologging as
select   a11.SUBCAT_ID SUBCAT_ID,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11,
   ZZMQ03   pa12
where   a11.SUBCAT_ID = pa12.SUBCAT_ID

select   pa11.SUBCAT_ID SUBCAT_ID,
   a12.SUBCAT_DESC SUBCAT_DESC,
   pa11.WJXBFS1 WJXBFS1,
   pa11.WJXBFS2 WJXBFS2
from   ZZEA04   pa11,
   LU_SUBCATEG   a12
where   pa11.SUBCAT_ID = a12.SUBCAT_IDcreate table ZZMQ01 nologging as
select   pa11.SUBCAT_ID SUBCAT_ID
from   ZZAM00   pa11
where   (pa11.WJXBFS1 <= 5.0)

select   a11.SUBCAT_ID SUBCAT_ID,
   a13.SUBCAT_DESC SUBCAT_DESC,
   a11.TOT_DOLLAR_SALES WJXBFS1,
   (a11.TOT_DOLLAR_SALES - a11.TOT_COST) WJXBFS2
from   SUBCAT_SLS   a11,
   ZZMQ01   pa12,
   LU_SUBCATEG   a13
where   a11.SUBCAT_ID = pa12.SUBCAT_ID and
   a11.SUBCAT_ID = a13.SUBCAT_ID Function TypeBuilt-inUser-DefinedExampleSELECT   base64(dt_binary)
FROM   wh2.dt_fact_table1;CREATE TEMPORARY FUNCTION c_to_string AS 'org.apache.hadoop.hive.ql.udf.UDFToString';

SELECT   c_to_string(dt_binary)
FROM   wh2.dt_fact_table1;FunctionSQL PatternAdd(#0#< + #*#>)  Average(#0#< + #*#>) / (((0 * #0) + 1)#< + ((0 * #*) + 1)#>)  AvgAVG(#0#<#*#>) CountCOUNT(#0#<#*#>) GeoMeanFunctionEXP(AVG(LN(#0))) GreatestFunctionGREATEST(#0#<, #*#>) LeastFunctionLEAST(#0#<, #*#>) MaxMAX(#0#<#*#>) MinMIN(#0#<#*#>) Multiply(#0#< * #*#>)  StdevFunctionSTDDEV(#0#< #*#>) SumSUM(#0#<#*#>) VarFunctionVAR(#0#< #*#>) FunctionSQL PatternConcatFunctionCONCAT(#0, #1) LengthFunctionLENGTH(#0)LowerFunctionLOWER(#0) LTrimFunctionLTRIM(#0) PositionFunctionLOCATE(#1, #0) RTrimFunctionRTRIM(#0) SubStrFunctionSUBSTRING(#0, #1, #2) InitCapFunction(UPPER(SUBSTRING(#0, 1, 1)) || LOWER(SUBSTRING(#0, 2, Length(#0) - 1))) TrimFunctionLTRIM(RTRIM(#0)) RightStrFunctionRIGHT(#0, #1) LeftStrFunctionLEFT(#0, #1) UpperFunctionUPPER(#0) ConcatBlankFunction(#0#< || ' ' || #*#>) FunctionSQL PatternDaysBetweenFunctionDAYS_BETWEEN(#0, #1)YearEndDateFunctionADD_DAYS(ADD_MONTHS (#0, (13 - EXTRACT(MONTH FROM #0))),- EXTRACT(DAY FROM #0))CurrentDateFunctionNOW()DayOfMonthFunctionDAYOFMONTH(#0)DayOfWeekFunction(CASE WHEN WEEKDAY(#0) =6 THEN WEEKDAY(#0)-5 ELSE WEEKDAY(#0)+2 END)DayOfYearFunctionDAYOFYEAR(#0)WeekFunctionTO_CHAR(#0,'WW')MonthFunctionMONTH(#0)QuarterFunctionTO_CHAR (#0, 'Q')YearFunctionYEAR(#0)CurrentDateTimeFunctionNOW()CurrentTimeFunctionNOW()HourFunctionHOUR(#0)MinuteFunctionMINUTE(#0)SecondFunctionROUND(SECOND(#0))MilliSecondFunctionsubstring(cast(extract(second from #0) as char(10)), 4, 3)MonthStartDateFunctionADD_DAYS(#0,1-DAYOFMONTH(#0))YearStartDateFunctionADD_MONTHS (ADD_DAYS (#0, 1 - (EXTRACT(DAY FROM #0))), 1 -(EXTRACT(MONTH FROM #0)))AddMonthsFunctionADD_MONTHS(#0, #1)MonthsBetweenFunction(12 * (EXTRACT(YEAR FROM #1) - EXTRACT(YEAR FROM #0)) + EXTRACT(MONTH FROM #1) - EXTRACT(MONTH FROM #0) - (CASE WHEN EXTRACT(DAY FROM #0) > EXTRACT (DAY FROM #1) THEN 1 ELSE 0 END))AddDaysFunctionADD_DAYS(#0, #1)MonthEndDateFunctionADD_DAYS((ADD_MONTHS(#0, 1)), -DAYOFMONTH((ADD_MONTHS(#0, 1))))DateFunctionTO_DATE(#0,'YYYY-MM-DD')FunctionSQL PatternLagFunctionlag(#0#<, #*#>) over ([#P] [#O]) LeadFunctionlead(#0#<, #*#>) over ([#P] [#O]) OLAPRankFunctionrank() over ([#P] [#O])FunctionSQL PatternRound2FunctionROUND(#0, #1)RankFunction#0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0)AbsFunctionABS(#0)IntersectFunction#<(#>#1#<#0 intersect #*#>#<)#>Int2FunctionFLOOR(#0)AcosFunctionACOS(#0)AcoshFunctionLN(#0+SQRT(#0-1)*SQRT(#0+1))AsinFunctionASIN(#0)AtanFunctionATAN(#0)Atan2FunctionATAN2(#1, #0)AtanhFunction((LN(1+#0)-LN(1- #0))/2)CeilingFunctionCEILING(#0)CosFunctionCOS(#0)CoshFunctionCOSH(#0)ExpFunctionEXP(#0)FloorFunctionFLOOR(#0)IntFunctionFLOOR(#0)LnFunctionLN(#0)LogFunctionLOG(#1, #0)Log10FunctionLOG(10,#0)PowerFunctionPOWER(#0, #1)QuotientFunction
(CASE WHEN SIGN(#0/NULLIF(#1, 0)) =1 THEN FLOOR (#0/NULLIF(#1,0)) ELSE CEILING (#0/NULLIF(#1,0)) END)RoundFunctionROUND(#0, 0)SinFunctionSIN(#0)SinhFunctionSINH(#0)SqrtFunctionSQRT(#0)TanFunctionTAN(#0)TanhFunctionTANH(#0)TruncFunctionCAST(#0 AS INTEGER)AsinhFunctionLN(#0+SQRT(POWER(#0,2)+1))ModFunctionMOD(#0, #1)ItemExampleApplication server versionTomcat 7.0, IIS 7.5, etc.Application server operating systemWindows 2008 R2Operating system bit type64-bitStrategy Web version9.4.1 Hotfix 9Machine physical memory8 GBNumber of CPUs and speedDual core @ 2.4 GhzJava version1.8.0 update 60Java max heap size2048 MOther Java parametersVerbose GC loggingAverage user concurrency on peak times100select count(distinct a11.REGION_ID) WJXBFS1
from LU_REGION a11 select count(distinct a13.REGION_ID) WJXBFS1
from LU_CALL_CTR a13
where a13.MANAGER_ID in (1)select distinct a11.REGION_ID REGION_ID,
a11.REGION_NAME REGION_NAME0
from LU_REGION a11select distinct a12.REGION_ID REGION_ID,
a13.REGION_NAME REGION_NAME0
from LU_CALL_CTR a12
join LU_REGION a13
      on (a12.REGION_ID = a13.REGION_ID)
where a12.MANAGER_ID in (1)2012 NULL

CAUSE:

This is a known issue with Strategy 9.x-10.x.

WORKAROUND:

To work around this ensure that all metrics will return a value for at least one row in incremental refresh reports.

Comment

0 comments

Details

Knowledge Article

Published:

June 5, 2017

Last Updated:

June 5, 2017