TI process taking too much time to create a subset

Post Reply
amit_hhh
Posts: 15
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 » Tue Jun 05, 2018 6:37 am

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

lotsaram
MVP
Posts: 3069
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: TM1 10.2.2 PA 2.0x
Excel Version: 2010 2013 365
Location: Switzerland

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

Post by lotsaram » 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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

amit_hhh
Posts: 15
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 » Wed Jun 06, 2018 3:28 am

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: 15
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 » Wed Jun 06, 2018 3:30 am

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

amit_hhh
Posts: 15
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 » Sat Jun 09, 2018 3:40 am

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: 1691
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

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

Post by Wim Gielis » 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
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

amit_hhh
Posts: 15
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 » Mon Jun 11, 2018 7:41 am

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
Community Contributor
Posts: 189
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 fixpack 7
Excel Version: 2013
Location: Cleveland, Tennessee

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

Post by PavoGa » 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.
Ty
Cleveland, TN

Wim Gielis
MVP
Posts: 1691
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

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

Post by Wim Gielis » Mon Jun 11, 2018 3:58 pm

amit_hhh wrote:
Mon Jun 11, 2018 7:41 am
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!!!
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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

amit_hhh
Posts: 15
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 » Tue Jun 12, 2018 2:50 am

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: 1691
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

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

Post by Wim Gielis » 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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

amit_hhh
Posts: 15
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 » Wed Jun 13, 2018 5:16 am

Wim Gielis wrote:
Mon Jun 11, 2018 3:58 pm
amit_hhh wrote:
Mon Jun 11, 2018 7:41 am
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!!!
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

Post Reply