Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jmillar
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
v-frfei-msft
Community Support
Community Support

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.