I am new to EazyBI. I am using below calculated measure in my report. But the challenge i am facing is with the performance of the below calculated measure. Most of the time it is giving timeout error.
Can someone please guide me on how to improve the performance of this calculated measure.
This measure calculates the ratio of following
1/ No of times an issue has moved to a specific status
divided by
2/ Count of these issues
Sum(Filter(Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),[Measures].[Transitions to status]>1),[Measures].[Transitions to status])
/
Sum(Filter(Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),[Measures].[Transitions to status]>1),[Measures].[Transitions to status issues count])
Some generic things that helped me speed up my computations:
If you need to calculate some subset (e.g. of issues with specific criteria) to iterate over, you can create a calculated member to compute that subset and then use CalculatedChildrenSet([Issues].[My Pre Filtered Subset]), it seems eazybi precomputes these sets so it is MUCH faster than computing such sets on the fly in a measure. This was had most speed impact in my reports.
If you work on the same sets with different measures in the same report, you can use cache(some computed set) to cache sets and reuse them (reuse means have the same “cache(some computed set)” in another measure in the same report).
In my experience, tuples are often MUCH faster than computing properties over sets yourself, use them if you can.
My reason to compute sets and then compute properties over these sets is to aggregate different kinds of information in one report, so I have a report that has fix versions (milestones) on the left and then for every milestone I compute properties of the jira issues that are in the milestone of the current row (or in milestones that are related to the current row’s milestone as they have the same name prefix). This cannot be expressed with page filters, so I need to construct the sets myself in measures.
Not sure if this will help you, there is not a lot of context given for your computation but I thought this might be helpful to others as well.
I have used following condition to filter out issues where an issue has transitioned from ‘QA Ready=> QA In Progress’ status more than once.
[Measures].[Transitions to status]>1
If I use predefined measures, then I am getting the sum of all the issues even where transition happened only once. What I need is, issues where transition happened more than once.
Other dimensions used are
Page Filters
Time, Project, Fixed Version, Status, Transition
Rows
Issue Type
Here is what you can try to do to calculate results much faster.
Create a new calcualted measure “issue set” using this formula:
This measure is unnecessary to select, but it must exist in the cube, with that specific name. It is caching set of issues with the issue type from each row and will be used in next calculation.
Cache(CASE WHEN
NOT [Project].CurrentHierarchyMember is [Project].CurrentHierarchy.DefaultMember
AND
[Issue].CurrentHierarchyMember is [Issue].CurrentHierarchy.DefaultMember
THEN
--optimized iteration
Generate(
Filter(
--set of relevant issues
Generate(
--set of relevant projects
Filter(
[Issue].[Project].Members,
DefaultContext(([Measures].[Issues created],[Issue].CurrenthierarchyMember,[Project].CurrenthierarchyMember))>0
),
--set of issues in filtered projects
DescendantsSet(
[Issue].CurrentHierarchyMember,
[Issue].[Issue])
),
IIF([Issue Type].CurrentMember.Level.Name = "Issue Type",
[Measures].[Issue type] = [Issue Type].CurrentMember.Name, 1)
),
Cast([Issue].CurrentMember.key as string),
","
)
ELSE
--general iteration from issues
Generate(
Filter(
DescendantsSet(
[Issue].CurrentMember,
[Issue].[Issue]
),
IIF([Issue Type].CurrentMember.Level.Name = "Issue Type",
[Measures].[Issue type] = [Issue Type].CurrentMember.Name, 1)
),
Cast([Issue].CurrentMember.key as string),
","
)
END
)
then use this formula for your final calculated measure.
Sum(
Filter(
[Issue].[Issue].GetMembersByKeys(
[Measures].[issue set] --from step1
),
[Measures].[Transitions to status]>1
),
[Measures].[Transitions to status]
)
/
Sum(
Filter(
[Issue].[Issue].GetMembersByKeys(
[Measures].[issue set] --from step1
),
[Measures].[Transitions to status]>1
),
[Measures].[Transitions to status issues count]
)
This should calculate final results much faster than your previous method.
Thanks Martins for the sample code that you had shared. I had used it and found that the results are still taking time to load but one good thing has surely happened with the report that the report is not failing anymore due to timeout error.
