Forum Discussion

Gabriele_hbto's avatar
Gabriele_hbto
Icon for Helper II rankHelper II
2 years ago

Direct query BAD performance, what to do to try to optimize?

Hey guys, can anyone please give me some tips on how to improve performance on my model? I have a simple star schema. The fact table has only 40k rows. I'm required to use DirectQuery. I embed t...
Need Help
Gabriele_hbto's avatar
Gabriele_hbto
Icon for Helper II rankHelper II
2 years ago

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

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

In your SQL Server slap an index on Level1Description and Level1Order

 

Show the measure(s) that feed(s) the values area of the matrix.

  • Gabriele_hbto's avatar
    Gabriele_hbto
    Icon for Helper II rankHelper II
    2 years ago

    Ok I'm going to add indexes on Levels.
    Those are the measures:

    Value = SUM( 'masterdata RiclassificationValuesForReports'[MonthValue] ) + SUM('masterdata RiclassificationValuesForReports'[AdjustmentValue])

    Value YTD =SUM('masterdata RiclassificationValuesForReports'[Value])
     
    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]
        ))
     
     
    %Δ Value-Compared Value (matrix) =
    VAR __Delta = DIVIDE( [Δ Value-Compared Value (matrix)], [Compared Value (matrix)] )

    RETURN
    if(ISBLANK('dim Reclassifications'[Reclassification Hierachy Selected Level]), blank(),
    IF(
        SELECTEDVALUE('dim Reclassifications'[Level2Code]) = "E.B", - __Delta,
        __Delta))
    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

      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.