KB30119: VLDB Property settings to filter on date values in Microsoft Analysis Services data sources in MicroStrategy

• Strategy

This article notes settings to apply when dealing with date filters against Analysis Services

The correct MDX syntax to filter on dates against Microsoft Analysis Services 2005 depends on the definition of the Date attribute in the Microsoft SQL Server Business Intelligence Development Studio.

In Microsoft Analysis Services 2005, attribute element IDs appear in the format:

Strategy Grandmaster Badges
Receive a badge for being recognized as a Strategy Grandmaster. Former Strategy Grandmasters will also be recognized with an alumni badge.

Community Influencer Badges
Receive a badge for being recognized as a
Community Influencer. Former Community Influencers will also be recognized with an alumni badge.

DossBoss Badge
Receive a badge for winning our monthly data visualization challenge, DossBoss. Learn more about the competition here.
MethodDescriptionTypeBottomLevelA bottom level specifies the lowest analysis level that the security filter is applied.IDSSDimtyExpressionSecurity filter.IDSSExpressionInfoThe IDSSObjectInfo interface for the MD security filter.IDSSObjectInfoPopulate ( object as IDSSMDSecurityFilter)Populate this filter's value from an existing MD security filter. RootA shortcut to Expression.Root.IDSSNodeTopLevelA top level specifies the highest analysis level that the security filter is applied without
changing the dimensionality of metrics.
IDSSDimtyStrategy Function NameSQL PatternABSABS(#0)COSCOS(#0)EXPEXP(#0)LNLN(#0)LOG10LOG(#0)POWERPOWER(#0, #1)ROUNDROUND(#0,0)ROUND2ROUND(#0, #1)SINSIN(#0)SQRTSQRT(#0)MOD((#0) MOD (#1))STDEVSTDDEV_SAMP(#0#< #*#>)VARVAR_SAMP(#0#< #*#>)STDEVPSTDDEV_POP(#0#< #*#>)VARPVAR_POP(#0#< #*#>)SKEWSKEW(#0#< #*#>)KURTOSISKURTOSIS(#0#< #*#>)Strategy Function NameSQL PatternCURRENTDATECURRENT_DATEDAYOFMONTHEXTRACT(DAY FROM #0)DAYOFWEEKTO_NUMBER(TO_CHAR(#0, 'D'))DAYOFYEARTO_NUMBER(TO_CHAR(#0, 'DDD'))WEEKTO_NUMBER(TO_CHAR(#0, 'WW'))MONTHEXTRACT(MONTH FROM #0)QUARTERTO_NUMBER(TO_CHAR(#0, 'Q'))YEAREXTRACT(YEAR FROM #0)CURRENTDATETIMELOCALTIMESTAMPCURRENTTIMELOCALTIMESTAMPHOURTO_NUMBER(TO_CHAR(#0, 'HH24'))MINUTETO_NUMBER(TO_CHAR(#0, 'MI')SECONDTO_NUMBER(TO_CHAR(#0, 'SS'))DATETRUNC(CAST(#0 AS DATE))DAYSBETWEENCAST(#1 AS DATE) - CAST(#0 AS DATE)MONTHSBETWEENFLOOR(MONTHS_BETWEEN(#1, #0))ADDDAYS(#0 + #1 * INTERVAL '1' DAY)ADDMONTHSADD_MONTHS(#0, Cast(#1 as Integer))MONTHSTARTDATETRUNC(ADD_MONTHS((LAST_DAY(#0) + 1), -1))MONTHENDDATETRUNC(LAST_DAY(#0))YEARSTARTDATEADD_MONTHS(TRUNC(#0) - EXTRACT(DAY FROM #0) + 1, 1 - EXTRACT(MONTH FROM #0))YEARENDDATEADD_MONTHS(TRUNC(#0) - EXTRACT(DAY FROM #0), 13 - EXTRACT(MONTH FROM #0))Strategy Function NameColumn NameRUNNINGAVGavg(#0) over(#1)RUNNINGMAXmax(#0) over(#1)RUNNINGMINmin(#0) over(#1)RUNNINGCOUNTcount(#0) over(#1)RUNNINGSUMsum(#0) over(#1)MOVINGAVGavg(#0) over(#1)MOVINGMAXmax(#0) over(#1)MOVINGMINmin(#0) over(#1)MOVINGSUMsum(#0) over(#1)Rank#0#,#<partition by#>#<#, #*#>#|rank over(#1#2#<,#*#> order by #0)COVARIANCECOVAR_POP(#0, #1)CORRELATIONCORR(#0, #1)SLOPEREGR_SLOPE(#0, #1)INTERCEPTREGR_INTERCEPT(#0, #1)BANDINGCase when (#0 between #1 and #2) then
cast((1.0*(#0- #1)/#3) +1 AS INTEGER) else 0 endBANDINGCCase when (#1=#2 and #0=#1) then #3 when
(#0=#2) then #3 when (#0 between #1 and #2)
then CAST(1.0*(#0- #1)/(#2- #1)*#3+1 AS INTEGER)
else 0 endIF(Case when #0 then #1 else #2 end)RUNNINGSTDEVstddev_samp(#0) over(#1)RUNNINGSTDEVPstddev_pop(#0) over (#1)MOVINGSTDEVstddev_samp(#0) over(#1)MOVINGSTDEVPstddev_pop(#0) over(#1)OLAPSUMsum(#0#< #*#>) over ([#P] [#O] [#W])" OLAPDBPATTERN="P:o|O:o|W:o|D:n|R:nOLAPAVGavg(#0#< #*#>) over ([#P] [#O] [#W])" OLAPDBPATTERN="P:o|O:o|W:o|D:n|R:yOLAPCOUNTcount(#0#< #*#>) over ([#P] [#O] [#W])" OLAPDBPATTERN="P:o|O:o|W:o|D:n|N:y|R:yOLAPMAXmax(#0#< #*#>) over ([#P] [#O] [#W])" OLAPDBPATTERN="P:o|O:o|W:o|D:n|R:yOLAPMINmin(#0#< #*#>) over ([#P] [#O] [#W])" OLAPDBPATTERN="P:o|O:o|W:o|D:n|R:yLAGCASE WHEN count(*) OVER ([#P] [#O] rows between unbounded preceding and current row) <= #1 THEN #2 ELSE sum(#0#< #*#>) OVER ([#P][#O] rows between #1 preceding and #1 preceding) END" OLAPDBPATTERN="P:o|O:rLEADCASE WHEN count(*) OVER ([#P] [#O] rows between current row and unbounded following) <= #1 THEN #2 ELSE sum(#0#< #*#>) OVER ([#P] [#O] rows between #1 following and #1 following) END" OLAPDBPATTERN="P:o|O:rOLAPRANKrank() over ([#P] [#O])" OLAPDBPATTERN="P:o|O:rLAGlag(#0#<, #*#>) over ([#P][#O])" OLAPDBPATTERN="P:o|O:rLEADlead(#0#<, #*#>) over ([#P] [#O])" OLAPDBPATTERN="P:o|O:rOLAPRANKrank() over ([#P] [#O])" OLAPDBPATTERN="P:o|O:rMEDIANMEDIAN(#0)FIRST IN RANGEfirst_value(#0) over(#1)LAST IN RANGElast_value(#0) over(#1)Strategy Function NameSQL PatternACOSACOS(#0)ASINASIN(#0)ATANATAN(#0)ATAN2ATAN2(#0,#1)COSHCOSH(#0)SINHSINH(#0)TANTAN(#0)TANHTANH(#0)ACOSHACOSH(#0)ASINHASINH(#0)ATANHATANH(#0)GEOMEANEXP(AVG(LN(#0)))PRODUCT(CASE WHEN SUM(CASE WHEN #0=0 THEN 1 ELSE 0 END) > 0 THEN 0 ELSE (POWER(-1,SUM(CASE WHEN #0<0 THEN 1 END)))*EXP(SUM(LN(ABS(CASE WHEN #0<>0 THEN #0 ELSE NULL END)))) END) CEILINGCEILING(#0)DEGRESS(POWER(ASIN(1),(-1))*#0*90)FLOORFLOOR(#0)RADIANS(ASIN(1)*#0/90)TRUNCTRUNC(#0)Strategy Function NameSQL PatternINTCASE WHEN #0 > 0 THEN TRUNC(#0, 0) ELSE (TRUNC(#0, 0) - 1) ENDINT2(CASE WHEN (#0-CAST(#0 as integer)) >= 0 THEN CAST(#0 as integer) ELSE (CAST(#0 as integer) - 1) END)LOG(LN(#0)/LN(#1))QUOTIENTTRUNC((#0)/(CASE WHEN (#1)=0 THEN NULL ELSE(#1) END), 0)MOVINGCOUNTSUM(#0- #0+1) OVER(#1)MOVINGSTDEV(CASE WHEN SUM(#0 - #0 +1) OVER(#1)=1 THEN 0 ELSE SQRT((SUM(#0*#0) OVER(#1)-((SUM(#0) OVER(#1))/(SUM(#0-OVER(#1)*SUM(#0) OVER(#1))))/(SUM(#0- #0+1) OVER(#1)-1)) END)FISHER(LN((1+#0)/(1- #0))/2)INVERSEFISHER((EXP(2*#0)-1)/(EXP(2*#0)+1))PEARSON(((SUM(#0-#0+#1-#1+1)*SUM(#0*#1))-(SUM(#0)*SUM(#1)))/SQRT(((SUM(#0-#1+1)*SUM(#0*#0))-(SUM(#0)*SUM(#0)))*((SUM(#0-#1+1)*SUM(#1*#1))-(SUM(#1)*SUM(#1)))))STANDARDIZEcase when (#2 > 0) then (#0 - #1)/(#2) else NULL endRSQUAREREGR_R2(#0,#1)STEYXSQRT((1/(SUM(#1- #1+#0- #0+1)*(SUM(#1- #1+#0- #0+1)-2)))*((SUM(#1- #1+#0- #0+1)*SUM(#0*#0))- (SUM(#0)*SUM(#0))-((((SUM(#1- #0+1)*SUM(#1*#0))-(SUM(#1)*SUM(#0)))*((SUM(#1-#0+1)*SUM(#1*#0))-(SUM(#1)*SUM(#0))))/((SUM(#1-#0+1)*SUM(#1*#1))-(SUM(#1)*SUM(#1))))))GREATESTGREATEST(#0#<, #*#>)LEASTLEAST(#0#<, #*#>)CountryMetricCanada23Australia10United States50Estonia12[Dimension].[Hierarchy].[Level].&[Key][Date].[Date].[Month].&[200501]

(The level may be omitted if the attribute is at the lowest level of the hierarchy.)

If a date attribute uses a datetime column in its source tables (typical in Microsoft SQL Server databases), attribute element IDs for date may be returned to Strategy in the following format:

[Date].[Date].&[2007-03-27T00:00:00]

Strategy Engine 8.1.x and 9.x generates this syntax when using an element list qualification, where dates are chosen from a list of elements retrieved from the data source. In an element list qualification, the condition includes data returned by the data source for the attribute's ID form; hence the MDX syntax is expected to match the MDX cube data.

To use dynamic dates or a prompt with the calendar date picker, the qualification must be defined against a different member property (attribute form). This reverts to a shortcut syntax, which defaults to the following:

[Date].[Date].[20090327]

Such reports will return no data, because there is no date member in the dimension with the given identifier.

In Microsoft Analysis Services 2005, attribute elements can be specified in either of two ways:

[Dimension].[Hierarchy].[Level].&[Key]
[Dimension].[Hierarchy].[Level].[MEMBER_NAME]

Therefore, there are two alternatives for date syntax:

Use the SQL Date Format VLDB property to format the date string to match the attribute's description (MEMBER_NAME property in the cube).
Or, format the date string to match the raw IDs from the data source, and use the Date Pattern VLDB property to include the & in the element that ID. (Note that the raw ID may not be the same as the MEMBER_KEY member property in the data source. A date element may have the ID [Date].[Date].&[2007-03-27T00:00:00] while the MEMBER_KEY is 3/27/2007.)

To generate the proper date format, perform the following steps.

In the Database Instance Manager, right-click on the Microsoft Analysis Services 2005 database instance and choose "VLDB properties."
In the Tools menu, enable the option to show advanced settings.
Expand the Select/Insert folder.
Modify the SQL Date Format property to match either the MEMBER_NAME or raw ID.

If the MEMBER_NAME is 03/05/2007, the SQL Date Format should be mm/dd/yyyy. Or, the MEMBER_NAME might be March 5, 2007, in which case the SQL Date Format would be mmmm d, yyyy.
For raw ID, in the above example, SQL Date Format should be yyyy-mm-dd\Thh:mm:ss. (The backslash is necessary as an escape character. Without it, T will be replaced with the time portion of the timestamp in localized format, which is not valid for MDX filtering.)

If the raw ID is used, also modify the Date Pattern property to have the value #0.&[#1]. There is no need to change this when using the MEMBER_NAME format.
Restart Strategy Intelligence Server (or, in a two-tier connection, disconnect and reconnect to the project source).

In this example, the raw ID is used. Both the SQL Date Format and Date Pattern VLDB Properties are modified.

MDX should then be generated with syntax such as the following, for a filter returning dates between March 27, 2007 and April 1, 2007.

with set [dim0_filter_comp0] as '{generate({[Date].[Date].&[2007-03-27T00:00:00]:[Date].[Date].&[2007-04-01T00:00:00], [Date].[Date].&[2007-04-01T00:00:00]:[Date].[Date].&[2007-03-27T00:00:00]}, {[Date].[Date].CurrentMember})}'
set [dim0_filter_elements] as '{[dim0_filter_comp0]}'
select [dim0_filter_elements] dimension properties MEMBER_KEY on columns
from [Sales_GDP]
cell properties CELL_ORDINAL, FORMATTED_VALUE, VALUE, FORMAT_STRING

Note: Successful date filtering in MDX depends on proper configuration of the data types for the attribute's ID form and underlying column in the cube. Consult the following Strategy Knowledgebase document for further details.

KB14716: Considerations for using date attribute forms in MDXCube data sources in Strategy

Comment

0 comments

Details

Knowledge Article

Published:

May 31, 2017

Last Updated:

May 31, 2017