Subset using index

[dharav]

Hi, All

I notice following while testing my TI process:

My all other TI code remain same except the code to create the subset.

=> When i use following code, MY TI takes 1 min 30 seconds.

IF( SubsetExists( pDim, sSub) = 1 );
SubsetDestroy( pDim, sSub);
ENDIF;

SubsetCreateByMDX(sSub,'{TM1FILTERBYLEVEL( {TM1SUBSETALL( [1h_PCHierarchy])}, 0)}');


=> When i use this code my TI process takes less than 3 seconds.

IF( SubsetExists( pDim, sSub) = 1 );
SubsetDestroy( pDim, sSub);
ENDIF;


vMax=DIMSIZ(pDim);
k=1;

SubsetCreate(pDim,sSub);

While (k<=vMax);

v10=DIMNM(pDim,k);

IF (ELLEV(pDim,v10)=0);

z10=SubsetGetSize(pDim,sSub);

z100=z10+1;

SubsetElementInsert(pDim,sSub,v10,z100);

ENDIF;

K=K+1;

END;

#================================================================================================================================
Just curious to know if there is fundamental concept laying behind it. Is there any draw back to use indexation if we looping all elements?

Please feel free if you require further information.

Thank You

Dharav

[tomok]

The second way is more than likely faster because you are using a static subset in your data source rather than a dynamic one, like with the MDX. The best of both worlds would be to use MDX to create a static subset. Just use this syntax:

Code: Select all

SubsetMDXSet(pDim, sSub, '{TM1FILTERBYLEVEL( {TM1SUBSETALL( [1h_PCHierarchy])}, 0)}')

[PavoGa]

What exactly is your process doing? If you are reading and writing within the same cube (and in some instances inter-cube) the CELLPUT may destroy the read cache and causes the MDX to recalculate which greatly slows down the process. The second method you are using is building a static subset, so it never changes.

Here is what I do when creating an MDX subset that converts an MDX subset to a static subset like your second example:

Code: Select all

strMDX = 'Union(
     {[dimname].currentmember]}
     , TM1FILTERBYLEVEL( [TM1SUBSETALL( [dimname] ), 0)
      , ALL)';
   SubsetCreateByMDX(subName, strMDX);
   SubsetElementDelete(dimName, subName, 1);
   

This has an added advantage of the SubsetCreateByMDX will not fail if the MDX does not return anything. There may be some newer functionality in fixpacks later than 10.2.2 which I'm on, but this does work 100% of the time to take advantage of MDX and then converting it immediately to a static subset to alleviate any performance issues.

One additional thought is if you are reading and writing from and to cubes, write to a text file and then read it back in to do the write.

[PavoGa]

Tom beat me to the click, but what he recommends is much cleaner.

Tom, I've not used that function. I assume it handles MDX that does not return a set without bombing?

[dharav]

The second way is more than likely faster because you are using a static subset in your data source rather than a dynamic one, like with the MDX. The best of both worlds would be to use MDX to create a static subset. Just use this syntax:

Code: Select all

SubsetMDXSet(pDim, sSub, '{TM1FILTERBYLEVEL( {TM1SUBSETALL( [1h_PCHierarchy])}, 0)}')

@Tomok: Thank You for the prompt response:

The above function is exists in 10.3 only

http://www.ibm.com/support/knowledgecen ... dxset.html

@PavoGa:

Thank You for the response. I used to convert the dynamic subset in to static subset same way you explained.
Looping elements and create static subset in a TI makes TI faster in processing time than making dynamic subset to static subset.

Thank You Guys

[lotsaram]

The above function is exists in 10.3 only

Dear chap, just because IBM didn't succeed in adding the function to the documentation before 10.3 doesn't mean the function isn't already there and working!

[Wim Gielis]

Indeed, SubsetMDXSet is available as of TM1 10.2.2 FP4. See the documentation http://www-01.ibm.com/support/docview.w ... wg27042401.

[Guillaume Galtier]

Hi,

I'm on 10.2.2FP1 and SubsetMDXSet is also available.

Using the syntax SubsetMDXSet(DimName, SubsetName, '') on an existing dynamic subset, the subset becomes static.

But using the syntax SubsetMDXSet(DimName, SubsetName, MDX Expression), the target subset remains dynamic (the MDX is attached to the subset).

Is it a bug? May be corrected in the next versions?

[PavoGa]

Yes, the functions are available in 10.2.2 FP1 and have started using it.

Thank You for the response. I used to convert the dynamic subset in to static subset same way you explained.
Looping elements and create static subset in a TI makes TI faster in processing time than making dynamic subset to static subset.

I'm not sure what you meant here because the conversion to a static subset is virtually instantaneous.

Ty

[Wim Gielis]

Indeed, SubsetMDXSet is available as of TM1 10.2.2 FP4. See the documentation http://www-01.ibm.com/support/docview.w ... wg27042401.

Apologies, I was confused with 10.2.2 FP4 where the temporary variants of SubsetCreate, SubsetCreateByMDX, ... were added.