TI: Dynamic Subset as Datasource

[Mark2007]

Dear all,
I have got a problem when copying one dimension to another.
TM1 Version 10.2.1.

In the Prolog, I define the Datasource:

sSub = 'z_akt_';
sDim = psQuellDim;
smdx = '{TM1SUBSETALL( [' | psQuellDim | '] )}';
subsetdestroy(sDim, sSub);
subsetcreatebymdx( sSub, smdx);
DatasourceNameForServer = psQuellDim;
DatasourceDimensionSubset = sSub;

The Source-Dimension has about 30.000 Elements.
With this datasource, the Meta-Part is through (here I insert all elements from the sourcedim) in about 1 Second.

In Data, I want to copy the attributes with the following code:

anzAttr=DIMSIZ('}ElementAttributes_'| psQuelldim);
While(anzAttr>0);
Attributname=DIMNM('}ElementAttributes_'| psQuelldim,anzAttr);
AttrType=DTYPE('}ElementAttributes_'| psQuelldim,Attributname);

IF(AttrType@='AA' % AttrType@='AS');
AttrInhaltS=ATTRS(psQuellDim, vsElem,Attributname);
IF(DIMIX(psZielDim, vsElem) >0);
ATTRPUTS(AttrInhaltS, psZielDim, vsElem,Attributname);
ENDIF;
ENDIF;
anzAttr=AnzAttr-1;
END;

This takes sevaral Minutes!
If I remove the dynamic datasource (marked bold above) and take the subset "All" as datasource, Copying the attributes in Data is as fast copying the elements in Meta.

Does anyone have a idea, why?

Regards

Mark

[declanr]

Dynamic subsets for TI processes being slow is discussed extensively if you search the forum.
Just remove the last element from the subset and then add it back on straight after your subsetcreatebymdx in the prolog. You then get the exact same subset of elements to work with but it is now static, your process will then be much quicker.

[hyunjia]

Dynamic subsets for TI processes being slow is discussed extensively if you search the forum.
Just remove the last element from the subset and then add it back on straight after your subsetcreatebymdx in the prolog. You then get the exact same subset of elements to work with but it is now static, your process will then be much quicker.

That sounds interesting , I would alwasy avoid using dynamic subset as data source to minimize locking issue , but I haven't realized it would actuall has a performance impact . To extend this topic , I am very curious to know if you have experienced any slow peroformance when using a view as datasource where it 's using dynamic subsets ?

Cheers

[Wim Gielis]

Hello

To be honest, I never experienced slower performance due to dynamic subsets as being the source of a process, compared to static subsets.

Maybe I can test some cases if anyone has a scenario where it's evident, so that I can reproduce it?

[declanr]

Dynamic subsets for TI processes being slow is discussed extensively if you search the forum.
Just remove the last element from the subset and then add it back on straight after your subsetcreatebymdx in the prolog. You then get the exact same subset of elements to work with but it is now static, your process will then be much quicker.

That sounds interesting , I would alwasy avoid using dynamic subset as data source to minimize locking issue , but I haven't realized it would actuall has a performance impact . To extend this topic , I am very curious to know if you have experienced any slow peroformance when using a view as datasource where it 's using dynamic subsets ?

Cheers

To be honest I was mostly referring to my experience with cube views as sources; when I need a subset as a source I tend to just do while loops through it and avoid using the Metadata/Data tabs (no specific reason, just habit.)
It can largely be dependent on what you do but changes to data can cause the Dynamic Subset to be re-evaluated, causing the performance hit.

[lotsaram]

Hello

To be honest, I never experienced slower performance due to dynamic subsets as being the source of a process, compared to static subsets.

Maybe I can test some cases if anyone has a scenario where it's evident, so that I can reproduce it?

Wim your experience <> my experience. I think at some point I even built a test case model and it is posted here in some thread somewhere showing that processing with a dynamic subset as the source is orders of magnitude slower than a static source if any action on the metadata or data tabs should cause the subset to be reevaluated.

[Wim Gielis]

Hi Lotsaram

With my statement, I was referring to the typical data-integration processes (sending data from 1 cube to the same cube or a different one).
I did not think about processes that update the dimension in their Metadata tab, for example. In that case, you're right.
Making the dimension static (an MDX statement with the correct elements plus the DFRST of the dimension, which is then eliminated to make it static) is preferable.
Both the power and condensed notation of MDX, and avoiding slowness like you wrote down correctly.

[hyunjia]

Good point . In that case , dynamic subset should be avoided defitnitly .

Hello

To be honest, I never experienced slower performance due to dynamic subsets as being the source of a process, compared to static subsets.

Maybe I can test some cases if anyone has a scenario where it's evident, so that I can reproduce it?

Wim your experience <> my experience. I think at some point I even built a test case model and it is posted here in some thread somewhere showing that processing with a dynamic subset as the source is orders of magnitude slower than a static source if any action on the metadata or data tabs should cause the subset to be reevaluated.