Tutorials: https://daanse.org/cubeserver/tutorial/tutorial.cube.hierarchy.hasall.html (or implementation bug???)

[kdengler]

In the tutorial https://daanse.org/cubeserver/tutorial/tutorial.cube.hierarchy.hasall.html, 3 different options for the 'All' member of a hierarchy are shown (2 hierarchies with 'All' member and 1 without).

There seems to be a bug with the 2 hierarchies having an 'All' member. If you execute the cube in Excel, you see no 'All' level in these hierarchies. If the 'All' Level should be represented in the Total Result Line, custom level/member names are not provided. Also only one of the level members (A & B) is shown:

[dbulahov]

@kdengler Could you look please attached video.
I had talking with Sergei about two (several) hierarchies in one dimension.
He said that behavior when we use one hierarchy with hasAll = false and second with hasAll = true and result both of them don't have hasAll is correct.

I don't know should we fix behavior when
heerarchy1 hasAll = true
heerarchy2 hasAll = false

result - have all section.

now we have behavior when

heerarchy1 hasAll = true
heerarchy2 hasAll = false

result - doesn't have all section.

[kdengler]

Another recording about executing the Tutorial Cube with Excel Version 2024:

Daanse_IssueAllLevel.mp4

[kdengler]

@dbulahov I uploaded another video above showing my issues on the hasAll member in Excel.

I think the behaviour when hasAll = false is OK.

In my issue, there are actually 2 "sub-issues":

Missing member when hasAll=true

In those cases where hasAll=true, in my Excel Version 2024 the member 'B' from the tutorial is not shown in the pivot. There seems to be a bug somewhere. I executed the original downloadable tutorial cube.

Representation of the All level in Excel in general

I think there are basically 2 options how the All level could be represented in Excel when hasAll =true:

a) The 'total' row at the end of the Pivot table represents the All Level (and also sub-total rows if more than 1 hierarchies with hasAll=true are combined)

This seems to be the case now. But there is the problem that even if a custom All member name is defined in the mapping xmi, it is not shown in Excel. The total line always has the same name provided by Excel (in German "Gesamtergebnis"). If the custom All member name was provided correctly, the Excel Pivot would look this way:

b) The All level is represented by a normal row in the Pivot Table

That means in our tutorial example, the pivot table would not show the members 'A' and 'B' first in the rows of the pivot. Instead, the All level is initially shown (with its custom names, if defined) an can be drilled down to the members of the next level:

Maybe this option has the advantage that Excel-given totals have not to be overrided. But I don't know if it is possible to implement this structure. And it may be easier to combine hierarchies with hasAll=true and hasAll=false (?)

Please discuss with @stbischof.

[kdengler]

@dbulahov, I've seen in your video that there isn't the missing 'B' member problem in your Excel. Have you already fixed it?

[dbulahov]

In my video I can see that "B" is present . I have fixed that. But Stefan did not merged and deployed that yet
Right now I can see two issues in my video which I would like to discus

1. when we use hasAll true and false then all section is absent. Is it correct or I should fix that?
2. " ..HasAll and Names" hierarchy has custom name for all section but we still see default.

[dbulahov]

@kdengler

[dbulahov]

@kdengler What should be behavior when we click to hierarchy with hasAll true and hierarchy hasAll false? What do you think?

[kdengler]

@kdengler Could you look please attached video. I had talking with Sergei about two (several) hierarchies in one dimension. He said that behavior when we use one hierarchy with hasAll = false and second with hasAll = true and result both of them don't have hasAll is correct.

I don't know should we fix behavior when heerarchy1 hasAll = true heerarchy2 hasAll = false

result - have all section.

now we have behavior when

heerarchy1 hasAll = true heerarchy2 hasAll = false

result - doesn't have all section.

Hm, basically I think that if a hierarchy hasAll=true then the all level should be shown regardless of the pivot position. But I assume the problem is, if the All level corresponds with totals and subtotals in the pivot, you'll get a problem if you have more than 2 hierarchies.

This is the pivot behaviour I would expect as a user for combination of 2 hierarchies:

hierarchy 1 hasAll=true
hierarchy 2 hasAll=true
-> both total and sub-totals

hierarchy 1 hasAll=false
hierarchy 2 hasAll=false
-> neither total nor sub-totals

hierarchy 1 hasAll=true
hierarchy 2 hasAll=false
-> total, but no sub-totals

hierarchy 1 hasAll=false
hierarchy 2 hasAll=true
-> no total, but sub-totals

But as said, as soon as a 3rd hierarchy occurs and this has a different hasAll rule than the 2nd hierarchy, it may be that you cannot solve this with the pivot total/sub-total logic. Because I guess sub-totals cannot be controlled for each level differently (which has to be proved for XMLA data...)

[kdengler]

And the hierarchies in the examples above all have only 1 regular level. It could get even more difficult with more hierarchy levels.

That's why I think a new way to show the All level as a 'normal' drillable row in the pivot could possibly make things easier. But I haven't fully thought this idea through either.

[dbulahov]

@kdengler
@stbischof Stefan rejected fix of this issue. The reason for this was the falling integrations tests.
After fixing, the behavior changed. But the tests expected the old behavior. I had talking with Sergei Semenkov. He is our OLAP expert. He said that previous behavior (before fix) was correct. He said that fix will change conception of OLAP. He provided link to documentation
https://learn.microsoft.com/en-us/analysis-services/multidimensional-models/attribute-properties-define-a-default-member?view=sql-analysis-services-2025&utm_source=chatgpt.com

This is a quote:
...
The default member of an attribute hierarchy is used to evaluate expressions when an attribute hierarchy is not included in a query. The default member is ignored whenever a query includes an attribute hierarchy or user hierarchy that contains the attribute that sources the attribute hierarchy. This is because the member specified in the query is used.
...

I have solution for current issue. But we should change expected behavior in integration tests for that. I am suggestion to discuss that with Sergei and decide our next steps if need.