A strange behavior of AutoExist for MDX on Tabular models #powerpivot #ssas #tabular

Alberto Ferrari wrote an interesting article about a strange behavior of AutoExist in normalized data models. I always say that a star schema is the best practice in Power Pivot and Tabular data modeling. The issue described by Alberto is another good reason to avoid snowflake schemas.

I think that an example is better than many words. Consider this simple measure working in a star schema where all product attributes (such as Category and Subcategory) are in the same denormalized DimProduct table:

SalesOfBikes := CALCULATE ( [Sales], DimProduct[Category] = “Bikes” )

If you have a snowflake schema with DimProduct, DimProductSubcategory and DimProductCategory tables, you have to write a much longer and complex DAX formula in order to obtain the same result:

SalesOfBikes :=
CALCULATE (
    [Sales],
    DimProductCategory[EnglishProductCategoryName] = “Bikes”,
    CALCULATETABLE (
        DimProductCategory,
        FILTER (
            ALL ( DimProductSubcategory ),
            IF (
                ISFILTERED ( DimProductSubcategory[EnglishProductSubcategoryName] ),
                CONTAINS (
                    VALUES ( DimProductSubcategory ),
                    DimProductSubcategory[ProductSubcategoryKey], DimProductSubcategory[ProductSubcategoryKey]
                ),
                TRUE
            )
        )
    )
)

Which seems crazy, and actually it is…

The reasons are interesting and well described in the AutoExist and Normalization article on SQLBI.