Large Dynamic Subset
-
- Posts: 57
- Joined: Fri Sep 17, 2010 11:57 am
- OLAP Product: TM1
- Version: 9.5.2
- Excel Version: 2003
Large Dynamic Subset
Hi
I am using a large dynamic subset (more than 100, 000 elements and growing) in my active form and it's failing to retrieve the data. The active form has more than 7 columns(TM1RPTROW).Dynamic subset works in smaller queries with just one or two column.
How do I solve this problem?
regards,
Abi
I am using a large dynamic subset (more than 100, 000 elements and growing) in my active form and it's failing to retrieve the data. The active form has more than 7 columns(TM1RPTROW).Dynamic subset works in smaller queries with just one or two column.
How do I solve this problem?
regards,
Abi
-
- Community Contributor
- Posts: 324
- Joined: Mon Jul 02, 2012 9:39 pm
- OLAP Product: TM1
- Version: PAL 2.0.8
- Excel Version: 2016
- Location: Sydney, Australia
Re: Large Dynamic Subset
first question that comes to my mind: why would you have an active form with 100,000 element displaying? what would be the purpose of such report?
-
- Posts: 57
- Joined: Fri Sep 17, 2010 11:57 am
- OLAP Product: TM1
- Version: 9.5.2
- Excel Version: 2003
Re: Large Dynamic Subset
It's kind of Transaction level report where users are given access via Tm1 Web to check the accuracy of the data.
There are more than 100 000 elements because each line of the excel file is unique record.
Let me know any other solution where each line of excel could be retrived ?
There are more than 100 000 elements because each line of the excel file is unique record.
Let me know any other solution where each line of excel could be retrived ?
-
- MVP
- Posts: 3657
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Large Dynamic Subset
Don't even get me started on this. Does response time and useability not factor in the design? This is what drill-through is for.
-
- Community Contributor
- Posts: 324
- Joined: Mon Jul 02, 2012 9:39 pm
- OLAP Product: TM1
- Version: PAL 2.0.8
- Excel Version: 2016
- Location: Sydney, Australia
Re: Large Dynamic Subset
agree with lotsaram here. It would a nightmare to use
-
- Posts: 57
- Joined: Fri Sep 17, 2010 11:57 am
- OLAP Product: TM1
- Version: 9.5.2
- Excel Version: 2003
Re: Large Dynamic Subset
Even though there are so many elements I am using MDX query to filter so that not more than 1000 records are displayed at once.
There are two problems, one is even when filtering large subset is not working.
no dynamic subset is working in TM1 Web 9.5.2. is this not supported?
There are two problems, one is even when filtering large subset is not working.
no dynamic subset is working in TM1 Web 9.5.2. is this not supported?
- qml
- MVP
- Posts: 1094
- Joined: Mon Feb 01, 2010 1:01 pm
- OLAP Product: TM1 / Planning Analytics
- Version: 2.0.9 and all previous
- Excel Version: 2007 - 2016
- Location: London, UK, Europe
Re: Large Dynamic Subset
Is it just me or are these two sentences in direct contradiction with each other?
Abinaya wrote:I am using a large dynamic subset (more than 100, 000 elements and growing)
MDX / Dynamic Subsets are 'supported' in TM1 Web, including your version, so it must be something you are doing that is breaking it.Abinaya wrote:I am using MDX query to filter so that not more than 1000 records are displayed at once
Kamil Arendt
-
- Community Contributor
- Posts: 324
- Joined: Mon Jul 02, 2012 9:39 pm
- OLAP Product: TM1
- Version: PAL 2.0.8
- Excel Version: 2016
- Location: Sydney, Australia
Re: Large Dynamic Subset
Abinaya wrote:
There are two problems, one is even when filtering large subset is not working.
Does mdx compile in the subset editor? If not, then its probably syntax problem...
-
- Posts: 57
- Joined: Fri Sep 17, 2010 11:57 am
- OLAP Product: TM1
- Version: 9.5.2
- Excel Version: 2003
Re: Large Dynamic Subset
qml wrote:Is it just me or are these two sentences in direct contradiction with each other?Abinaya wrote:I am using a large dynamic subset (more than 100, 000 elements and growing)MDX / Dynamic Subsets are 'supported' in TM1 Web, including your version, so it must be something you are doing that is breaking it.Abinaya wrote:I am using MDX query to filter so that not more than 1000 records are displayed at once
What I mean is dimension have more than 100, 000 elements but in TM1 web when it displays, because I am using a filter no more than 1000 records are displayed.
Do you need to install FP3 also to get Dynamic subset work or just 9.5.2 ? I have asked IT to install FP3.
-
- Posts: 57
- Joined: Fri Sep 17, 2010 11:57 am
- OLAP Product: TM1
- Version: 9.5.2
- Excel Version: 2003
Re: Large Dynamic Subset
EvgenyT wrote:Abinaya wrote:
There are two problems, one is even when filtering large subset is not working.
Does mdx compile in the subset editor? If not, then its probably syntax problem...
it's Dynamic subset that's not working.
{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Customer] )}, 0)}, ASC)}
-
- MVP
- Posts: 3121
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Large Dynamic Subset
Do you mean that in the Server Explorer, more specifically the Subset Editor, that statement does not work? The syntax is correct though.Abinaya wrote:it's Dynamic subset that's not working.
{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Customer] )}, 0)}, ASC)}
Try testing the MDX on a small dimension (and change the dimension name in the MDX if needed).
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 57
- Joined: Fri Sep 17, 2010 11:57 am
- OLAP Product: TM1
- Version: 9.5.2
- Excel Version: 2003
Re: Large Dynamic Subset
it works in Server Explorer. It works in small dimension. It fails in TM1 web 9.5.2.
-
- Community Contributor
- Posts: 324
- Joined: Mon Jul 02, 2012 9:39 pm
- OLAP Product: TM1
- Version: PAL 2.0.8
- Excel Version: 2016
- Location: Sydney, Australia
Re: Large Dynamic Subset
Does it work in excel report? If not, then you are breaking your mdx in the report... can you please screendump how you are referencing mdx string in the cell (excel) ?Abinaya wrote:it works in Server Explorer. It works in small dimension. It fails in TM1 web 9.5.2.
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: Large Dynamic Subset
So, you're trying to tell us that 1) this simple MDX expression does not work in an Active Form and 2) this MDX expression is supposed to filter a 100,000+ member dimension down to around 1,000? That doesn't sound plausible to me. That expression simply filters an entire dimension down to it's leaf level elements. You are telling us that your customer dimension has 100,000 members but only 1,000 are actually leaf level elements (customers, I presume) and the other 99,000 are rollups? No way. The active form doesn't work because you are pulling back too many rows with that subset.Abinaya wrote:it's Dynamic subset that's not working.
{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Customer] )}, 0)}, ASC)}
-
- MVP
- Posts: 3657
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Large Dynamic Subset
It sounds to me like the dynamic subset is just to ensure that all new customers are included and returns a list of 100K+ elements and zero suppression in the active form as opposed to the MDX itself is being relied on to reduce the list down to 1K or so elements. That is the problem is not with the MDX subset, it is a limitation in rendering the view.
Presumably what is breaking is a timeout either in generating the view which would be entirely possible given the OP hasn't said anything about the dimensionality and size of the cube, whether any rules are involved, etc. Given the brief description of the business problem this solution is trying to solve it would seem highly likely that other parts of the design may be suspect and not exactly best practice or geared for optimal performance.
Frankly the design is ridiculous. Even if the form works with 1000 rows where does that leave the user? How is a user supposed to make any sense of a "report" with 1000 rows? There are other (and better) ways to deal with data validation. There are other (and better) ways to expose more transactional level data (e.g. by drilling through to an RDBMS view).
Presumably what is breaking is a timeout either in generating the view which would be entirely possible given the OP hasn't said anything about the dimensionality and size of the cube, whether any rules are involved, etc. Given the brief description of the business problem this solution is trying to solve it would seem highly likely that other parts of the design may be suspect and not exactly best practice or geared for optimal performance.
Frankly the design is ridiculous. Even if the form works with 1000 rows where does that leave the user? How is a user supposed to make any sense of a "report" with 1000 rows? There are other (and better) ways to deal with data validation. There are other (and better) ways to expose more transactional level data (e.g. by drilling through to an RDBMS view).
Last edited by lotsaram on Tue Sep 17, 2013 8:09 am, edited 3 times in total.
-
- Posts: 57
- Joined: Fri Sep 17, 2010 11:57 am
- OLAP Product: TM1
- Version: 9.5.2
- Excel Version: 2003
Re: Large Dynamic Subset
thanks all. It worked when I reduced the number of rows.tomok wrote: The active form doesn't work because you are pulling back too many rows with that subset.
-
- MVP
- Posts: 263
- Joined: Fri Jun 27, 2008 12:15 am
- OLAP Product: Cognos TM1, CX
- Version: 9.0 and up
- Excel Version: 2007 and up
Re: Large Dynamic Subset
lotsaram, you are such a killjoy. why did you clear things up, i was so hoping for yet another 270 degree turn in what the actual problem is.
abinaya, please don't tell us how you actually did it. some secrets should remain untold.
abinaya, please don't tell us how you actually did it. some secrets should remain untold.