Simplifying EazyBI Queries

[J.M.]

As I expand and navigate the capabilities of EazyBI I am running into issues trying to add in more complex calculations and scale data. I'm not sure the purpose of the 60 second timeout. If my report is just taking time, let it take time? and cache the results on my side so it doesn't need to re-traverse the cube until the next import. I most likely don't appreciate the amount of system churn that causes but it's frustrating to run into as a user, especially a user with little MDX knowledge.

That's my 2 cents as someone who enjoys this tool and wants it to expand.

In the meantime, since MDX is not a strong suite of myself and many I'd like to submit some of my report definitions to see where I can get the desired results in a more efficient way.

• Created vs Resolve - Lots of projects

{
"cube_name": "Issues",
"cube_reports": [ {
"name": "Created vs resolved issues-EdTech",
"result_view": "line_chart",
"definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Average resolution days]","[Measures].[Issues created]","[Measures].[Issues resolved]","[Measures].[Open issues]","[Measures].[Rolling Average cycle time]"],"members":null}]},"rows":{"dimensions":[{"name":"Time","selected_set":["[Time].[Month].Members"],"members":null,"bookmarked_members":null}],"filter_by":{"conditions":[{"expression":"[Time].CurrentHierarchyMember","operator":"between","value":"90 days ago and today","value_type":"date"}]},"nonempty_crossjoin":true},"pages":{"dimensions":[{"name":"Project","selected_set":["[Project].[All Projects]"],"members":[{"depth":0,"name":"All Projects","full_name":"[Project].[All Projects]","drillable":true,"type":"all","expanded":true,"drilled_into":false},{"depth":1,"name":"EdTech Analytics","full_name":"[Project].[EdTech Analytics]","drillable":true,"key":"EAN","parent_full_name":"[Project].[All Projects]"},{"depth":1,"name":"EdTech Back Office","full_name":"[Project].[EdTech Back Office]","drillable":true,"key":"BOF","parent_full_name":"[Project].[All Projects]"},{"depth":1,"name":"EdTech Connections","full_name":"[Project].[EdTech Connections]","drillable":true,"key":"CON","parent_full_name":"[Project].[All Projects]"},{"depth":1,"name":"EdTech Content Management (ECM)","full_name":"[Project].[EdTech Content Management (ECM)]","drillable":true,"key":"ECM","parent_full_name":"[Project].[All Projects]"},{"depth":1,"name":"EdTech Core Shared Services","full_name":"[Project].[EdTech Core Shared Services]","drillable":true,"key":"COR","parent_full_name":"[Project].[All Projects]"},{"depth":1,"name":"EdTech EAQ","full_name":"[Project].[EdTech EAQ]","drillable":true,"key":"EAQ","parent_full_name":"[Project].[All Projects]"},{"depth":1,"name":"EdTech Evolve","full_name":"[Project].[EdTech Evolve]","drillable":true,"key":"EVL","parent_full_name":"[Project].[All Projects]"},{"depth":1,"name":"EdTech HESI High Stakes","full_name":"[Project].[EdTech HESI High Stakes]","drillable":true,"key":"HHS","parent_full_name":"[Project].[All Projects]"},{"depth":1,"name":"EdTech HESI PS&R","full_name":"[Project].[EdTech HESI PS&R]","drillable":true,"key":"HPSR","parent_full_name":"[Project].[All Projects]"},{"depth":1,"name":"EdTech NCO","full_name":"[Project].[EdTech NCO]","drillable":true,"key":"ENCO","parent_full_name":"[Project].[All Projects]"},{"depth":1,"name":"EdTech Recommender","full_name":"[Project].[EdTech Recommender]","drillable":true,"key":"REC","parent_full_name":"[Project].[All Projects]"},{"depth":1,"name":"EdTech Sherpath","full_name":"[Project].[EdTech Sherpath]","drillable":true,"key":"SHER","parent_full_name":"[Project].[All Projects]"},{"depth":1,"name":"EdTech Simulations","full_name":"[Project].[EdTech Simulations]","drillable":true,"key":"SIMS","parent_full_name":"[Project].[All Projects]"},{"depth":1,"name":"MedEd","full_name":"[Project].[MedEd]","drillable":true,"key":"MED","parent_full_name":"[Project].[All Projects]"},{"depth":1,"name":"MedEd Test ","full_name":"[Project].[MedEd Test ]","drillable":true,"key":"MT","parent_full_name":"[Project].[All Projects]"}],"bookmarked_members":null,"current_page_members":["[Project].[EdTech Analytics]","[Project].[EdTech Back Office]","[Project].[EdTech Connections]","[Project].[EdTech Content Management (ECM)]","[Project].[EdTech Core Shared Services]","[Project].[EdTech EAQ]","[Project].[EdTech Evolve]","[Project].[EdTech HESI High Stakes]","[Project].[EdTech HESI PS&R]","[Project].[EdTech NCO]","[Project].[EdTech Recommender]","[Project].[EdTech Sherpath]","[Project].[EdTech Simulations]","[Project].[MedEd]","[Project].[MedEd Test ]"]},{"name":"Issue Type","selected_set":["[Issue Type].[All Issue Types]"],"members":[{"depth":0,"name":"All Issue Types","full_name":"[Issue Type].[All Issue Types]","drillable":true,"type":"all","expanded":true,"drilled_into":false},{"depth":1,"name":"Task","full_name":"[Issue Type].[Task]","parent_full_name":"[Issue Type].[All Issue Types]"},{"depth":1,"name":"Technical task","full_name":"[Issue Type].[Technical task]","parent_full_name":"[Issue Type].[All Issue Types]"},{"depth":1,"name":"Technical Story","full_name":"[Issue Type].[Technical Story]","parent_full_name":"[Issue Type].[All Issue Types]"},{"depth":1,"name":"Bug","full_name":"[Issue Type].[Bug]","parent_full_name":"[Issue Type].[All Issue Types]"},{"depth":1,"name":"Support Defect","full_name":"[Issue Type].[Support Defect]","parent_full_name":"[Issue Type].[All Issue Types]"},{"depth":1,"name":"Story Defect","full_name":"[Issue Type].[Story Defect]","parent_full_name":"[Issue Type].[All Issue Types]"},{"depth":1,"name":"Support Request","full_name":"[Issue Type].[Support Request]","parent_full_name":"[Issue Type].[All Issue Types]"},{"depth":1,"name":"Design","full_name":"[Issue Type].[Design]","parent_full_name":"[Issue Type].[All Issue Types]"},{"depth":1,"name":"Story","full_name":"[Issue Type].[Story]","parent_full_name":"[Issue Type].[All Issue Types]"}],"bookmarked_members":null,"current_page_members":["[Issue Type].[Task]","[Issue Type].[Technical task]","[Issue Type].[Technical Story]","[Issue Type].[Bug]","[Issue Type].[Support Defect]","[Issue Type].[Story Defect]","[Issue Type].[Support Request]","[Issue Type].[Design]","[Issue Type].[Story]"]}]},"options":{},"view":{"current":"line_chart","maximized":false,"line_chart":{"area":false,"swap_axes":false,"data_labels":false,"series_options":{"Issues created":{"type":"column","color":"#DC7369"},"Issues resolved":{"type":"column","color":"#AAC458"},"Average resolution days":{"separateAxis":true,"color":"#6795C4","type":"areaspline"},"Open issues":{"separateAxis":true,"color":"#8B76A1","dataLabelType":"top rotated"}}},"table":{}}}
} ],
"calculated_members": [{"dimension":"Measures","name":"Open issues","format_string":"#,##0","formula":"CASE WHEN [Issue].CurrentMember.Level.Name <> 'Issue' THEN\n Cache(\n NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n Cache([Measures].[Issues created]\n - [Measures].[Issues resolved])\n ))\n + [Measures].[Issues created]\n - [Measures].[Issues resolved]\n )\nWHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember\nTHEN NonZero([Measures].[Issues due])\nELSE\n -- optimized formula for drill through Issue\n NonZero(IIF(\n DateBeforePeriodEnd(\n [Issue].CurrentMember.get('Created at'),\n [Time].CurrentHierarchyMember) AND\n NOT DateBeforePeriodEnd(\n [Issue].CurrentMember.get('Resolved at'),\n [Time].CurrentHierarchyMember),\n ([Time].CurrentHierarchy.DefaultMember,\n [Measures].[Issues created]),\n 0\n ))\nEND","annotations":{"group":"Default"}},{"dimension":"Measures","name":"Average resolution days","format_string":"#,##0.00","formula":"CASE WHEN [Measures].[Issues resolved] > 0 THEN\n [Measures].[Total resolution days] / [Measures].[Issues resolved]\nEND","annotations":{"group":"Default"}},{"dimension":"Measures","name":"Issue resolution date","format_string":"mmm dd yyyy","formula":"[Issue].CurrentHierarchyMember.get('Resolved at')","annotations":{"group":"Issue properties"}},{"name":"Rolling Average cycle time","dimension":"Measures","formula":"IIF(\n [Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember,\n ([Measures].[Days in transition status],\n [Transition Status.Category].[In Progress],\n [Status.Category].[Done])\n /\n [Measures].[Issues resolved] ,\n -- calculation per period on issue level for resolved issues only\n NonZero(AVG(\n Filter(\n Descendants([Issue].CurrentMember, [Issue].[Issue]),\n DateInPeriod(\n [Measures].[Issue resolution date],\n [Time].CurrentHierarchyMember\n )\n AND\n [Measures].[Issues resolved] > 0),\n ([Measures].[Days in transition status],\n [Transition Status.Category].[In Progress],\n [Status.Category].[Done],\n [Time].CurrentHierarchy.DefaultMember)\n )\n )\n)","format_string":""}]
}

• 
  Rolling average cycle time, 1 project, filtering out issues by type and labels

{
"cube_name": "Issues",
"cube_reports": [ {
"name": "CON Avg. Resolution Days",
"result_view": "line_chart",
"definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Rolling Average cycle time]","[Measures].[Standard deviation cycle days]"],"members":null}]},"rows":{"dimensions":[{"name":"Time","selected_set":["[Time.Weekly].[Week].Members"],"members":null,"bookmarked_members":null}],"filter_by":{"conditions":[{"expression":"[Time.Weekly].CurrentHierarchyMember","operator":"between","value":"3 months ago and today","value_type":"date"}]},"nonempty_crossjoin":true},"pages":{"dimensions":[{"name":"Project","selected_set":["[Project.Category].[Education].[EdTech Connections]"],"members":[{"depth":2,"name":"EdTech Connections","full_name":"[Project.Category].[Education].[EdTech Connections]","drillable":true,"key":"CON"}],"bookmarked_members":null,"current_page_members":["[Project.Category].[Education].[EdTech Connections]"]},{"name":"Issue Type","selected_set":["[Issue Type].[All Issue Types]"],"members":[{"depth":0,"name":"All Issue Types","full_name":"[Issue Type].[All Issue Types]","drillable":true,"type":"all","expanded":true,"drilled_into":false},{"depth":1,"name":"Task","full_name":"[Issue Type].[Task]","parent_full_name":"[Issue Type].[All Issue Types]"},{"depth":1,"name":"Story","full_name":"[Issue Type].[Story]","parent_full_name":"[Issue Type].[All Issue Types]"},{"depth":1,"name":"Technical Story","full_name":"[Issue Type].[Technical Story]","parent_full_name":"[Issue Type].[All Issue Types]"},{"depth":1,"name":"Bug","full_name":"[Issue Type].[Bug]","parent_full_name":"[Issue Type].[All Issue Types]"}],"bookmarked_members":null,"current_page_members":["[Issue Type].[Task]","[Issue Type].[Story]","[Issue Type].[Technical Story]","[Issue Type].[Bug]"]},{"name":"Label","selected_set":["[Label].[All Labels]","[Label].[No Outliers]"],"members":[{"depth":0,"name":"All Labels","full_name":"[Label].[All Labels]","drillable":true,"type":"all","expanded":true,"drilled_into":false},{"depth":0,"name":"No Outliers","full_name":"[Label].[No Outliers]","calculated":true,"dimension":"Label","formula":"[Label].DefaultMember - [Label].[reporting-outlier]","format_string":"","drillable":null,"annotations":null}],"bookmarked_members":null,"current_page_members":["[Label].[No Outliers]"]}]},"options":{},"view":{"current":"line_chart","maximized":false,"line_chart":{"area":false,"swap_axes":false,"data_labels":false,"series_options":{"Issues resolved":{"type":"scatter","separateAxis":1,"dataLabelType":false}}},"table":{}}}
} ],
"calculated_members": [{"dimension":"Measures","name":"Issue resolution date","format_string":"mmm dd yyyy","formula":"[Issue].CurrentHierarchyMember.get('Resolved at')","annotations":{"group":"Issue properties"}},{"name":"Standard deviation cycle days","dimension":"Measures","formula":"[Measures].[Rolling Average cycle time]+\nCache(NonZero(Stdev(\n Filter(\n Descendants([Issue].CurrentMember, [Issue].[Issue]),\n DateInPeriod(\n [Measures].[Issue resolution date],\n [Time].CurrentHierarchyMember\n )\n AND\n [Measures].[Issues resolved] > 0),\n ([Measures].[Days in transition status],\n [Transition Status.Category].[In Progress],\n [Status.Category].[Done],\n [Time].CurrentHierarchy.DefaultMember)\n )\n))","format_string":"#,##0.00"},{"name":"Rolling Average cycle time","dimension":"Measures","formula":"IIF(\n [Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember,\n ([Measures].[Days in transition status],\n [Transition Status.Category].[In Progress],\n [Status.Category].[Done])\n /\n [Measures].[Issues resolved] ,\n -- calculation per period on issue level for resolved issues only\n NonZero(AVG(\n Filter(\n Descendants([Issue].CurrentMember, [Issue].[Issue]),\n DateInPeriod(\n [Measures].[Issue resolution date],\n [Time].CurrentHierarchyMember\n )\n AND\n [Measures].[Issues resolved] > 0),\n ([Measures].[Days in transition status],\n [Transition Status.Category].[In Progress],\n [Status.Category].[Done],\n [Time].CurrentHierarchy.DefaultMember)\n )\n )\n)","format_string":""},{"name":"No Outliers","dimension":"Label","formula":"[Label].DefaultMember - [Label].[reporting-outlier]","format_string":""}]
}

[Fabian Lim]

Hi @J.M.

You can update the timeout in the advanced settings. Here is the link: https://docs.eazybi.com/eazybi/set-up-and-administer/atlassian-server-and-data-center/advanced-settings

Regards