Building Conditional Dynamic Hierarchy using TI or MDX

Post Reply
Atif Hameed
Posts: 2
Joined: Mon Mar 23, 2009 9:29 pm
Version: 9.4
Excel Version: 2003

Building Conditional Dynamic Hierarchy using TI or MDX

Post by Atif Hameed »

I am new to the board and I must say that there is a wealth of information here. Looking forward to being an active member.

I have a concept of building a Dynamic Hierarcy using Turbo with the condition that all members with a data value of of 100 or less be lumped into an "Other" Parent.

What I want to know is if this is even possible using TI or MDX.
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Building Conditional Dynamic Hierarchy using TI or MDX

Post by paulsimon »

Atif

A possible approach is as follows:

First of all, decide what you mean by <= 100. This will usually mean <= 100 in a particular cube, selecting base level elements in the dimension where you want to create the hierarchy, eg base level Departments, then selecting a single value in all the other dimensions, eg Version Actual, Period Current Month (The source View can be made dynamic by using a subset eg zCurrMth, which you just update each month), Total Accounts, etc, depending on the dimensions in the cube.

In the following MDX I wanted to select Departments using the HO_Expense Cube, where their Actuals in March 2008 across All Accounts, had a value <= 100.

An MDX approach is something like the following:

Code: Select all

{ FILTER( {TM1SUBSETALL( [Dept] )}, [HO_Expense].([Scenario].[Actual],[Trans_Mth].[T_2008-03],[HO_Expense_Account].[All Accounts],[HO_Expense_Meas].[Val]) <= 100) }
That will give you a Dynamic MDX subset. You can then read that in TI and update your dimension appropriately.

An alternative, without MDX is:

Create a View on the cube - Right click on the cube and select Export as ASCII data, then just save the View and ignore the Export to File option.

The View needs to be set up so that the operation is '<= a' and a value of 100.

I would guess that you would still want to exclude zeros so tick the appropriate box for that in the View. You will however need to keep consolidations if you selected consolidated elements such as Total Accounts.

Select the elements that you decided on above.

Save the View

Create a TI process and select the View that you created as its source (You need to select the cube first then the view).

Read from the View. Ignore all the data except the elements in the dimension that you want to update.

Whether you use an MDX subset or a View as the source, will then want something like the following.

For each element that meets the criteria, de-link it from existing parents.

Code: Select all

# Get the Number of Parents for this element

vNumParents = elparn(vDim,vElem )  ; 

# For each parent, delete the link between
# this element and the parent

vCt = 1 ;
WHILE( vCt <= vNumParents  );
  vParent = elpar(vDim,vElem,vCt) ; 
# Error trap for occasional TM1 abberations
  IF(vParent @<> '' ) ;
    dimensionelementcomponentdelete(vDim,vParent,vElem) ;  
  ENDIF ;
  vCt = vCt + 1 ;
END ;

Code: Select all

dimensionelementinsert(vDim,'',vElem,'n') ;
dimensionelementcomponentadd(vDim,'Other Depts',vElem,1) ;
vDim is a variable you set in the Prolog to the name of the dimension.

The dimensionelementinsert may look odd, but adding an element that already exists does not cause an issue, and in some cases I have found that it is necessary to do this to get TM1 to update the dimension properly.

By the way, I would always call it something like 'Other Depts', instead of just 'Other' as things can get confusing if you have Other in several dimensions.

Regards


Paul Simon
Atif Hameed
Posts: 2
Joined: Mon Mar 23, 2009 9:29 pm
Version: 9.4
Excel Version: 2003

Re: Building Conditional Dynamic Hierarchy using TI or MDX

Post by Atif Hameed »

Paul, Thanks for the solution. I am a bit confused about the following highlighted items and that is partly due to the fact that I am new to turbo.

# Get the Number of Parents for this element

vNumParents = elparn(vDim,vElem ) ; (Is vDim a variable that I have to create and what about vElem

# For each parent, delete the link between
# this element and the parent

vCt = 1 ;
WHILE( vCt <= vNumParents );
vParent = elpar(vDim,vElem,vCt) ;
# Error trap for occasional TM1 abberations
IF(vParent @<> '' ) ;(Is vParent also a variable)
dimensionelementcomponentdelete(vDim,vParent,vElem) ;
ENDIF ;
vCt = vCt + 1 ;
END ;


Also where do I use the following is it in advanced tab ?

dimensionelementinsert(vDim,'',vElem,'n') ;
dimensionelementcomponentadd(vDim,'Other Depts',vElem,1) ;


Thansk in advance for you time and effort.
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Building Conditional Dynamic Hierarchy using TI or MDX

Post by paulsimon »

Atif

Replies merged in below
Atif

A possible approach is as follows:

First of all, decide what you mean by <= 100. This will usually mean <= 100 in a particular cube, selecting base level elements in the dimension where you want to create the hierarchy, eg base level Departments, then selecting a single value in all the other dimensions, eg Version Actual, Period Current Month (The source View can be made dynamic by using a subset eg zCurrMth, which you just update each month), Total Accounts, etc, depending on the dimensions in the cube.

In the following MDX I wanted to select Departments using the HO_Expense Cube, where their Actuals in March 2008 across All Accounts, had a value <= 100.

An MDX approach is something like the following:


Code: Select all
{ FILTER( {TM1SUBSETALL( [Dept] )}, [HO_Expense].([Scenario].[Actual],[Trans_Mth].[T_2008-03],[HO_Expense_Account].[All Accounts],[HO_Expense_Meas].[Val]) <= 100) }


That will give you a Dynamic MDX subset. You can then read that in TI and update your dimension appropriately.

An alternative, without MDX is:

Create a View on the cube - Right click on the cube and select Export as ASCII data, then just save the View and ignore the Export to File option.

The View needs to be set up so that the operation is '<= a' and a value of 100.

I would guess that you would still want to exclude zeros so tick the appropriate box for that in the View. You will however need to keep consolidations if you selected consolidated elements such as Total Accounts.

Select the elements that you decided on above.

Save the View

Create a TI process and select the View that you created as its source (You need to select the cube first then the view).

Read from the View. Ignore all the data except the elements in the dimension that you want to update.

Whether you use an MDX subset or a View as the source, will then want something like the following.

For each element that meets the criteria, de-link it from existing parents.


Code: Select all
# Get the Number of Parents for this element

vNumParents = elparn(vDim,vElem ) ;

# For each parent, delete the link between
# this element and the parent

vCt = 1 ;
WHILE( vCt <= vNumParents );
vParent = elpar(vDim,vElem,vCt) ;
# Error trap for occasional TM1 abberations
IF(vParent @<> '' ) ;
dimensionelementcomponentdelete(vDim,vParent,vElem) ;
ENDIF ;
vCt = vCt + 1 ;
END ;



Code: Select all
dimensionelementinsert(vDim,'',vElem,'n') ;
dimensionelementcomponentadd(vDim,'Other Depts',vElem,1) ;


vDim is a variable you set in the Prolog to the name of the dimension.

The dimensionelementinsert may look odd, but adding an element that already exists does not cause an issue, and in some cases I have found that it is necessary to do this to get TM1 to update the dimension properly.

By the way, I would always call it something like 'Other Depts', instead of just 'Other' as things can get confusing if you have Other in several dimensions.

Regards


Paul Simon
PaulSimon

Posts: 77
Joined: Tue May 20, 2008 9:10 pm
Private message Top
--------------------------------------------------------------------------------
Report this postReply with quote Re: Building Conditional Dynamic Hierarchy using TI or MDX
by Atif Hameed on Tue Mar 24, 2009 9:39 pm

Paul, Thanks for the solution. I am a bit confused about the following highlighted items and that is partly due to the fact that I am new to turbo.

# Get the Number of Parents for this element

vNumParents = elparn(vDim,vElem ) ; (Is vDim a variable that I have to create and what about vElem

vDim is a variable that you would set in the Prolog tab eg vDim = 'MyDim' ; It should be the name of the dimension that you want to update.

vElem will be the name of the variable on the Variables tab that you give to each element on the dimension that comes from your View or MDX subset




# For each parent, delete the link between
# this element and the parent

vCt = 1 ;
WHILE( vCt <= vNumParents );
vParent = elpar(vDim,vElem,vCt) ;
# Error trap for occasional TM1 abberations
IF(vParent @<> '' ) ;(Is vParent also a variable) vParent is the variable set in the statement above ie vParent = elpar(vDim,vElem,vCt) ; Perhaps you are used to variable being declared in advance like in VB? In TI variables are declared just by assigning something to them. If you assign a string, then it is a string variable. If you assign a number then it is a numeric variable. Those are the only two types of variables in TI.
dimensionelementcomponentdelete(vDim,vParent,vElem) ;
ENDIF ;
vCt = vCt + 1 ;
END ;


Also where do I use the following is it in advanced tab ? Yes you need to put this in the Advanced Tabs, specifically on the Meta Data Tab with the Advanced Tabs area.

dimensionelementinsert(vDim,'',vElem,'n') ;
dimensionelementcomponentadd(vDim,'Other Depts',vElem,1) ;

Thansk in advance for you time and effort.
Post Reply