TI process taking too much time to create a subset

Post Reply
amit_hhh
Posts: 28
Joined: Wed Jan 18, 2017 3:27 pm
OLAP Product: Cognos TM1
Version: 10.2.2, 11
Excel Version: Excel2010
Location: Bangalore, India

TI process taking too much time to create a subset

Post by amit_hhh »

Hi All,

I have created a TI to export cube data. In prolog, I am creating the source cube view by creating subsets for different dimensions of the cube. There is a dimension A whose .dim file size is around 4 MB. This means it has millions of records since the dimension is updated daily from database. I am taking all leaf level elements of this dimension in subset in prolog and calling generic TI available at bedrock.org to create the subset. While monitoring TM1 Top utility, the parent TI executes sys_subset_create generic TI for more than 15 min to create subset for the dimension which seems obvious as it has so many records.
Is there a way I can reduce the TI execution time while creating subset?
Looking forward for response!!!
Regards,
Amit Saxena
India
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: TI process taking too much time to create a subset

Post by declanr »

Assuming that your source view has skip consolidations enabled just dont create a subset and for that dimension leave the cube view with no subset assigned, it will default to just taking every element and due to having skip consolidations ticked it would only be n level elements.
Declan Rodger
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: TI process taking too much time to create a subset

Post by lotsaram »

Why are you doing this to start with? As normal practice you would export leaf cells with null suppression turned on. Therefore for any dimension where you have no need to apply a filter (e.g. you want all leaf elements) then you simply do not assign a subset for these dimensions as there is no need to do so as the view filters take care of this and are alone sufficient.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
amit_hhh
Posts: 28
Joined: Wed Jan 18, 2017 3:27 pm
OLAP Product: Cognos TM1
Version: 10.2.2, 11
Excel Version: Excel2010
Location: Bangalore, India

Re: TI process taking too much time to create a subset

Post by amit_hhh »

declanr wrote: Tue Jun 05, 2018 7:00 am Assuming that your source view has skip consolidations enabled just dont create a subset and for that dimension leave the cube view with no subset assigned, it will default to just taking every element and due to having skip consolidations ticked it would only be n level elements.
Thanks Declan for your response!!
The export source view has all other elements of dimension at consolidated level, and this particular dimension at leaf. So skipping consolidation is not working in this case.
Regards,
Amit Saxena
India
amit_hhh
Posts: 28
Joined: Wed Jan 18, 2017 3:27 pm
OLAP Product: Cognos TM1
Version: 10.2.2, 11
Excel Version: Excel2010
Location: Bangalore, India

Re: TI process taking too much time to create a subset

Post by amit_hhh »

lotsaram wrote: Tue Jun 05, 2018 7:25 am Why are you doing this to start with? As normal practice you would export leaf cells with null suppression turned on. Therefore for any dimension where you have no need to apply a filter (e.g. you want all leaf elements) then you simply do not assign a subset for these dimensions as there is no need to do so as the view filters take care of this and are alone sufficient.
Actually all other dimension are at consolidated level, and this one is only at leaf level.
Regards,
Amit Saxena
India
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: TI process taking too much time to create a subset

Post by declanr »

Ok, then I would do a bit of trial and error testing on 2 things:
1/ just create your subset with a simple while loop in your actual TI instead of bedrock
2/ just create the subset with MDX in your actual TI instead of bedrock - make static in the prolog as well
3 / leave the dimensions aith no subset for the view so all elements are picked, then add an if statement to check dtype in data and meta tabs then itemskip if it is not ‘N’

Depending on how many consolidations there are in your dimension I would expect that the itemskip might be your quickest option.
Also worth noting that bedrock TIs from what I have seen contain a lot of logging code etc that you probably will never use, its probably well written code but as you are seeing - if you have a big source and it performs 20 steps on each element when you only want it to do 1 thing.... well obviously it will take longer.
Declan Rodger
amit_hhh
Posts: 28
Joined: Wed Jan 18, 2017 3:27 pm
OLAP Product: Cognos TM1
Version: 10.2.2, 11
Excel Version: Excel2010
Location: Bangalore, India

Re: TI process taking too much time to create a subset

Post by amit_hhh »

declanr wrote: Wed Jun 06, 2018 6:40 am Ok, then I would do a bit of trial and error testing on 2 things:
1/ just create your subset with a simple while loop in your actual TI instead of bedrock
2/ just create the subset with MDX in your actual TI instead of bedrock - make static in the prolog as well
3 / leave the dimensions aith no subset for the view so all elements are picked, then add an if statement to check dtype in data and meta tabs then itemskip if it is not ‘N’

Depending on how many consolidations there are in your dimension I would expect that the itemskip might be your quickest option.
Also worth noting that bedrock TIs from what I have seen contain a lot of logging code etc that you probably will never use, its probably well written code but as you are seeing - if you have a big source and it performs 20 steps on each element when you only want it to do 1 thing.... well obviously it will take longer.
I tried the 2nd option to create subset by MDX and made it static in prolog, and execution time reduced into seconds. Thanks for that.
But I asked my colleague about how to make dynamic subset static in prolog, and she suggested to insert an element in the subset and delete it in the next line of code, just after SubsetCreateByMDX statement. This solution worked, but can't figure out how this logic is working, neither could she.

SubsetCreateByMDX(...);
SubsetElementInsert(..N...);
SubsetElementDelete(..N..);

any idea if this is the correct approach to make a dynamic subset as static?
Regards,
Amit Saxena
India
Wim Gielis
MVP
Posts: 3103
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: TI process taking too much time to create a subset

Post by Wim Gielis »

Clever colleague you have 8-)

The approach is correct. I think it’s logical that whenever you have a dynamic subset and you insert or delete an element, the dynamic character is gone. TM1 takes the set of elements that are in the subset at that time and continues working on it in a static way. If you do the same action manually in the Subset Editor you have the same behavior. Or would you expect the MDX definition of a dynamic subset to keep on expanding every time you make a change to it ?

An alternative TI function is SubsetMDXSet.

See:
http://www.wimgielis.com/tm1_neatmdxtricks_EN.htm
http://www.wimgielis.com/tm1_subsetcrea ... set_EN.htm
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
amit_hhh
Posts: 28
Joined: Wed Jan 18, 2017 3:27 pm
OLAP Product: Cognos TM1
Version: 10.2.2, 11
Excel Version: Excel2010
Location: Bangalore, India

Re: TI process taking too much time to create a subset

Post by amit_hhh »

Wim Gielis wrote: Sat Jun 09, 2018 8:29 am Or would you expect the MDX definition of a dynamic subset to keep on expanding every time you make a change to it ?

An alternative TI function is SubsetMDXSet.

See:
http://www.wimgielis.com/tm1_neatmdxtricks_EN.htm
http://www.wimgielis.com/tm1_subsetcrea ... set_EN.htm
Yes, I expect the dynamic subset to expand since the dimension is getting updated daily with elements. I think since I am creating a temporary view in prolog and deleting it in epilog, I believe this solution will keep the subset updated everyday when TI executes. Your opinion/ advice is highly appreciated!!!
Regards,
Amit Saxena
India
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: TI process taking too much time to create a subset

Post by PavoGa »

Here is a possible helpful hint for dealing with MDX subsets going forward: if an MDX statement with filters does not return any members, then SubsetCreateByMDX will fail. One thing to do in order to guarantee the MDX statement will compile (assuming of course the statement is syntactically correct) is to do this:

Code: Select all

sMDX = UNION( {[dimname].currentmember}, FILTER(blah, blah, blah), ALL);
SubsetCreateByMDX(subName, sMDX);
SubsetElementDelete(dimName, subName, 1);
This way, if the MDX filters do not return any valid elements, the TI does not bomb. May not DO anything, but it will not error out and provides you with a static subset as well.
Ty
Cleveland, TN
Wim Gielis
MVP
Posts: 3103
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: TI process taking too much time to create a subset

Post by Wim Gielis »

amit_hhh wrote: Mon Jun 11, 2018 7:41 amYes, I expect the dynamic subset to expand since the dimension is getting updated daily with elements. I think since I am creating a temporary view in prolog and deleting it in epilog, I believe this solution will keep the subset updated everyday when TI executes. Your opinion/ advice is highly appreciated!!!
Correct. The MDX is dynamic and gives you the set of elements, or 1 element, or no elements, that satisfies the query.
But since you do not want to have TM1 evaluate that subset with every change, it's best to make it static for the duration of your process.
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
amit_hhh
Posts: 28
Joined: Wed Jan 18, 2017 3:27 pm
OLAP Product: Cognos TM1
Version: 10.2.2, 11
Excel Version: Excel2010
Location: Bangalore, India

Re: TI process taking too much time to create a subset

Post by amit_hhh »

PavoGa wrote: Mon Jun 11, 2018 12:29 pm Here is a possible helpful hint for dealing with MDX subsets going forward: if an MDX statement with filters does not return any members, then SubsetCreateByMDX will fail. One thing to do in order to guarantee the MDX statement will compile (assuming of course the statement is syntactically correct) is to do this:

Code: Select all

sMDX = UNION( {[dimname].currentmember}, FILTER(blah, blah, blah), ALL);
SubsetCreateByMDX(subName, sMDX);
SubsetElementDelete(dimName, subName, 1);
This way, if the MDX filters do not return any valid elements, the TI does not bomb. May not DO anything, but it will not error out and provides you with a static subset as well.
Thanks!! Will take care of it going forward.
Regards,
Amit Saxena
India
Wim Gielis
MVP
Posts: 3103
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: TI process taking too much time to create a subset

Post by Wim Gielis »

PavoGa wrote: Mon Jun 11, 2018 12:29 pm Here is a possible helpful hint for dealing with MDX subsets going forward: if an MDX statement with filters does not return any members, then SubsetCreateByMDX will fail. One thing to do in order to guarantee the MDX statement will compile (assuming of course the statement is syntactically correct) is to do this:

Code: Select all

sMDX = UNION( {[dimname].currentmember}, FILTER(blah, blah, blah), ALL);
SubsetCreateByMDX(subName, sMDX);
SubsetElementDelete(dimName, subName, 1);
This way, if the MDX filters do not return any valid elements, the TI does not bomb. May not DO anything, but it will not error out and provides you with a static subset as well.
Hello ty,

Reading this topic, I think that the OP already does this.
Your SubsetCreateByMDX is missing the dimension name as 3rd parameter, though.
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
amit_hhh
Posts: 28
Joined: Wed Jan 18, 2017 3:27 pm
OLAP Product: Cognos TM1
Version: 10.2.2, 11
Excel Version: Excel2010
Location: Bangalore, India

Re: TI process taking too much time to create a subset

Post by amit_hhh »

Wim Gielis wrote: Mon Jun 11, 2018 3:58 pm
amit_hhh wrote: Mon Jun 11, 2018 7:41 amYes, I expect the dynamic subset to expand since the dimension is getting updated daily with elements. I think since I am creating a temporary view in prolog and deleting it in epilog, I believe this solution will keep the subset updated everyday when TI executes. Your opinion/ advice is highly appreciated!!!
Correct. The MDX is dynamic and gives you the set of elements, or 1 element, or no elements, that satisfies the query.
But since you do not want to have TM1 evaluate that subset with every change, it's best to make it static for the duration of your process.
Thanks Wim!!!
Regards,
Amit Saxena
India
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: TI process taking too much time to create a subset

Post by PavoGa »

Wim Gielis wrote: Tue Jun 12, 2018 10:49 pm
PavoGa wrote: Mon Jun 11, 2018 12:29 pm Here is a possible helpful hint for dealing with MDX subsets going forward: if an MDX statement with filters does not return any members, then SubsetCreateByMDX will fail. One thing to do in order to guarantee the MDX statement will compile (assuming of course the statement is syntactically correct) is to do this:

Code: Select all

sMDX = UNION( {[dimname].currentmember}, FILTER(blah, blah, blah), ALL);
SubsetCreateByMDX(subName, sMDX);
SubsetElementDelete(dimName, subName, 1);
This way, if the MDX filters do not return any valid elements, the TI does not bomb. May not DO anything, but it will not error out and provides you with a static subset as well.
Hello ty,

Reading this topic, I think that the OP already does this.
Your SubsetCreateByMDX is missing the dimension name as 3rd parameter, though.
May have missed it, but do not see where the OP was using the UNION statement. This was a tip for going forward, not a specific response to the initial post. By including the UNION in the MDX, one does not have to insert an element in order to convert the set to static by deleting that same added element. The UNION statement as illustrated always puts the element to be deleted in position 1. Although not much, it is more compact and guarantees 100% compilation of the MDX when a FILTER may possibly return an empty subset, subsequently causing an error in the TI.

Also, the SubsetCreateByMDX does not require the dimension name as a parameter. The third (optional) argument is just the flag designating temporary subset or not.
Ty
Cleveland, TN
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: TI process taking too much time to create a subset

Post by PavoGa »

PavoGa wrote: Fri Jun 22, 2018 12:48 pm
Wim Gielis wrote: Tue Jun 12, 2018 10:49 pm

Hello ty,

Reading this topic, I think that the OP already does this.
Your SubsetCreateByMDX is missing the dimension name as 3rd parameter, though.
May have missed it, but do not see where the OP was using the UNION statement. This was a tip for going forward, not a specific response to the initial post. By including the UNION in the MDX, one does not have to insert an element in order to convert the set to static by deleting that same added element. The UNION statement as illustrated always puts the element to be deleted in position 1. Although not much, it is more compact and guarantees 100% compilation of the MDX when a FILTER may possibly return an empty subset, subsequently causing an error in the TI.

Also, the SubsetCreateByMDX does not require the dimension name as a parameter. The third (optional) argument is just the flag designating temporary subset or not.
Wim,

Forgive me. I was unaware of the "undocumented" :D third parameter for SubsetCreateByMDX to ensure an error is not returned when an MDX statement does not return a valid subset.

I had experimented with SubsetMDXGet and SubsetMDXSet several years ago. Do not recall results from then, but for some reason, stuck with the UNION and SubsetElementDelete to get a static subset regardless of whether the MDX returned any elements or not.
Ty
Cleveland, TN
amit_hhh
Posts: 28
Joined: Wed Jan 18, 2017 3:27 pm
OLAP Product: Cognos TM1
Version: 10.2.2, 11
Excel Version: Excel2010
Location: Bangalore, India

Re: TI process taking too much time to create a subset

Post by amit_hhh »

Wim Gielis wrote: Sat Jun 09, 2018 8:29 am Clever colleague you have 8-)

The approach is correct. I think it’s logical that whenever you have a dynamic subset and you insert or delete an element, the dynamic character is gone. TM1 takes the set of elements that are in the subset at that time and continues working on it in a static way. If you do the same action manually in the Subset Editor you have the same behavior. Or would you expect the MDX definition of a dynamic subset to keep on expanding every time you make a change to it ?

An alternative TI function is SubsetMDXSet.

See:
http://www.wimgielis.com/tm1_neatmdxtricks_EN.htm
http://www.wimgielis.com/tm1_subsetcrea ... set_EN.htm

Hi Wim,

I am running into the same issue. When I execute the TI for the first time in the day, It is still taking minutes to complete, but after it's first execution, export TI finishes within seconds. Why does this irrational behavior happen? Are data values stored somewhere in cache after first execution?
Regards,
Amit Saxena
India
Wim Gielis
MVP
Posts: 3103
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: TI process taking too much time to create a subset

Post by Wim Gielis »

Indeed, TM1 caches data for faster retrieval later on, unless the cache is invalidated because you load a new value in a cell (that is part of the cache).
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
amit_hhh
Posts: 28
Joined: Wed Jan 18, 2017 3:27 pm
OLAP Product: Cognos TM1
Version: 10.2.2, 11
Excel Version: Excel2010
Location: Bangalore, India

Re: TI process taking too much time to create a subset

Post by amit_hhh »

Wim Gielis wrote: Tue Jul 03, 2018 6:20 am Indeed, TM1 caches data for faster retrieval later on, unless the cache is invalidated because you load a new value in a cell (that is part of the cache).
Thanks! Got it.
Regards,
Amit Saxena
India
Post Reply