ROLAP - SUBCUBE bad sql queries


  • If subcube has filter condition with key attributes of a dimension, it fires sql query with all of it high cardinality attributes in group by clause of sql and low cardinality in where clause of sql.

    Example: PRODUCT is dimensions with UPC as key attributes. All attributes of Product dimension are related to UPC.

    select {[Total Sales Volume Units]} on 0 , non empty [STORE].[STORE].members on 1


    (SELECT filter([PRODUCT].[UPC].[UPC], [Measures].[Total Sales Volume Units] > 1000) on 0

    from [PEPSI_FLION]


    This results in ROLAP SQL query like below

    SELECT   SUM (..),.., UPC, <all high cardinality>


    WHERE <low cardinality> OR/AND conditions

    GROUP by …., UPC, <all high cardinality>

    This can be solved by setting this un-documented property DisableStrongAttributeRelationships to 1. However when this property set to 1, Semi-additive measures stop working if MDX has sub-cube - it result is "Unexpected Error"


    Monday, September 23, 2013 4:52 AM