How to set "Set_Expression" containing a large number of objects in complex calculations

Questions & Answers
1

Hi

I have 40,000 objects in Insight. Each object has a name, 4 time attributes, and other 50 attributes. I now need to use 4 time attributes to calculate the number of active days of each object in a specific time period, and then sum them up. If I select a few specific objects, I can now get the correct result:

Sum(
{[Object].[Room].[123],[Object].[Room].[234],[Object].[Room].[345],[Object].[Room].[456],[Object].[Room].[567],[Object].[Room].[678],[Object].[Room].[891]},
IIF(DateCompare(CoalesceEmpty([Measures].[Object Room RentTime],[Measures].[StartTime]), [Measures].[StartTime])= 1, (DateDiffDays([Measures].[Object Room RentTime], [Measures].[EndTime])) + 1, [Measures].[Naturalmoondays])
)

But I cannot perform similar calculations on all results. For example, if I specify the set expression as all objects “{[Object Type].[Room].CurrentMember}”, the calculation result is displayed as the value of “Naturalmoondays”:

Sum(
{[Object Type].[Room].CurrentMember},
IIF(DateCompare(CoalesceEmpty([Measures].[Object Room RentTime],[Measures].[StartTime]), [Measures].[StartTime])= 1, (DateDiffDays([Measures].[Object Room RentTime], [Measures].[EndTime])) + 1, [Measures].[Naturalmoondays])
)

Please help me to tell me what I missed？

2

You would ike to access a set of Objects and filter objects with object type Room there. Then you can apply the calculation on those members.

Here is an example formula:

Sum(
Filter(
Descendants([Object].CurrentHierarchyMember, [Object].CurrentHierarchy.Levels("Object")),
[Measures].[Object type] = "Room"
),
IIF(DateCompare(CoalesceEmpty([Measures].[Object Room RentTime],[Measures].[StartTime]), [Measures].[StartTime])= 1, 
(DateDiffDays([Measures].[Object Room RentTime], [Measures].[EndTime])) + 1, [Measures].[Naturalmoondays])
)

Daina / support@eazybi.com

3

Hi Daina

Thank you for your reply.I tried your suggestion, but got an error:

Query execution timeout of 120 seconds reached.
Please try to make query simpler.

I don’t know if it is related to the performance of the server, because I use the JIRA server version, and the CPU of the server has only 32 cores

4

The formula iterates through all objects and applies the calculation on all objects with the type Room. Detailed level calculations might work slow. However, the report setup is important as well.

Please check an overview of best practices in report creation. While the principles are described for Issue cube, it can be applied to Insight or any cube on that matter.

If there are still problems, please share more details about your insight setup and the report definition via support@eazybi.com

Daina / support@eazybi.com