TM1 Forum

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

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?

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 ?

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.

agree with lotsaram here. It would a nightmare to use

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?

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)

Abinaya wrote:I am using MDX query to filter so that not more than 1000 records are displayed at once

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:
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...

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)

Abinaya wrote:I am using MDX query to filter so that not more than 1000 records are displayed at once

MDX / Dynamic Subsets are 'supported' in TM1 Web, including your version, so it must be something you are doing that is breaking it.

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.

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)}

Abinaya wrote:it's Dynamic subset that's not working.

{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Customer] )}, 0)}, ASC)}

Do you mean that in the Server Explorer, more specifically the Subset Editor, that statement does not work? The syntax is correct though.
Try testing the MDX on a small dimension (and change the dimension name in the MDX if needed).

it works in Server Explorer. It works in small dimension. It fails in TM1 web 9.5.2.

Abinaya wrote:it works in Server Explorer. It works in small dimension. It fails in TM1 web 9.5.2.

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's Dynamic subset that's not working.

{TM1SORT( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Customer] )}, 0)}, ASC)}

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.

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).

tomok wrote: The active form doesn't work because you are pulling back too many rows with that subset.

thanks all. It worked when I reduced the number of rows.

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.