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])
)