Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hey guys, can anyone please give me some tips on how to improve performance on my model?
I've monitored how much time the SQL takes to respond, and it never goes above 1 second using SQL Profiler. However, the matrix takes from 10 to even 45 seconds to expand a level or to load.
One thing that I didn't understand it's why every interaction with the matrix it sends like 15 sql query
Does anyone know what could be the issue?
Use DAXStudio to evaluate the performance of the query for that visual. You can grab both queries (DAX and DQ) from the Performance Analyzer and then paste them into DAX Studio. There you can see the actual query plan and the server timings between the storage and formula engines.
Look for the number of records (high number means potential issue with the data model) and for excessive formula engine timing.
Hey thanks for the reply.
I checked with dax studio. I've FE 527ms and SE 4453MS.
It sends almost 100 SQL query.
This is the most expensive one, almost 1 sec:
SELECT TOP (1000001) [t13].[Level1Description] AS [t13_Level1Description],
[t13].[Level1Order] AS [t13_Level1Order],
COUNT_BIG([t13].[Level2Code]) AS [a0],
MIN([t13].[Level2Code]) AS [a1],
MAX([t13].[Level2Code]) AS [a2],
COUNT_BIG(*) AS [a3]
FROM ([dim].[Reclassifications]) AS [t13]
WHERE (
(
[t13].[Level1Description] IN (
'Costi del personale',
'Accantonamenti e svalutazioni',
'CONTI NON CLASSSIFICATI',
'COSTI DELLA PRODUZIONE',
'Godimento beni di terzi',
'MARGINE OPERATIVO LORDO',
'Variazioni delle rimanenze prodotti finiti',
'Altri ricavi',
'Acquisti di servizi',
'Oneri e proventi finanziari',
'Piano conti Co.Ge.',
'Ricavi dalle vendite e prestazioni',
'RENDICONTO FINANZIARIO, METODO DIRETTO',
'RISULTATO CORRENTE',
'Acquisti di merci',
'RISULTATO NETTO',
'Ammortamenti',
'RISULTATO OPERATIVO',
'Budget',
'CASH FLOW DI PERIODO',
'RISULTATO PRIMA DELLE IMPOSTE',
'CONTI D''ORDINE',
'STATO PATRIMONIALE',
'CONTI NON CLASSIFICATI',
'VALORE AGGIUNTO',
'VALORE DELLA PRODUZIONE',
'CONTO ECONOMICO'
)
)
)
GROUP BY [t13].[Level1Description],
[t13].[Level1Order]
I don't know what to do
In your SQL Server slap an index on Level1Description and Level1Order
Show the measure(s) that feed(s) the values area of the matrix.
Ok I'm going to add indexes on Levels.
Those are the measures:
this one
Compared Value (matrix) =
VAR __SelectedComparedValue =
SELECTEDVALUE ( 'Selection Compared Value'[Selection Code] )
RETURN
IF (
ISBLANK ( 'dim Reclassifications'[Reclassification Hierachy Selected Level] ),
BLANK (),
IF (
HASONEVALUE ( 'Selection Compared Value'[Selection Code] ),
SWITCH ( __SelectedComparedValue, "PY", [Value PY], "BDG", [Budget] ),
[Value PY]
)
)
can probably use some refactoring. You can check its query plan in DAXStudio.