KB32687: How to troubleshoot issues with SQL generation for attribute element browsing without warehouse tables in MicroStrategy SQL Generation Engine

• Strategy

Normally, the queries generated by Strategy SQL Generation Engine to browse attribute elements in the Data Explorer or in element lists for problems are very simple. When the element list is filtered, by a user's security filter or by a filter embedded in a prompt, the SQL can be more complex and thus require troubleshooting.

Whereas report SQL is easy to view for troubleshooting, element browsing SQL is generated and submitted immediately to the warehouse. A user can read the SQL in the Strategy database classes SQL trace log, but the log is inconvenient and requires lookup tables with dummy data to be created.

Instead, the same SQL can be simulated by a pair of reports.

Element browsing uses a pair of queries. The first counts the distinct elements, to support incremental fetching in the prompt or Data Explorer display. The second selects the attribute's ID and browsing forms.

Element count pass
To view element counting SQL, perform the following steps:

Create a metric with the formula Count<Distinct=True>(Attribute) { ~+ }.
Create a report with this metric, and no attributes, on the template.
If the browsing request is restricted by a filter in a prompt, include that filter object in the report filter. (If the issue is with a security filter, there is no need to add the security filter conditions to the report filter.)
Log in as the affected user and view SQL for this report.

Element fetching pass
To view element retrieval SQL, perform the following steps:

Create a report with only the attribute on the template.
If the browsing request is restricted by a filter in a prompt, include that filter object in the report filter. (If the issue is with a security filter, there is no need to add the conditions to the report filter.)
Log in as the affected user and view SQL for this report.

Security filters
It may not always be accurate to place security filter conditions into the report filter of the above reports. For efficiency, Strategy SQL Generation Engine can remove report filter qualifications that are unrelated to the template attribute(s) for reports that contain no metrics. (SQL passes that calculate metrics remove filters that are unrelated to the fact's entry level, which may include more attributes than those on the template.)

In some cases, security filter qualifications may be considered in element browsing when the same qualifications would be removed from the report filter. Therefore, to reproduce SQL generation issues with security filters, it is recommended to omit security filter qualifications from the above testing reports. Instead, log in as a user associated with the security filter so that the qualifications will apply as security filter qualifications, exactly as they would in production.

Example
To simulate Region in element browsing in the Strategy Tutorial project, the counting pass requires a metric defined as follows:

Note: <Distinct=True> is normally hidden, but can be displayed using the "Show Function Parameters" option in the View menu of the metric editor. The same parameter can be viewed by selecting the text "Count" and choosing "Count parameters" from the right-click menu.

The report is constructed as follows:

The following SQL is generated:

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)

The element fetching pass requires no additional objects to be created. The report definition is as follows:

The following SQL is generated:

Comment

0 comments

Details

Knowledge Article

Published:

May 22, 2017

Last Updated:

May 22, 2017