cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Performance issues against SSAS tabular model

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

1 REPLY 1
Highlighted
Community Support
Community Support

Re: Performance issues against SSAS tabular model

Hi @jmillar,

 

As the formula you shared, it is a bit complex. So I suggest you can directly execute the same query's as Power BI would against the tabular model and review the performance.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors