Our Story

Who We Are
BEM Partners Inc. was started with the sole purpose of assisting clients with their Cognos Business Intelligence and Cognos TM1 applications. With this being our primary focus we provided the most experienced resources in Southern California.

BEM Partners Inc.

info@bempartners.com

(619) 839-9250

Top
 

Alpha Sorted Subset Trick in IBM Planning Analytics

BEM Partners Inc.Cognos TM1 Alpha Sorted Subset Trick in IBM Planning Analytics

Alpha Sorted Subset Trick in IBM Planning Analytics

I was recently working on a project were we needed to provided a “Lowest Level” subset on a dimension that would have about 40-50k elements in the subset in IBM Planning Analytics (aka Cognos TM1). This subset had to be alpha sorted so that when users used it in PAX and suppressed zeroes it was easy to sort through their data. Typically the first thing you think about to accomplish this is to create an MDX derived subset that sorts the elements for you:

 

sMDX = ‘{TM1FILTERBYLEVEL( {TM1SORT( {TM1SUBSETALL( [Your Dimension] )}, ASC)}, 0)}’;
SUBSETCREATEBYMDX(sSubset, sMDX, 1);

 

The problem with using MDX subsets is that when two users attempt to run a query at the same time using the same MDX subset the 2nd user that runs the query is put into a wait state until the first query completes. Depending on your application this can create issues for your users. To resolve for this we needed to create a static subset of all lowest level elements that was alpha sorted so that queries would not go into a wait state. Typically in the past to build this subset I would generate a temporary MDX subset that is alpha sorted then parse through this subset in a TI process and insert each element into my static subset. Taking this approach on a dimension with 50k elements was taking way to long.

 

Playing around I found a new trick with just a few lines of code. Generate your MDX derived subset, insert an element into position 1, delete this element out of position 1. TM1 will automatically convert this to a static subset with the inserting of an element. Here is the code:

 

sMDX = ‘{TM1FILTERBYLEVEL( {TM1SORT( {TM1SUBSETALL( [Your Dimension] )}, ASC)}, 0)}’;
SUBSETCREATEBYMDX(sSubset, sMDX, 1);

 

SUBSETELEMENTINSERT(sDimension, sSubset, ‘Dummy Element’, 1);
SUBSETELEMENTDELETE(sDimension, sSubset, 1);

Brandon
No Comments

Leave a Comment