Report often takes more than 60s and fail to load... how to improve performance

Questions & Answers
1

Dear experts, I have few report often taking more than 60s and then fail to load; they were fine a few months ago but lately it became problem. Please help analyze the report definition below and suggest what can be done to improve the performance. Thanks!

{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “ThinOS SUS metric #999 team resolution total”,
“folder_name”: “Leon Kan Li (please do not edit)”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Time”,“selected_set”:["[Time].[Month].Members"],“members”:[{“depth”:3,“name”:“Jan 2019”,“full_name”:"[Time].[2019].[Q1 2019].[Jan 2019]",“drillable”:true,“removed”:true},{“depth”:3,“name”:“Nov 2018”,“full_name”:"[Time].[2018].[Q4 2018].[Nov 2018]",“drillable”:true,“removed”:true},{“depth”:3,“name”:“Dec 2018”,“full_name”:"[Time].[2018].[Q4 2018].[Dec 2018]",“drillable”:true,“removed”:true}],“bookmarked_members”:[]}]},“rows”:{“dimensions”:[{“name”:“Measures”,“selected_set”:["[Measures].[Total Engineers with resolution]","[Measures].[Issues resolved]","[Measures].[Total estimate hrs of resolution]"],“members”:[]},{“name”:“Engineer”,“selected_set”:["[Engineer].[All Engineers THINOS]","[Engineer].[\u003e\u003e\u003e BJDC All]","[Engineer].[BJDC 100%]","[Engineer].[BDC 100%]","[Engineer].[SVDC 100%]","[Engineer].[NONE / OLD]"],“members”:[],“bookmarked_members”:[]}]},“pages”:{“dimensions”:[{“name”:“Project”,“selected_set”:["[Project].[Project].Members"],“members”:[{“depth”:1,“name”:“ThinOS Defect Management”,“full_name”:"[Project].[ThinOS Defect Management]",“drillable”:true,“key”:“THINOS”},{“depth”:1,“name”:“ThinOS Maintenance”,“full_name”:"[Project].[ThinOS Maintenance]",“drillable”:true,“key”:“THINOXSUS”}],“bookmarked_members”:[],“current_page_members”:["[Project].[ThinOS Defect Management]","[Project].[ThinOS Maintenance]"]},{“name”:“Found By”,“selected_set”:["[Found By].[All Found Bies]"],“members”:[{“depth”:0,“name”:“All Found Bies”,“full_name”:"[Found By].[All Found Bies]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false}],“bookmarked_members”:[],“current_page_members”:["[Found By].[All Found Bies]"]},{“name”:“Sub Project”,“selected_set”:["[Sub Project].[All Sub Projects]"],“members”:[{“depth”:0,“name”:“All Sub Projects”,“full_name”:"[Sub Project].[All Sub Projects]",“drillable”:true,“type”:“all”}],“bookmarked_members”:[],“current_page_members”:["[Sub Project].[All Sub Projects]"]}]},“options”:{“nonempty”:true},“view”:{“current”:“table”,“maximized”:false,“table”:{“freeze_header”:true}}}
} ],
“calculated_members”: [{“name”:“SVDC 100%”,“dimension”:“Engineer”,“formula”:“Aggregate({\n[Engineer].[Michael Chew], [Engineer].[Usman Ansari], \n[Engineer].[Yani Lau], [Engineer].[Bach Le],\n[Engineer].[Raghu Chereddy]\n\n})”,“format_string”:""},{“name”:“BDC 100%”,“dimension”:“Engineer”,“formula”:“Aggregate({\n[Engineer].[Ajit Kishore]\n, [Engineer].[Dheeraj Reddy]\n, [Engineer].[Anil Gurumurthy]\n, [Engineer].[Anup Keshwani]\n, [Engineer].[Harish Agrawal]\n, [Engineer].[arjun chandran]\n, [Engineer].[Ravi Parashetty]\n, [Engineer].[Charan Sai K]\n,[Engineer].[Ujwal Agarwal]\n\n\n})”,“format_string”:""},{“name”:“NONE / OLD”,“dimension”:“Engineer”,“formula”:"\nAggregate({\n\n[Engineer].[(none)]\n\n})\n\n",“format_string”:""},{“name”:“Total estimate hrs of resolution”,“dimension”:“Measures”,“formula”:“Sum(\n Filter(\n Descendants([Issue].CurrentMember,\n [Issue].[Issue]),\n [Measures].[Issues resolved]\u003e0\n ),\n–[Issue].CurrentHierarchyMember.get(‘Original estimated hours’)\n\n [Measures].[Original estimated hours resolved]\n --(\n-- [Measures].[Original estimated hours]–,\n-- [Engineer].CurrentHierarchyMember,\n-- [Time].CurrentMember)\n)\n”,“format_string”:"#,##0.00"},{“name”:“Total Engineers with resolution”,“dimension”:“Measures”,“formula”:“case when [Measures].[Issues resolved]\u003e0\nTHEN\nCount(\nFilter(\nGenerate(\n ChildrenSet([Engineer].CurrentHierarchyMember),\n Descendants([Engineer].CurrentHierarchyMember, [Engineer].[Engineer])\n ),\n(\n[Measures].[Issues resolved]\n–,--\n–[Status].CurrentHierarchy.DefaultMember–\n)\u003e0\n)\n)\nEND”,“format_string”:""},{“name”:“BJDC 100%”,“dimension”:“Engineer”,“formula”:“Aggregate({\n[Engineer].[Daniel Liu], [Engineer].[Dawei Jiang] ,\n–[Engineer].[Wei Zhao3] , --[Engineer].[Jian Yu] ,\n[Engineer].[Xin Yao],\n–[Engineer].[Pingyuan Teng],\n[Engineer].[Shaotian Yu]\n–, [Engineer].[Jacob Wang]\n})”,“format_string”:""}]
}

2

Hi @Leon,

The main reason your report is slowing down over time is the growing number of issues. The calculated measure “Total estimate hrs of resolution” uses the Descendants() function, which iterates through all the issues in the cube. I would suggest replacing this calculation with a tuple of the Resolution dimension member and the measure “Original estimated hours resolved”. Please have a look at the code below:

([Resolution].[Done],
[Measures].[Original estimated hours resolved])

If you have more than one resolution, you can create a new calculated member in the Resolution dimension and us that in the tuple above.

One other reason for the long loading times is the multiple selections in the pages filter for the Project dimension. With multiple selections or aggregated calculated members in the pages, eazyBI creates multiple reports (for each member in the selection) and then joins them together. This takes time and resources.

Finally, please try to enable the “Nonempty” option in the rows of the report. This is more efficient than the “Hide empty” option in the toolbar. It creates a non-empty cross join of all the dimensions and measures used in the report. Please read more about it on our documentation page - https://docs.eazybi.com/eazybijira/analyze-and-visualize/create-reports#Createreports-Pagedimensions.

Best,
Roberts // eazyBI support