TI process taking too much time to create a subset
-
- 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
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!!!
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
Amit Saxena
India
-
- 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
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
-
- MVP
- Posts: 3654
- 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
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.
-
- 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
Thanks Declan for your response!!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.
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 Saxena
India
-
- 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
Actually all other dimension are at consolidated level, and this one is only at leaf level.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.
Regards,
Amit Saxena
India
Amit Saxena
India
-
- 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
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.
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
-
- 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
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.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.
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
Amit Saxena
India
-
- MVP
- Posts: 3120
- 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
Clever colleague you have
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
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
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: 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
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!!!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
Regards,
Amit Saxena
India
Amit Saxena
India
- PavoGa
- MVP
- Posts: 617
- 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
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:
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.
Code: Select all
sMDX = UNION( {[dimname].currentmember}, FILTER(blah, blah, blah), ALL);
SubsetCreateByMDX(subName, sMDX);
SubsetElementDelete(dimName, subName, 1);
Ty
Cleveland, TN
Cleveland, TN
-
- MVP
- Posts: 3120
- 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
Correct. The MDX is dynamic and gives you the set of elements, or 1 element, or no elements, that satisfies the query.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!!!
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
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: 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
Thanks!! Will take care of it going forward.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:
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.Code: Select all
sMDX = UNION( {[dimname].currentmember}, FILTER(blah, blah, blah), ALL); SubsetCreateByMDX(subName, sMDX); SubsetElementDelete(dimName, subName, 1);
Regards,
Amit Saxena
India
Amit Saxena
India
-
- MVP
- Posts: 3120
- 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
Hello ty,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:
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.Code: Select all
sMDX = UNION( {[dimname].currentmember}, FILTER(blah, blah, blah), ALL); SubsetCreateByMDX(subName, sMDX); SubsetElementDelete(dimName, subName, 1);
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
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: 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
Thanks Wim!!!Wim Gielis wrote: ↑Mon Jun 11, 2018 3:58 pmCorrect. The MDX is dynamic and gives you the set of elements, or 1 element, or no elements, that satisfies the query.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!!!
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.
Regards,
Amit Saxena
India
Amit Saxena
India
- PavoGa
- MVP
- Posts: 617
- 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
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.Wim Gielis wrote: ↑Tue Jun 12, 2018 10:49 pmHello ty,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:
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.Code: Select all
sMDX = UNION( {[dimname].currentmember}, FILTER(blah, blah, blah), ALL); SubsetCreateByMDX(subName, sMDX); SubsetElementDelete(dimName, subName, 1);
Reading this topic, I think that the OP already does this.
Your SubsetCreateByMDX is missing the dimension name as 3rd parameter, though.
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
Cleveland, TN
- PavoGa
- MVP
- Posts: 617
- 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
Wim,PavoGa wrote: ↑Fri Jun 22, 2018 12:48 pmMay 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.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.
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.
Forgive me. I was unaware of the "undocumented" 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
Cleveland, TN
-
- 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
Wim Gielis wrote: ↑Sat Jun 09, 2018 8:29 am Clever colleague you have
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
Amit Saxena
India
-
- MVP
- Posts: 3120
- 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
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
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: 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
Thanks! Got it.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).
Regards,
Amit Saxena
India
Amit Saxena
India