Indirect variables in TI.

Ideas and tips for enhancing your TM1 application
Post Reply
User avatar
Steve Rowe
Site Admin
Posts: 1767
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Indirect variables in TI.

Post by Steve Rowe » Thu May 05, 2011 7:48 pm

I've not see this raised as a technique any where, it would be appear to be a pretty powerful approach to the generalisation of TIs.

For a while now I've been trying to figure out a way of using something like excels Indirect function in TI and today myself and a colleague, Brendan O'Brien, cracked it. Once you figure it out it's a pretty obvious solution but I've never seen it publicised anywhere so I thought I'd post it, given the nature of TM1 though I'm sure someone will be along to say "I've been doing that for years, I thought everyone knew about this"

Anyway so the (simplified) problem is like this.
My TM1 consolidation system has multiple data feeds and I'm not able to influence the structure of the incoming flat files. Each flat file has the columns in a different order.
They all go into the same cube but I don't want to want to have to write and maintain multiple TIs for each of the structurally different flat files.

When I load the file I need to know which field Cost Centre is in so that I can write the data to the cube, but cost centre could be in any of the fields of the incoming file. I want to be able to do this in 1TI without writing a long IF statement, so I could write something like this to solve the issue

If entity='A';
CostCentre=Field3;
ElseIf entity='B';
CostCentre=Field4;
etc for all the many different entites I need to cope with;
EndIf;

Field3 and Field4 etc are the variables names in the TI relating to each column of the incoming file.

This is not generic however, I would need to edit the TI every time a new entity came on line.

What I wanted to do is to be able to populate a file properties cube so that I can say that in the files for entity A cost centre is Field 3 and for entity B the cost centre is in Field 4.

So in my file properties cube I say
.............Cost Centre
Entity A Field3
Entity B Field4

In the TI it's easy for me to retrieve that Field number as a string.

sFieldRef=CellGetS('Datasource_Properties' , 'Entity A' , 'Cost Centre');

So sFieldRef now contains the name of the TI variable that contains the value that I need to use for Cost Centre.

If I write

sCostCentre = sFieldRef;

Then I just make sCostCentre = 'Field3' which is not what I was after.

Reading the help it would seem that the Expand function ought to solve the problem

sCostCentre = Expand('%sFieldRef%');

This still returns sCostCentre = 'Field3' which close reading of the help is the correct behaviour.

Now comes the sneaky bit! If you nest the Expand statements

sCostCentre = Expand ( '%' | Expand ( '%sFieldRef%') | '%');

You get exactly the behaviour we expect, i.e. sCostcentre=Whatever the value that is in the variable Field3.

In some situations where you are trying to build complex and flexible TI processes I expect this to be a powerful technique.

Hopefully I've explained this so it makes sense!

Cheers,

User avatar
Martin Ryan
Site Admin
Posts: 1952
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: Indirect variables in TI.

Post by Martin Ryan » Thu May 05, 2011 8:41 pm

We need a "like" button, ala facebook, that's a great idea.
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer

ajain86
Community Contributor
Posts: 132
Joined: Thu Oct 15, 2009 7:45 pm
OLAP Product: TM1
Version: 9.4.1 9.5 9.5.1
Excel Version: 2003 2007

Re: Indirect variables in TI.

Post by ajain86 » Thu May 05, 2011 10:39 pm

Your discovery gave me an idea.

I always hate writing a long string of IF statements when I need to check something in variables in a sequence ( v1, v2, v3, v4, v5, v6, v7, and so on...).

I used your idea to make it simpler.

nNum = 1;
While( nNum <= 7 );
sNum = NumberToString( nNum );
IF( EXPAND( '%v' | EXPAND( '%sNum%' ) | '%' ) @= 'Test' );
....
ENDIF;

nNum = nNum + 1;
END;


Thanks for sharing.
Ankur Jain

David Usherwood
Site Admin
Posts: 1321
Joined: Wed May 28, 2008 9:09 am

Re: Indirect variables in TI.

Post by David Usherwood » Fri May 06, 2011 10:17 pm

@Steve, cast your mind back to the original generic version rollover code for our Newport project. Couldn't this technique be relevant for that kind of task? Suppose you'd still need the cellputn for each dimension count.

User avatar
Steve Rowe
Site Admin
Posts: 1767
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Indirect variables in TI.

Post by Steve Rowe » Sat May 07, 2011 8:16 am

Ankur,
Yes also a good use of this approach, already used a few times yesterday in the TIs I'm writing.

David, I can't recall that I'm afraid, it was over 5 years ago :lol: I guess the answer would be probably.

bskalli
Posts: 21
Joined: Wed May 14, 2008 7:53 am

Re: Indirect variables in TI.

Post by bskalli » Wed May 11, 2011 12:21 pm

Hi Steve,

we must be patient in our forum.
I have a similar question one year ago.
Unfortunately, I have used the word Activation This Word can still be found in other
Programming languages ​​(APL, PL1 ..).
Within a week I see your solution under 2 topics.
In my case I have a ascii-file with 60 variables. I stored every helpfull Information in a dimension with attributes.
And now i just need a While + your Expand.

Thanks
Brahim

Christopher Kernahan
Community Contributor
Posts: 147
Joined: Mon Nov 29, 2010 6:30 pm
OLAP Product: Cognos TM1
Version: 10.1
Excel Version: Office 2010

Re: Indirect variables in TI.

Post by Christopher Kernahan » Tue Aug 30, 2011 4:27 pm

Steve,

Just wanted to add my thanks, you've saved me from pulling out all of my hair.

User avatar
Steve Rowe
Site Admin
Posts: 1767
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Indirect variables in TI.

Post by Steve Rowe » Fri May 04, 2012 12:51 pm

I've been meaning to post an update on this.
Ankur posted a use of the nested expand as shown below.
nNum = 1;
While( nNum <= 7 );
sNum = NumberToString( nNum );
IF( EXPAND( '%v' | EXPAND( '%sNum%' ) | '%' ) @= 'Test' );
....
ENDIF;

nNum = nNum + 1;
END;
In this example we don't need to nest the expand as we are only trying to get to the value of a variable (not the value of a variable the identifier of which is itself stored in another variable).

For example to add elements to a dimension in a nice tidy TI sequence.

Code: Select all

sElName1='Destination Instance';
sElName2='Destination Cube';
sElName3='Include ruled items';
sElName4='Refresh Dimensions';
sElName5='Rebuild Dimensions';
sElName6='Recreate Cube';


ixDim=1;
sDimName=sDimName17;
While ( ixDim<=5);
   sElName=Expand ('%sElName' | numbertostring(ixDim) | '%');
   If (Dimix ( sDimName , sElName )=0);
       DimensionElementInsert (sDimName , '', sElName , 'S' );
   EndIf;
  ixDim=ixDim+1;
End;
The help section on Expand appears to have sent many people down a bit a of a blind alley in that it implies that it is only for ODBC statements and so on. It is actually much more powerful even without nesting it.

Cheers,

cfm04
Posts: 13
Joined: Fri Aug 31, 2012 6:30 am
OLAP Product: TM1
Version: 10.1
Excel Version: Excel 2007

Re: Indirect variables in TI.

Post by cfm04 » Thu Apr 11, 2013 9:50 am

During your example you checked values in the If clause.
But did you try to assign values like this?

Code: Select all

ixDim=1;

While ( ixDim<=100);
   Expand ('%sElName' | numbertostring(ixDim) | '%') = numbertostring(ixDim);
     ixDim=ixDim+1;
End;


Thanks
CFM04

User avatar
Steve Rowe
Site Admin
Posts: 1767
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Indirect variables in TI.

Post by Steve Rowe » Sat Apr 13, 2013 10:35 pm

I've never been able to get dynamic variable names to work on the LHS of statements.

Don't forget though that you could build a cube and tear it down on the fly, cubes in this context behave pretty much like an array variable and in a CellPutN you can manipulate the element references pretty much how you like.

http://www.tm1forum.com/viewtopic.php?f=21&t=2691

Cheers,

cfm04
Posts: 13
Joined: Fri Aug 31, 2012 6:30 am
OLAP Product: TM1
Version: 10.1
Excel Version: Excel 2007

Re: Indirect variables in TI.

Post by cfm04 » Wed Apr 17, 2013 7:20 am

thanks for the hint.

Ron Raetz
Posts: 1
Joined: Wed Dec 20, 2017 11:27 pm
OLAP Product: tm1
Version: 10.2.2
Excel Version: 10

Re: Indirect variables in TI.

Post by Ron Raetz » Wed Dec 20, 2017 11:51 pm

This works. I really don't understand the Expand statement, but I followed the example and it did exactly what I wanted. I turned two pages of code into about 10 rows.

At the next TM1 Hall of Fame Induction Ceremony this guy should be featured.

User avatar
Alan Kirk
Site Admin
Posts: 5797
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
Location: Sydney, Australia
Contact:

Re: Indirect variables in TI.

Post by Alan Kirk » Thu Dec 21, 2017 12:33 am

Ron Raetz wrote:
Wed Dec 20, 2017 11:51 pm
This works. I really don't understand the Expand statement, but I followed the example and it did exactly what I wanted. I turned two pages of code into about 10 rows.

At the next TM1 Hall of Fame Induction Ceremony this guy should be featured.
It's Steve, he was inducted years ago.

Expand really isn't very well explained in the manual and IMHO it's not exceptionally well named either. Had I been writing it today I may well have called it something like "Extract" since its function is to extract a value from a variable.

Once you understand that it's relatively easy to use.

(a) A variable is a named "box" in memory which stores a value.
(b) If you know the name of the box in advance, then you have no problem.
(c) The problem arises when you want to figure out the variable name that you need to use based on a loop, or based on other inputs.
(d) For example, suppose that your data source has the variables named vnValue1 to vnValue10, and you don't want to hard code 10 separate lines in your TI to process the values in those 10 variables. Instead you just want to loop from 1 to 10.
(d) It's easy to write a loop which counts from 1 to 10, then just sticks that number on the end of the string 'vnValue' so that on the first loop you get 'vnValue1', on the second loop you get 'vnValue2', on the third one you get 'vnValue3' and so on.
(e) However that just gives you the variable name. To get to what is stored inside that "box", you need a function which takes the variable name, and returns (extracts) what is stored in it. That function is Expand().

You can think of it as drilling into a variable to find out what that variable contains.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

Post Reply