Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Improve performance of Aggregate subtotals

Description

Environment

None

Test Cases

None

Steps to Reproduce

None

Workaround

None

Attachments

4
screenshot-2.png
07 Feb, 2021
screenshot-1.png
07 Feb, 2021
Subtotal - One Segment Load - AGG.xanalyzer.zip
07 Feb, 2021
Subtotal - One Segment Load - SUM.xanalyzer.zip
07 Feb, 2021

Activity

Show:
Steve Maring
deleted a Comment
March 2, 2023 at 11:54 PM
Steve Maring
deleted a Comment
March 2, 2023 at 11:54 PM
Steve Maring
updated the RemoteIssueLink
February 6, 2023 at 8:24 PM
None
This issue links to "ANALYZER-4016 (has Pentaho private)"
Doug Mandeville
updated the Workflow
August 23, 2022 at 7:22 PM
Pentaho Engineering 9.0 Workflow
Pentaho Engineering 9.0.4 Workflow
Gurudev S R
changed the Status
August 12, 2021 at 1:22 PM
Reopened
Closed
Gurudev S R
updated the Resolution
August 12, 2021 at 1:22 PM
None
Fixed
Gurudev S R
updated the Security Level
August 12, 2021 at 1:22 PM
InternalAndContractors
None
Gurudev S R
changed the Status
August 12, 2021 at 1:22 PM
Closed
Reopened
Gurudev S R
updated the Resolution
August 12, 2021 at 1:22 PM
Fixed
None
Duarte Cunha Leao
changed the Status
March 29, 2021 at 5:46 PM
Resolved
Closed
Carlos Lopez
updated the Linked Issues
February 17, 2021 at 10:42 PM
None
This issue relates to MONDRIAN-2684
Benny Chow
changed the Assignee
February 15, 2021 at 9:21 PM
Benny Chow
Unassigned
Benny Chow
changed the Status
February 15, 2021 at 9:21 PM
In Progress
Resolved
Benny Chow
updated the Resolution
February 15, 2021 at 9:21 PM
None
Fixed
Benny Chow
updated the Linked Issues
February 8, 2021 at 11:27 PM
None
This issue is blocked by MONDRIAN-2713
Benny Chow
updated the Description
February 7, 2021 at 10:31 PM
Performance of aggregate totals with filters on the report can be significantly improved by lowering the solve order of the compound slicer member so that it is evaluated after the aggregate total calculated member. The aggregate subtotal member uses a solve order of -100. (The design decision for -100 was so that schema defined calculated measures that default to a solve order of 0 can work with aggregate totals.) The current solve order of the compound slicer member is 0 but we should make this configurable and default to -99999. Here's a sample report with 62K rows with 4K totals. You can see the big improvement between the red and green rows when changing the solve order. The number of cell requests and calls to the Aggregate function are significantly reduced: !screenshot-1.png|thumbnail! Report: !screenshot-2.png|thumbnail! [^Subtotal - One Segment Load - AGG.xanalyzer.zip] [^Subtotal - One Segment Load - SUM.xanalyzer.zip] The reason for such a big improvement has to do with evaluating the AGGREGATE calculated member before the compound slicer calculated member and being able to leverage a better expression cache that is implicitly used by the compound slicer. Note: For comparison, I ran the same report with SUM totals and SUM is still faster than AGG. I also compare with 9.1 and the performance is the same as 9.2 when the solve order is 0.
Performance of aggregate totals with filters on the report can be significantly improved by lowering the solve order of the compound slicer member so that it is evaluated after the aggregate total calculated member. The aggregate subtotal member uses a solve order of -100. (The design decision for -100 was so that schema defined calculated measures that default to a solve order of 0 can work with aggregate totals.) The current solve order of the compound slicer member is 0 but we should make this configurable and default to -99999. Here's a sample report with 62K rows with 4K totals. *Solve order -99999 runs 45% faster than solve order 0 (Compare red and green rows)*. The number of cell requests and calls to the Aggregate function are significantly reduced: !screenshot-1.png|thumbnail! Report: !screenshot-2.png|thumbnail! [^Subtotal - One Segment Load - AGG.xanalyzer.zip] [^Subtotal - One Segment Load - SUM.xanalyzer.zip] The reason for such a big improvement has to do with evaluating the AGGREGATE calculated member before the compound slicer calculated member and being able to leverage a better expression cache that is implicitly used by the compound slicer. Note: For comparison, I ran the same report with SUM totals and SUM is still faster than AGG. I also compare with 9.1 and the performance is the same as 9.2 when the solve order is 0.
Benny Chow
changed the Status
February 7, 2021 at 9:40 PM
Open
In Progress
Benny Chow
changed the Assignee
February 7, 2021 at 9:40 PM
Triage
Benny Chow
Benny Chow
updated the Description
February 7, 2021 at 9:38 PM
Performance of aggregate totals with filters on the report can be significantly improved by lowering the solve order of the compound slicer member so that it is evaluated after the aggregate total calculated member. The aggregate subtotal member uses a solve order of -100. (The design decision for -100 was so that schema defined calculated measures that default to a solve order of 0 can work with aggregate totals.) The current solve order of the compound slicer member is 0 but we should make this configurable and default to -99999. Here's a sample report with 62K rows with 4K totals. You can see the big improvement between the red and green rows when changing the solve order. The number of cell requests and calls to the Aggregate function are significantly reduced: !screenshot-1.png|thumbnail! Report: !screenshot-2.png|thumbnail! [^Subtotal - One Segment Load - AGG.xanalyzer.zip] [^Subtotal - One Segment Load - SUM.xanalyzer.zip] Note: For comparison, I ran the same report with SUM totals and SUM is still faster than AGG. I also compare with 9.1 and the performance is the same as 9.2 when the solve order is 0.
Performance of aggregate totals with filters on the report can be significantly improved by lowering the solve order of the compound slicer member so that it is evaluated after the aggregate total calculated member. The aggregate subtotal member uses a solve order of -100. (The design decision for -100 was so that schema defined calculated measures that default to a solve order of 0 can work with aggregate totals.) The current solve order of the compound slicer member is 0 but we should make this configurable and default to -99999. Here's a sample report with 62K rows with 4K totals. You can see the big improvement between the red and green rows when changing the solve order. The number of cell requests and calls to the Aggregate function are significantly reduced: !screenshot-1.png|thumbnail! Report: !screenshot-2.png|thumbnail! [^Subtotal - One Segment Load - AGG.xanalyzer.zip] [^Subtotal - One Segment Load - SUM.xanalyzer.zip] The reason for such a big improvement has to do with evaluating the AGGREGATE calculated member before the compound slicer calculated member and being able to leverage a better expression cache that is implicitly used by the compound slicer. Note: For comparison, I ran the same report with SUM totals and SUM is still faster than AGG. I also compare with 9.1 and the performance is the same as 9.2 when the solve order is 0.
Benny Chow
updated the Description
February 7, 2021 at 9:34 PM
Performance of aggregate totals with filters on the report can be significantly improved by lowering the solve order of the compound slicer member so that it is evaluated after the aggregate total calculated member. The aggregate subtotal member uses a solve order of -100. (The design decision for -100 was so that schema defined calculated measures that default to a solve order of 0 can work with aggregate totals.) The current solve order of the compound slicer member is 0 but we should make this configurable and default to -99999. Here's a sample report with 62K rows with 4K totals. You can see the big improvement between the red and green rows when changing the solve order. The number of cell requests and calls to the Aggregate function are significantly reduced: !screenshot-1.png|thumbnail! Report: !screenshot-2.png|thumbnail! [^Subtotal - One Segment Load - AGG.xanalyzer.zip] [^Subtotal - One Segment Load - SUM.xanalyzer.zip]
Performance of aggregate totals with filters on the report can be significantly improved by lowering the solve order of the compound slicer member so that it is evaluated after the aggregate total calculated member. The aggregate subtotal member uses a solve order of -100. (The design decision for -100 was so that schema defined calculated measures that default to a solve order of 0 can work with aggregate totals.) The current solve order of the compound slicer member is 0 but we should make this configurable and default to -99999. Here's a sample report with 62K rows with 4K totals. You can see the big improvement between the red and green rows when changing the solve order. The number of cell requests and calls to the Aggregate function are significantly reduced: !screenshot-1.png|thumbnail! Report: !screenshot-2.png|thumbnail! [^Subtotal - One Segment Load - AGG.xanalyzer.zip] [^Subtotal - One Segment Load - SUM.xanalyzer.zip] Note: For comparison, I ran the same report with SUM totals and SUM is still faster than AGG. I also compare with 9.1 and the performance is the same as 9.2 when the solve order is 0.
Benny Chow
updated the Description
February 7, 2021 at 9:31 PM
Performance of aggregate totals with filters on the report can be significantly improved by lowering the solve order of the compound slicer member so that it is evaluated after the aggregate total calculated member. The aggregate subtotal member uses a solve order of -100. (The design decision for -100 was so that schema defined calculated measures that default to a solve order of 0 can work with aggregate totals.) The current solve order of the compound slicer member is 0 but we should make this configurable and default to -99999. Here's a sample report with 62K rows with 4K totals. You can see the big improvement between the red and green rows when changing the solve order. The number of cell requests and calls to the Aggregate function are significantly reduced: !screenshot-1.png|thumbnail! Report: !screenshot-2.png|thumbnail! [^Subtotal - One Segment Load - AGG.xanalyzer.zip]
Performance of aggregate totals with filters on the report can be significantly improved by lowering the solve order of the compound slicer member so that it is evaluated after the aggregate total calculated member. The aggregate subtotal member uses a solve order of -100. (The design decision for -100 was so that schema defined calculated measures that default to a solve order of 0 can work with aggregate totals.) The current solve order of the compound slicer member is 0 but we should make this configurable and default to -99999. Here's a sample report with 62K rows with 4K totals. You can see the big improvement between the red and green rows when changing the solve order. The number of cell requests and calls to the Aggregate function are significantly reduced: !screenshot-1.png|thumbnail! Report: !screenshot-2.png|thumbnail! [^Subtotal - One Segment Load - AGG.xanalyzer.zip] [^Subtotal - One Segment Load - SUM.xanalyzer.zip]
Benny Chow
updated the Description
February 7, 2021 at 9:31 PM
None
Performance of aggregate totals with filters on the report can be significantly improved by lowering the solve order of the compound slicer member so that it is evaluated after the aggregate total calculated member. The aggregate subtotal member uses a solve order of -100. (The design decision for -100 was so that schema defined calculated measures that default to a solve order of 0 can work with aggregate totals.) The current solve order of the compound slicer member is 0 but we should make this configurable and default to -99999. Here's a sample report with 62K rows with 4K totals. You can see the big improvement between the red and green rows when changing the solve order. The number of cell requests and calls to the Aggregate function are significantly reduced: !screenshot-1.png|thumbnail! Report: !screenshot-2.png|thumbnail! [^Subtotal - One Segment Load - AGG.xanalyzer.zip]
Benny Chow
added an Attachment
February 7, 2021 at 9:31 PM
None
screenshot-2.png
Benny Chow
added an Attachment
February 7, 2021 at 9:30 PM
None
screenshot-1.png
Benny Chow
added an Attachment
February 7, 2021 at 9:24 PM
None
Subtotal - One Segment Load - SUM.xanalyzer.zip
Benny Chow
added an Attachment
February 7, 2021 at 9:24 PM
None
Subtotal - One Segment Load - AGG.xanalyzer.zip
Benny Chow
created the Improvement
February 7, 2021 at 7:42 PM
Fixed

Details

Assignee

Reporter

Edit Screen Preamble

<div class="notify info" style="margin-bottom: 10px;">
If you are a Pentaho customer, please use the <a href="http://support.pentaho.com">Customer Support portal</a> to log issues.
<p />
This system is used for logging bugs and enhancement requests only. Please use our <a href="https://community.pentaho.com">community at https://community.pentaho.com</a> if you have questions, configuration issues, or have an issue with a marketplace plugin as Pentaho does not support marketplace plugins unless written by Pentaho.
<p />
Lastly, when creating a bug, please provide as much detail as possible. To prevent unnecessary delays in reviewing your issue, please attach complete server logs, SQL/MDX logs where applicable, schemas, etc. Also, screen-shots and screen-cams are especially helpful in demonstrating the issue.
<p />
Thank-you so much,<br />
The Pentaho Team
</div>

Notice

<div class="notify info" style="margin-bottom: 10px;">
When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.
</div>

Sprint Team

Story Points

Fix versions

Priority

Created February 7, 2021 at 7:42 PM
Updated March 2, 2023 at 11:54 PM
Resolved August 12, 2021 at 1:22 PM