Pentaho

 View Only

 Percentile in Analysis Report and MDX Datasource in Ctools CDE Dashboard

  • General
  • ReportingAndAnalytics
  • Pentaho
  • Ctools
  • Pentaho
Marek Tomko's profile image
Marek Tomko posted 11-30-2020 16:28

Hi,

 

Is there any way to calculate and visualise Percentile in Analysis Report and using MDX data source in CTools CDE Dahsboard (e.g. Charts)?

 

Thanks and regards,

 

Marek


#ReportingandAnalytics
#Pentaho
#Ctools
William Jansen Van Nieuwenhuizen's profile image
William Jansen Van Nieuwenhuizen

Hello

 

Do you perhaps have an example of the data your looking for?

William Jansen Van Nieuwenhuizen's profile image
William Jansen Van Nieuwenhuizen

Also might be good to check out the example below, if this is Analyzer related:

https://community.hitachivantara.com/s/question/0D52S00008QNyR9SAL/generating-reports-from-analyzer

 

Marek Tomko's profile image
Marek Tomko

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,

 

SampleDataPSG1 

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.