KB45673: Support of Google BigQuery with MicroStrategy Analytics Enterprise 9.4.1

• Strategy

Google Big Query is supported as a warehouse with MicroStrategy Analytics Enterprise 9.4.1. This support status applies to MicroStrategy Intelligence Server 9.4.1 running on Linux - it does not apply to MicroStrategy Intelligence Server 9.4.1 running on AIX, Solaris or HP-UX.

Google Big Query is supported as a warehouse with Strategy Analytics Enterprise 9.4.1. This support status applies to Strategy Intelligence Server 9.4.1 running on Linux - it does not apply to Strategy Intelligence Server 9.4.1 running on AIX, Solaris or HP-UX.

Note:
Google Big Query is supported for Windows out of the box. Please refer to the Strategy Analytics 9.4.1 Readme and Release notes for details.

This is a post-support status since the testing was performed after the release of Strategy Analytics Enterprise 9.4.1. Therefore, this support information (tabulated below) is not present in the General Information Readme files in Strategy Analytics Enterprise 9.4.1.

The following is the support information Intelligence Server on Linux for Strategy 9.4.1:

Pre/Post Statements Report Pre Statement 1 Diagnostic nosldrvstats on for sessionStrategy
FunctionCalculated in Amazon RedshiftTotalBasic1121Data Mining030Date and Time2331Financial050Mathematical3334OLAP2039Other416Pattern03Statistical851String1325Total112300FunctionSQL PatternUnionFunction#<(#>#1#<#0 union #*#>#<)#>ExceptFunction#<(#>#1#<#0 except #*#>#<)#>StdevPFunctionSTDDEV_POP(#0#< #*#>)LikeOperator#0 LIKE '#1'ProductFunction(CASE WHEN SUM(CASE WHEN #0=0 THEN 1 ELSE 0 END) > 0 THEN 0 ELSE POW(-1,SUM(CASE WHEN #0<0 THEN 1 ELSE 0 END))*EXP((SUM(LN(ABS(CASE WHEN #0<>0 THEN #0::float8 ELSE NULL END))))) END)StdevFunctionSTDDEV_SAMP(#0#< #*#>)VarFunctionVAR_SAMP(#0#< #*#>)GeoMeanFunctionEXP(AVG(LN(#0)))NotLikeOperator#0 NOT LIKE '#1'IFOperator(CASE WHEN #0 THEN #1 ELSE #2 END)VarPFunctionVAR_POP(#0#< #*#>)FunctionSQL PatternDaysBetweenFunctionDATEDIFF(DAY, #0, #1) YearEndDateFunctionADD_MONTHS(TRUNC(#0) - EXTRACT(DAY FROM #0), 13 - EXTRACT(MONTH FROM #0))CurrentDateFunctionCURRENT_DATEDayOfMonthFunctionEXTRACT(DAY FROM #0)DayOfWeekFunction(DATE_PART('dow',#0)+1)DayOfYearFunctionDATE_PART('doy',#0)WeekFunctionDATE_PART('week',#0)MonthFunctionEXTRACT(MONTH FROM #0)QuarterFunctionEXTRACT(QUARTER FROM #0)YearFunctionEXTRACT(YEAR FROM #0)CurrentDateTimeFunctionSYSDATECurrentTimeFunctionSYSDATEHourFunctionEXTRACT(HOUR FROM #0)MinuteFunctionEXTRACT(MINUTE FROM #0)SecondFunctionEXTRACT(SECOND FROM #0)MilliSecondFunctionEXTRACT(MILLISECOND FROM #0)MonthStartDateFunctiondateadd(day, -1*(extract(day from #0) -1),#0)YearStartDateFunctionADD_MONTHS(TRUNC(#0) - EXTRACT(DAY FROM #0) + 1, 1 - EXTRACT(MONTH FROM #0))AddMonthsFunctionADD_MONTHS(#0, #1)MonthsBetweenFunctionFLOOR(MONTHS_BETWEEN(#1,#0))MonthEndDateFunctiondateadd(day, -1*extract(day from add_months(#0, 1)), add_months(#0, 1))DateFunctionCAST(#0 AS DATE)FunctionSQL PatternRunningSumFunctionsum(#0) over(#1)RunningAvgFunctionavg(#0) over(#1)MovingAvgFunctionavg(#0) over(#1)MovingSumFunctionsum(#0) over(#1)MovingMaxFunctionmax(#0) over(#1)MovingMinFunctionmin(#0) over(#1) MovingCountFunctioncount(#0) over(#1)RunningMaxFunctionmax(#0) over(#1)RunningMinFunctionmin(#0) over(#1)RunningCountFunctioncount(#0) over(#1)FirstInRangeFunctionfirst_value(#0) over(#1)LastInRangeFunctionlast_value(#0) over(#1)OLAPSumFunctionsum(#0) over ([#P] [#O] [#W])OLAPAvgFunctionavg(#0) over ([#P] [#O] [#W])OLAPCountFunctioncount(#0) over ([#P] [#O] [#W])OLAPMaxFunctionmax(#0) over ([#P] [#O] [#W])OLAPMinFunctionmin(#0) over ([#P] [#O] [#W]) LagFunctionCASE WHEN count(*) OVER ([#P] [#O] rows between unbounded preceding and current row) <= #1 THEN #2 ELSE max(#0#< #*#>) OVER ([#P] [#O] rows between #1 preceding and #1 preceding) END   LeadFunctionCASE WHEN count(*) OVER ([#P] [#O] rows between current row and unbounded following) <= #1 THEN #2 ELSE max(#0#< #*#>) OVER ([#P] [#O] rows between #1 following and #1 following) END OLAPRankFunctionrank() over ([#P] [#O]) SQL Syntax for Ordered Analytic Functions:
Moving average example:
select a11.DAY_DATE DAY_DATE,
      a12.REGION_ID REGION_ID,
      max(a13.REGION_NAME) REGION_NAME,
      sum(a11.TOT_DOLLAR_SALES) WJXBFS1,
      avg(sum(a11.TOT_DOLLAR_SALES)) over(partition by a12.REGION_ID order by sum(a11.TOT_DOLLAR_SALES) asc rows 2 preceding) WJXBFS1
from DAY_CTR_SLS a11
      join LU_CALL_CTR a12
      on (a11.CALL_CTR_ID = a12.CALL_CTR_ID)
      join LU_REGION a13
      on (a12.REGION_ID = a13.REGION_ID)
group by a11.DAY_DATE,
      a12.REGION_ID

Ranking: The RANK function is closely related to other ordered analytical functions. Key distinctions of the RANK function are that it requires an order by clause and does not support a window clause. The Strategy Engine is aware that Amazon Redshift supports the Rank function, so the ranking will be done by the database rather than the Strategy Analytical Engine.
Rank example:
select a13.REGION_ID REGION_ID,
      max(a14.REGION_NAME) REGION_NAME,
      a11.EMP_ID EMP_ID,
      max(a12.EMP_LAST_NAME) EMP_LAST_NAME,
      max(a12.EMP_FIRST_NAME) EMP_FIRST_NAME,
      sum(a11.TOT_DOLLAR_SALES) WJXBFS1,
      rank () over( order by sum(a11.TOT_DOLLAR_SALES) desc nulls last) WJXBFS1
from ITEM_EMP_SLS a11
      join LU_EMPLOYEE a12
      on (a11.EMP_ID = a12.EMP_ID)
      join LU_CALL_CTR a13
      on (a12.CALL_CTR_ID = a13.CALL_CTR_ID)
      join LU_REGION a14
      on (a13.REGION_ID = a14.REGION_ID)
group by a13.REGION_ID,
      a11.EMP_ID
  FunctionSQL PatternRound2FunctionROUND(#0, CAST(#1 AS INTEGER))RankFunction#0#,#<partition by#>#<#, #*#>#|rank () over(#1#2#<, #*#> order by #0)AbsFunctionABS(#0)IntersectFunction#<(#>#1#<#0 intersect #*#>#<)#>AcosFunctionACOS(#0) AcoshFunctionLN(#0+SQRT(#0-1)*SQRT(#0+1))AsinFunctionASIN(#0)AtanFunctionATAN(#0)Atan2FunctionATAN2(#0, #1)AtanhFunction((LN(1+#0)-LN(1- #0))/2)CeilingFunctionCEIL(#0)CosFunctionCOS(#0) CoshFunction((EXP(#0::float8)+EXP((#0*(-1))::float8))/2)DegreesFunctionDEGREES(#0) ExpFunctionEXP(#0::float8)FloorFunctionFLOOR(#0)IntFunctionFLOOR(#0)LnFunctionLN(#0)LogFunctionLOG(#0)/LOG(#1) Log10FunctionLOG(#0) PowerFunctionPOW(#0, #1) QuotientFunctionTRUNC((#0)/NULLIF(#1, 0)) RadiansFunctionRADIANS(#0)RandbetweenFunction((#1- #0)*RANDOM()+#0)RoundFunctionROUND(#0) SinFunctionSIN(#0)SinhFunction((EXP(#0::float8) - EXP((#0*(-1))::float8))/2)SqrtFunctionSQRT(#0)TanFunctionTAN(#0)TanhFunction((EXP(#0::float8)-EXP((#0*(-1))::float8))/(EXP(#0::float8)+EXP((#0*(-1))::float8)))TruncFunctionTRUNC(#0)AsinhFunctionLN(#0+SQRT(POWER(#0,2)+1))ModFunction(#0-FLOOR((#0)/NULLIF(#1, 0))*(#1))FunctionSQL PatternKurtosisFunction((((SUM(#0- #0+1)*(SUM(#0- #0+1)+1))/((SUM(#0- #0+1)-1)*(SUM(#0- #0+1)-2)*(SUM(#0- #0+1)-3)))*((SUM(POW(#0,4)))-(4*SUM(POW(#0,3))*AVG(#0))+(6*SUM(POW(#0,2))*POW(AVG(#0),2))-(4*SUM(#0)*POW(AVG(#0),3))+FisherFunction(LN((1+#0)/(1- #0))/2)InverseFisherFunction((EXP((2*#0)-1)::float8)/(EXP((2*#0)::float8)+1)) StandardizeFunctioncase when (#2 > 0) then (#0 - #1)/(#2) else NULL end  CovarianceFunction((1/SUM(#0- #0+#1- #1+1))*(SUM(#0*#1)-(AVG(#0)*SUM(#1))-(SUM(#0)*AVG(#1))+(AVG(#0)*AVG(#1)*SUM(#0- #0+#1- #1+1))))      RSquareFunctionPOW((((SUM(#0- #0+#1- #1+1)*SUM(#0*#1))-(SUM(#0)*SUM(#1)))/SQRT(((SUM(#0- #0+#1- #1+1)*SUM(#0*#0))-(SUM(#0)*SUM(#0)))*((SUM(#0- #0+#1- #1+1)*SUM(#1*#1))-(SUM(#1)*SUM(#1))))),2)SlopeFunction(((SUM(#1- #1+#0- #0+1)*SUM(#1*#0))-(SUM(#1)*SUM(#0)))/((SUM(#1- #1+#0- #0+1)*SUM(#1*#1))-(SUM(#1)*SUM(#1)))) InterceptFunction(AVG(#0)-((((SUM(#1- #1+#0- #0+1)*SUM(#1*#0))-(SUM(#1)*SUM(#0)))/((SUM(#1- #1+#0- #0+1)*SUM(#1*#1))-(SUM(#1)*SUM(#1))))*AVG(#1))) FunctionSQL PatternConcatFunction(#0#< || #*#>)LengthFunctionLENGTH(#0)  LowerFunctionLOWER(#0)LTrimFunctionLTRIM(#0)PositionFunctionPOSITION(#0 IN #1)   RTrimFunctionRTRIM(#0) SubStrFunctionSUBSTRING(#0, #1, #2)InitCapFunctionINITCAP(#0)TrimFunctionTRIM(BOTH ' ' FROM #0)RightStrFunctionSUBSTRING(#0, (LENGTH(#0) - #1) + 1)LeftStrFunctionSUBSTRING(#0, 1, #1)UpperFunctionUPPER(#0)ConcatBlankFunction(#0#< || ' ' || #*#>)Google Big Query Simba BigQuery ODBC Driver 1.1.x Supported

In order to use Google Big Query as a warehouse with Strategy Intelligence Server 9.4.1, users must use the 'Google BigQuery' object in the 'Database Connection Type' window (located at Configuration Managers > Database Instance Manager > Database Instance) in Strategy Developer 9.4.1.

In order to configure connectivity to Google Big Query using the Simba BigQuery ODBC Driver 1.1.x please refer to the following Strategy knowledge base technical note:

KB45674: How to configure the Simba BigQuery ODBC driver on Linux for connectivity to a Google BigQuery database.

Limitations:
Google Big Query is currently only supported for Freeform SQL, QueryBuilder, and Data Import operations

Third Party Software Installation WARNING
The third-party product(s) discussed in this technical note is manufactured by vendors independent of Strategy. Strategy makes no warranty, express, implied or otherwise, regarding this product, including its performance or reliability.

Comment

0 comments

Details

Knowledge Article

Published:

April 28, 2017

Last Updated:

April 28, 2017