Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am wondering something about data model design to improve performance.
I actually load data from multiple tables (financial data). The data is P&L lines for countries by month, business, scenario, year and other semgentations. Each table containing one scenario in one currency (EUR / Local Currency).
I load each table seperatly and then append all the queries into on big data table of 500k lines.
Now I'm computing some effects (Volume, price, mix range, mix countries, rate) and those calculations are quite complex because of exceptions. Those calculations are very slow and I'm wondering how I could improve this (Force to use sumx(summarize()) for many of the effects)
Would it be faster to keep each scenario in a different table instead of using one big data table ?
I'll put the example of the mix country formula. We have many exceptions to take into account which makes the calculation complex :
MixCtry_CA = SUMX(SUMMARIZE(DATA;COUNTRIES[_CTRY LVL_BRIDGE];BRAND[RANGE]);IF([HasMultiBus];[NetSales_A18_EUR]-[NetSales_SCN_EUR]-[Vol_CA]-[Price_CA]-[MixRange_CA]-[Rate_CA];SUMX(SUMMARIZE(DATA;BUSINESS[TOT BUS]);IF(OR([Units_A18]=0;[Units_SCN]=0);[NetSales_A18_EUR]-[NetSales_SCN_EUR]-[Vol_CA]-[Price_CA]-[MixRange_CA]-[Rate_CA];IFERROR(([Units_A18]-[Units_SCN])*(CALCULATE(DIVIDE([NetSales_A18_LC]/[Rate_SCN];[Units_A18];0);ALLEXCEPT(BRAND;BRAND[BRAND]))-[Price_AVG_A18]);0)))))
This calculation handles two exceptions : The first one is the multiple business cases on one of the scenario, note that the TOT BUS is not present on the first SUMMARIZE. The second exception is 0 units on one the scenario. The real effect calculation starts with IFERROR.
Thanks
hi,@Lichar
After my research, keep each scenario in a different table is faster than using one big data table.
It involves Vertipaq Engine and data storage mode
Reference:https://www.microsoftpressstore.com/articles/article.aspx?p=2449192
and some of the tips and techniques to improve the calculation
http://blog.pragmaticworks.com/power-bi-performance-tips-and-techniques
Best Regards,
Lin
Hi !
Thanks for your answer.
I change my data set so as to keep each scenario in a different table but didn't think of the measure computation before.
Indeed how can I use a SUMMARIZE on different tables ?
For example in each table I have multiple countries that can have 4 businesses. I use in the effects an "average price" which is computed per group of business (I have 2 groups, each one taking 2 businesses). Before I was using
SUMX(SUMMARIZE(DATA;BUSINESS[TOT BUS]);price_calculation
to get the desire result but now I am struggling to find how to achieve this.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |