Cannot optimize the query using a tuple of measure and first item of VisibleRowsSet

Questions & Answers
1

I have a report containing Time dimension as rows and MEasure as columns.
Measure is weekly throughput.

image
image857×677 27.6 KB

Now I wanted to calculate a percentiles:

Percentile(Cache(VisibleRowsSet()), [Measures].[Throughput], 50)

But even though rows are cached this formula takes so much time, because the percentile is calculated for every row.
So I thought “ok, I can just copy the results from the first row to save calculation”.

First, the percentile is filtered by a date in one measure, to be used in another measure:

CASE
WHEN
DateInPeriod(
[Measures].[Start date],
[Time].CurrentHierarchyMember
)
THEN
Percentile(Cache(VisibleRowsSet()), [Measures].[Throughput], 50)
END

Its because I could not find a way of “reusing” the calculated measure value from previous time member within the definition of the very same calculated measure.
And recurrence is impossible too (which is good).

But then… its impossible for me to create a tuple with the first time dimension member I store a calculation in:

image
image891×487 24 KB

Which I think is a bug, because tuple is exactly combination of members from different dimensions.
And I assume VisibleRowSet().Item(0) shall be the first member.

Without this tuple I cannot optimize the query.
How else can I optimize it or build differently?

2

Hi @MrR4ndom,

Since the .item(0) can return a dimension member or an empty response, tuples can fail.
Therefore, you cannot use .item(0) within a tuple, although it seems a legitimate approach.
If you need a numeric measure for the first member, you might better use the Head() to retrieve the first item and Sum() on the retrieved set of 1 member.

Sum(
 Head(VisibleRowsSet(),1),
--put the actual measure here
 [Measures].[Throughput 50 percentile base])

However, this might still call for the Percentile calculation for the first row from each row. To speed up the calculation, you might put the whole Percentile() function within Cache() in a separate calculated measure that is not displayed on the report.

In that case, this percentile calculation will only be called for the first row and cached. The remaining rows will request the already cached calculated value.

Regards,
Oskars / support@eazyBI.com

3

Unfortunately that did not help either.
I tried multiple Caches in every place I thought it would help, but it would not speed up the calculation.

The only solution (W/A) I have here is to calculate Measure only in one specific time cell - here I took weekly “W47, Nov 16 2020”.
Then all other measures need to be calculated only when the time member is exactly this one - this way I am able to reduce the calculations percentile.
And to actually calculate it in sensible time which would not exceed timeout.

It’s not very flexible, but doable.

If there are no other ideas, thank you then, we can close the topic.

4

Please contact the support via e-mail and share your report definition.
I will look into it to see if there are some better options.

Regards,
Oskars / support@eazyBI.com