Hi All,
Is anyone else getting performance issues with CAFE when pulling anything that contains a Dynamic Subset?
Never had this prior this current fix pack, now if the View or List object contains a Dynamic Subset it either times out or takes a extraordinaryly long time to retreive data.
CAFE under 10.2.2 FP3
-
- Posts: 25
- Joined: Wed Nov 12, 2014 2:27 pm
- OLAP Product: TM1 + BI
- Version: 10.2 10.2.2 + 10.2.1 10.2.2
- Excel Version: 2010 32 bit
Re: CAFE under 10.2.2 FP3
Hi,
What do you refer by saying View object, is it flex view or exploration view(crosstab)?
I just made a test with FP3 on Excel 2013 and I did not encounter such performance issues in either flex view or exploration view which include dynamic subsets in rows and/or columns and/or context. Note that, in my test case, mdx statements of dynamic subsets are not much complicated and also the data is not heavy.
I cannot guess what could be the performance issue with dynamic subset as once you created a flex view from a crosstab/view with a dynamic subset in the rows, then the elements in rows do not have dynamic behavior anymore because they do not change by time.
If you would like, you can post the detailed use case along with your example mdx and I can try to replicate the issue.
What do you refer by saying View object, is it flex view or exploration view(crosstab)?
I just made a test with FP3 on Excel 2013 and I did not encounter such performance issues in either flex view or exploration view which include dynamic subsets in rows and/or columns and/or context. Note that, in my test case, mdx statements of dynamic subsets are not much complicated and also the data is not heavy.
I cannot guess what could be the performance issue with dynamic subset as once you created a flex view from a crosstab/view with a dynamic subset in the rows, then the elements in rows do not have dynamic behavior anymore because they do not change by time.
If you would like, you can post the detailed use case along with your example mdx and I can try to replicate the issue.
-
- Community Contributor
- Posts: 219
- Joined: Mon Jul 23, 2012 8:31 am
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2010
Re: CAFE under 10.2.2 FP3
From my testing yesterday, here are the latest observations:
The MDX used:
The result of this particular MDX will pull a list of close to 200,000 Accounts from our GL.
Populating a List with a Dynamic Subset Using the "List"
Method 1:
If I drag & drop the Subset from the Cognos Side box on the a new List, the query can take up to 5mins+ (or time out).
Method 2:
If I drag the Account Hierarchy over to the List, then edit & insert the MDX above, the query takes a few seconds.
If I do the same with a Crosstab and pull a pre-existing view that has Dynamic Subsets, it will take a long time to resolve. By recreating the View by pasting the MDX of each Dimension that needs it, it will calculate in seconds again (i.e. on par with a Perspectives Query).
The MDX used:
Code: Select all
TM1SORT(HIERARCHIZE(TM1FILTERBYLEVEL(TM1SUBSETALL([Account]) , 0)) , ASC)
Populating a List with a Dynamic Subset Using the "List"
Method 1:
If I drag & drop the Subset from the Cognos Side box on the a new List, the query can take up to 5mins+ (or time out).
Method 2:
If I drag the Account Hierarchy over to the List, then edit & insert the MDX above, the query takes a few seconds.
If I do the same with a Crosstab and pull a pre-existing view that has Dynamic Subsets, it will take a long time to resolve. By recreating the View by pasting the MDX of each Dimension that needs it, it will calculate in seconds again (i.e. on par with a Perspectives Query).
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: CAFE under 10.2.2 FP3
Why would you try and hierarchize a set of elements that are all at the same level?RJ! wrote:The MDX used:Code: Select all
TM1SORT(HIERARCHIZE(TM1FILTERBYLEVEL(TM1SUBSETALL([Account]) , 0)) , ASC)
Over the 200,000 elements eliminating this overhead may make a noticeable difference.
Robin Mackenzie
-
- Community Contributor
- Posts: 219
- Joined: Mon Jul 23, 2012 8:31 am
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2010
Re: CAFE under 10.2.2 FP3
Our Account list have a lot of concatenations of Account + other data to allow us as much flexibility as possible with our models.
I rejigged the MDX to filter for an attribute that will scrub a lot of the "noise" from the list and it's now close to 12,000 lines.
Note that we are getting the same performance issues for smaller Dynamic Subsets (i.e. < 100 returned results)
I rejigged the MDX to filter for an attribute that will scrub a lot of the "noise" from the list and it's now close to 12,000 lines.
Note that we are getting the same performance issues for smaller Dynamic Subsets (i.e. < 100 returned results)