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
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.