We present here an example of a very advanced MDX that we encountered with one of our customers. Note it uses a functionality of icCube that allows for a tuple to have the same hierarchy multiple times defined.
The use case
The use case involves multiple time hierarchies within a single time dimension in a non standard way.
The simplified structure is as follows:
- Dimension: Calendar
- Hierarchy: Year
- Level: Year
- Hierarchy: Period
- Level: Period Value (PV)
- Level: Period (P)
- Hierarchy: Year
Period hierarchy example (PV & P children):
- 1
- P1 2024
- P1 2025
- P1 2026
- 2
- P2 2024
- P2 2025
- P2 2026
- …
The goal is to compute Sales for the previous year while displaying Year, PV, and P. Here’s the initial query:
WITH
MEMBER [Measures].[Sales Prev Year] AS ([Calendar].[Year].currentMember.prevMember, [Measures].[Sales])
SELECT
{ [Measures].[Sales], [Measures].[Sales Prev Year] } on 0
[Calendar].[Year].[Year] *
[Calendar].[Period].[PV] *
[Calendar].[Period].[P] on 1
[Product].[Product].[Product] on 2
FROM [Cube]
In our example we are looking to have something like:
But what we got:
What happened here?
Since Year and Period are separate hierarchies, the expression
becomes (after completion from the context defined from the axes):
Why?
We got:
Visually:
What we need to do
Solution
Specify the Period’s currentMember to override the default:
As read in the currentMember doc:
Since icCube 4.8, this function returns a set of members if the tuple contains two members of the same hierarchy. This functionality needs the 'dimensionalityCheck' icCube property set to false.
Things to take into account:
- You can use the same hierarchy in one or multiple axes but it might complicate your calculations.
- In this scenario, currentMember returns a set.
This subtle, advanced scenario highlights how hierarchy interactions can silently skew results. Tools like an MDX debugger are invaluable for tracing such calculations step-by-step.