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

[Gabriele_hbto]

Hey guys, can anyone please give me some tips on how to improve performance on my model?

1. I have a simple star schema.
2. The fact table has only 40k rows.
3. I'm required to use DirectQuery.
4. I embed the report on an embedded capacity.
5. I query a SQL Server on Azure.
6. My visual is just a matrix with 6 columns (only sum and %) with a hierarchy made with a dimension table.

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?

[lbendlin]

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.

[Gabriele_hbto]

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]

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]

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]

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.