Hi,
I attached some sample data with sample schema.
For CDE I'm trying to implement simple script:
WITH MEMBER
[Measures].[m1avgCpu-99p] as Percentile([Time].[Time].MEMBERS, [Measures].[m1avgCpu], 99)
SELECT
{[Measures].[m1avgCpu],[Measures].[m1avgCpu-99p]} ON COLUMNS,
NONEMPTYCROSSJOIN([Date].[Day].MEMBERS,[Time].[Time].MEMBERS) ON ROWS
FROM [CubeVirtual]
The thing here is, that I'm getting percentile results aggregated by day,
but I always want to have them aggregated by the lowest level mentioned ON ROWS.
For example if I say:
NONEMPTYCROSSJOIN([Date].[Day].MEMBERS,[Time].[Time].MEMBERS) ON ROWS
I want percentile be aggregated by time for every day
If I say:
NONEMPTYCROSSJOIN([Date].[Day].MEMBERS,[Time].[Hour].MEMBERS) ON ROWS
I want percentile be aggregated by hour for every day
If I say:
[Date].[Day].MEMBERS ON ROWS
I want percentile be aggregated by day
... or Month, Year ...
The thing is that it needs to take all [m1avgCpu] values on the lowest level, which is [Time].[Time] in this case, and calculate Percentile by Time, or Hour, or Day, or Month, or Year, as I mentioned above.
I'm new in MDX and have tried different approaches with Children, CurrentMember, ... without any success.
Thanks in advance.