AAS Bug with Calculation Groups Sideways recursion with Ordinals

[o-o00o-o]

Hi guys, I know this isn't a Tabular Editor bug, it is an SSAS/AAS bug.

I've written some details on mdsn forums but I will record here also for 2 reasons

• I can add my repro model here as this site accepts attachments (msdn doesn't from what I can see)
• I went to log this bug with our Azure support but it doesn't look like they accept product issues from the list of "problem types" there isn't anything for Query Engine or anything similar. I know some of you have direct lines in but where is the best place to raise this bug? I've tried logging https://feedback.azure.com/forums/908035-sql-server/suggestions/40047172-fix-aas-bug-with-calculation-groups-sideways-recur but it isn't clear if this is for Analysis Services or just SQL Server Engine.

---

We have hit a (another) problem with Calculation Groups that means that we have had to take off Ordinals on all our Calculation Items

I have spent quite a lot of time building a simplified repro which I will try to attach (although I can't see a way to do that in this initial post)

The self-contained repro is here. See the bim and the DAX that demonstrates the issue DemonstrateCalcGroupOrdinalIssue.zip

Model looks like this

• CG1{2} simply * 2 to the selectedmeasure

      SELECTEDMEASURE () * 2

• CG1{4} reuses CG1[2] via sideways recursion and multiplies it by 2

        VAR reuseFromSameCG =
            CALCULATE ( SELECTEDMEASURE (), 'CG1'[CG1] = "2" )
        RETURN
            reuseFromSameCG * 2

• CG2[8] only wants to apply a * 8 if we have already got CG1 2 or 4 filtered (not 1).

      IF (
          ISFILTERED ( 'CG1'[CG1] )
              && (
                  SELECTEDVALUE ( 'CG1'[CG1] ) = "2"
              //|| SELECTEDVALUE ( 'CG1'[CG1] ) = "4" -- even adding this condition doesn't work!
              ),
              SELECTEDMEASURE () * 8
      )

• CG1 has Ordinals set, CG2 doesn't (they are all -1)

Running the following queries should return the same rows, but they don't. Notice the only difference is query 2 has commented out the Ordinal columns. BTW this query is what Power BI will create if you stack 2 CGs on the same axis on a Matrix visual - which is why this is important

define 
    var rodsOnly = filter(VALUES(Orders[Item]), [Item] = "Fishing rod")
EVALUATE
    SUMMARIZECOLUMNS(
        'Orders'[Item],
        'CG1'[Ordinal],
        'CG2'[Ordinal],
        'CG1'[CG1],
        'CG2'[CG2],
        rodsOnly,
        "OrderPrice", [OrderPrice]
   )
order by 'Orders'[Item],
'CG1'[CG1], 
'CG2'[CG2]

EVALUATE
    SUMMARIZECOLUMNS(
        'Orders'[Item],
--          'CG1'[Ordinal],
--          'CG2'[Ordinal],
        'CG1'[CG1],
        'CG2'[CG2],
        rodsOnly,
        "OrderPrice", [OrderPrice]
   )
order by 'Orders'[Item], 
'CG1'[CG1], 
'CG2'[CG2]

This produces the following 2 results

--- this is not correct result as cg1[2] should be included in both cases that it is being used

Item    Ordinal    Ordinal    CG1    CG2    OrderPrice
Fishing rod    0    -1    1    1    100
Fishing rod    1    -1    2    1    200
Fishing rod    1    -1    2    8    1600
Fishing rod    2    -1    4    1    400

--- this is the correct result as cg1[2] is being included in both cases that it is being used

Item    CG1    CG2    OrderPrice
Fishing rod    1    1    100
Fishing rod    2    1    200
Fishing rod    2    8    1600
Fishing rod    4    1    400
Fishing rod    4    8    3200

The second one is correct, the first one is wrong. Ommitting the Ordinal columns shouldn't change the results.

We can solve this issue by not applying any Ordinals to any Calculation Groups (setting them all back to -1).

So we have a choice, either we can't use sideways recursion (and detect which other CI's are being applied) or we can't order our Calculation Items! :-( Notice I also tried adding CG1 = "4" to the CG2{8} CI but it made no difference, the only solution so far is to set all Ordinals to -1

Any other workaround suggestions welcome

[otykier]

Thanks for letting us know.

I would still raise this bug through the Azure Support interface. As long as you've selected Analysis Services as the service, then I think it shouldn't matter much which Problem Type you select (I would use "Query_Execution" for type and "Performance and timeouts" for the subtype):

For this particular issue, the product team has been informed, and I will try to forward any updates here.

[o-o00o-o]

Thanks Daniel, I've had a response from emailing azcommunity@microsoft.com and they are asking for SR# details of any existing case that is open - I guess you already have one, so could I share this with them rather than them opening a new case?

Brett

[otykier]

I don't have a SR# - one of the benefits of being an MVP is that we can contact certain people directly :-) I suggest you create the SR as this will help Microsoft prioritise a fix for this issue.

[otykier]

Hi @o-o00o-o - just out of curiosity, are there any updates on this? Do you know if it has been resolved by the AS engine team?

[o-o00o-o]

Yes, it seems that this is expected behaviour with the way that I was doing it.

the answer was actually when inside a CI and trying to detect if another specific CG was being used , we should use FILTERS instead of SELECTEDVALUE like I was in my original message.

e.g

CG2[8] only wants to apply a * 8 if we have already got CG1 2 or 4 filtered (not 1).
        IF (
            ISFILTERED ( 'CG1'[CG1] ) // pretty sure that this is superfluous now, but leaving to keep continuity with my original example
                && FILTERS( 'CG1'[CG1] ) = "2",
                SELECTEDMEASURE () * 8
        )

By using FILTERS it is just checking whether there is a filter on a specific CI or not without considering the current context which can include Ordinal if ordinals are defined against the CG

There were other solutions, but this was the cleanest.

[otykier]

I see - thanks for responding. As a side note, I've heard some reports on reduced performance when using sideways recursion. Apparently, inlining the expressions seem to fare better for now.

[zenzeinet]

Thanks for posting the answer, good to know. By any chance, did you also try changing..

VAR reuseFromSameCG = CALCULATE ( SELECTEDMEASURE (), 'CG1'[CG1] = "2" )

To

VAR reuseFromSameCG = CALCULATE ( SELECTEDMEASURE (), 'CG1'[CG1] = "2", all(cg1[ordinal]) )

To remove the ordinal, in case it is automatically included for sorting and that would affect the result?

Thanks!

El lun., 24 ago. 2020 15:24, Brett Gerhardi notifications@github.com escribió:

Yes, it seems that this is expected behaviour with the way that I was doing it.

the answer was actually when inside a CI and trying to detect if another specific CG was being used , we should use FILTERS instead of SELECTEDVALUE like I was in my original message.

e.g

CG2[8] only wants to apply a 8 if we have already got CG1 2 or 4 filtered (not 1). IF ( ISFILTERED ( 'CG1'[CG1] ) // pretty sure that this is superfluous now, but leaving to keep continuity with my original example && FILTERS( 'CG1'[CG1] ) = "2", SELECTEDMEASURE () 8 )

By using FILTERS it is just checking whether there is a filter on a specific CI or not without considering the current context. There were other solutions, but this was the cleanest.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/otykier/TabularEditor/issues/414#issuecomment-679123673, or unsubscribe https://github.com/notifications/unsubscribe-auth/AGTQLM66BRWJSKB42NXH5KLSCJSXPANCNFSM4LV757ZA .

[o-o00o-o]

Hi, yes that was MSs first suggestion and it did work. The reason I prefer the filters option is that it is at that point that you know you are doing sideways recursion (rather than sprinkling ALLs everywhere just in case) and it adds no extra code, just switch the selectedvalues for filters . Adding ALLs everywhere also makes the underlying intention of the code more difficult to read and it may never be needed so I guess there may be a performance hit.