Page 1 of 1

TI process taking too much time to create a subset

Posted: Tue Jun 05, 2018 6:37 am
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!!!

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

Posted: Tue Jun 05, 2018 7:00 am
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.

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

Posted: Tue Jun 05, 2018 7:25 am
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.

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

Posted: Wed Jun 06, 2018 3:28 am
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.

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

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

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

Posted: Wed Jun 06, 2018 6:40 am
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.

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

Posted: Sat Jun 09, 2018 3:40 am
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?

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

Posted: Sat Jun 09, 2018 8:29 am
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

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

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

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

Posted: Mon Jun 11, 2018 12:29 pm
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.

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

Posted: Mon Jun 11, 2018 3:58 pm
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.

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

Posted: Tue Jun 12, 2018 2:50 am
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.

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

Posted: Tue Jun 12, 2018 10:49 pm
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.

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

Posted: Wed Jun 13, 2018 5:16 am
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!!!

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

Posted: Fri Jun 22, 2018 12:48 pm
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.

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

Posted: Tue Jun 26, 2018 2:01 pm
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.

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

Posted: Tue Jul 03, 2018 5:23 am
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?

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

Posted: Tue Jul 03, 2018 6:20 am
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).

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

Posted: Wed Jul 04, 2018 3:14 am
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.