Performance issue in SSAS tabular

  • jason.millar

    Grasshopper

    Points: 16

    Hi all;
    I've created an SSAS tabular model based off of sales facts recorded at the invoice level, and have folded in budget data recorded at the month level.  Because of the difference in granularity, I can't define physical relationships between the sales and budget facts, so I've created virtual realtionships instead using TREATAS (using a design pattern similar to https://exceleratorbi.com.au/virtual-filters-using-treatas/).  The problem I'm experiencing is that when working with the model in Excel and working with budget data, once you add more than 5 or six dimensions as row elements, the model slows to a crawl (in some cases, driving the CPU on the development host server to 100% for 10 minutes or more).  Similar behaviour is seen in Power BI when using a crosstab.  The measure for budget sales in listed below.  The requirement is to be able to show budget totals aggregated to pretty much any of the sales dimensions, hence the number of arguments to the TREATAS.  When performing the same summarizes for sales data, where the physical relationships do exist, performance is pretty much instant.
    When question, then, is whether anyone has any suggestions (either in terms of a different way to build the measure, or a different design pattern for the budget/actual data) that would help alleviate this problem?
    Thanks,
    Jason
    Forecast Sales (TREATAS) := CALCULATE(SUM(FactForecast[Revenue]),
     TREATAS(VALUES('Site'[Site Code]),FactForecast[SiteCode]),
     TREATAS(VALUES('Billing Date'[ForecastStamp]),FactForecast[ForecastStamp]),
     TREATAS(VALUES('Customer'[CustomerId]),FactForecast[CustomerId]),
     TREATAS(VALUES('Account Rep'[AccountRepId]),FactForecast[AccountRepId]),
     TREATAS(VALUES('Order Category'[OrderCategoryId]),FactForecast[OrderCategoryId]),
     TREATAS(VALUES('Grade'[GradeId]),FactForecast[GradeId]),
     TREATAS(VALUES('Grade Family'[GradeFamilyId]),FactForecast[GradeFamilyId]),
     TREATAS(VALUES('Caliper'[CaliperId]),FactForecast[CaliperId]),
     TREATAS(VALUES('Site Customer'[SCDSKSiteCustomer]),FactForecast[SCDSKSiteCustomer]),
     TREATAS(VALUES('Site Product'[SCDSKSiteProduct]),FactForecast[SCDSKSiteProduct]),
     TREATAS(VALUES('Type'[type_code]),FactForecast[type_code])
    )
  • Brian Carlson

    SSCarpal Tunnel

    Points: 4063

    What I usually do for different grains on date is to make two tables internally: DimDate, DimMonth for example. Date has a direct relationship to month, many to one. Tie the budget to DimMonth and the other facts to DimDate. Then you can create calculated columns in DimDate for the DimMonth columns and hide DimMonth.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply