Putting Expand function to a good use

Post Reply
EvgenyT
Community Contributor
Posts: 324
Joined: Mon Jul 02, 2012 9:39 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: 2016
Location: Sydney, Australia

Putting Expand function to a good use

Post by EvgenyT »

Hi fellows,

I have seen Expand function mentioned few times before on thos forum and would like to share another technique (that you may find useful)...

Say, we are building a GL hierachies and we have level codes from data source as follow vlevel1code, vlevel2code, vlevel3code and so on. We also have names/aliases: vlevel1name, vlevel2name and so on.

Instead of having to write long block of code to populate Name and Description of those hierarchies, I have turned my eyes onto the EXPAND function to "Expand" elements under these variables to populate aliases in one simple loop... sample code below:

Loop will run through 8 variables to get out vLevel1code, etc and vLevel1Name etc by "Expanding" elements of each variable and assigning appropriate alias.



nIndex = 1;

nLimit = 8;

vLevelCode = Expand ( '%' | Expand ( 'vLevel'|NumberToString(nIndex)|'Code')| '%');

vLevelName = Expand ( '%' | Expand ( 'vLevel'|NumberToString(nIndex)|'Name')| '%');


CellPutS( vLevelCode|' - '|vLevelName, cCube, vLevelCode, 'Code + Description' );


nIndex = nIndex + 1;


END;

Please let me know if its any use to you guys :)


Evgeny
User avatar
macsir
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: Putting Expand function to a good use

Post by macsir »

Thanks for sharing although can be used under good name patterns.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Putting Expand function to a good use

Post by lotsaram »

I frequently do this type of thing if the data source has a separate column for monthly values (especially if there are multiple measures per month, can add up to a lot of columns). It saves a lot of otherwise repetitive code and is mostly less work than the alternative of pivoting the data from columns to rows in SQL with 12 unions.
EvgenyT
Community Contributor
Posts: 324
Joined: Mon Jul 02, 2012 9:39 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: 2016
Location: Sydney, Australia

Re: Putting Expand function to a good use

Post by EvgenyT »

lotsaram wrote:I frequently do this type of thing if the data source has a separate column for monthly values (especially if there are multiple measures per month, can add up to a lot of columns). It saves a lot of otherwise repetitive code and is mostly less work than the alternative of pivoting the data from columns to rows in SQL with 12 unions.
I agree with you lotsaram, "dont code harder, code smarter" ... :lol: :lol: :lol:
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: Putting Expand function to a good use

Post by David Usherwood »

Have you looked at the SQL keyword UNPIVOT?
http://weblogs.sqlteam.com/jeffs/archiv ... pivot.aspx
We've used it when cleaning up the Cognos Planning published tables into a more TM1 friendly format.
dwood09
Posts: 3
Joined: Fri Jun 17, 2011 3:43 am
OLAP Product: TM1
Version: CX 10.1
Excel Version: 2010

Re: Putting Expand function to a good use

Post by dwood09 »

Nice tid-bit. I used the combination expand to get the value of a parameter being passed to a TI process, using variables.

eg I have a paramater for each dimension of a cube from 1 to x say named sInDim1, sInDim2 ...sInDimx. I have used the expand within expand per EvgenyT's original post to scroll through and get the value for each of the params.

thx heaps
harrytm1
Regular Participant
Posts: 226
Joined: Thu Apr 02, 2009 2:51 pm
OLAP Product: IBM Planning Analytics
Version: Latest version
Excel Version: 2003 to 2019

Re: Putting Expand function to a good use

Post by harrytm1 »

Hi,

In the case where the source file contains monthly data in columns, this means we need to load into the corresponding period elements. If we are using continuous time dimension, how would the Expand function work to identify the correct period element to load into? This would mean that the elements must be named in a certain manner.

It would be great if you can share your experience on the approach. Many thanks!

harry
Planning Analytics latest version, including Cloud
JDLove
Posts: 49
Joined: Thu May 21, 2009 1:16 pm
OLAP Product: TM1
Version: 10.1
Excel Version: 2007

Re: Putting Expand function to a good use

Post by JDLove »

Hi

I am thinking about loading from a CSV file that's just like this but its got Year_Month for 6 years so 72 columns.
Is it possible to loop thought and build a string variable then assign this as the Value in the CELLPUTN(Value,'CubeName,Dim1....)

My Import Columns are nicely named Y01_M01,Y01_M02,Y01_M03.....Y06_M12

I am having trouble assigning the string that I build in the loop to the Value Argument in the CELLGETN function.

I am not sure this is even possible...

The below made me think it could be !

Re: Putting Expand function to a good use

Post by lotsaram » Tue Sep 10, 2013 6:12 am
I frequently do this type of thing if the data source has a separate column for monthly values (especially if there are multiple measures per month, can add up to a lot of columns). It saves a lot of otherwise repetitive code and is mostly less work than the alternative of pivoting the data from columns to rows in SQL with 12 unions.
Many thanks
JD
JDLove
Posts: 49
Joined: Thu May 21, 2009 1:16 pm
OLAP Product: TM1
Version: 10.1
Excel Version: 2007

Re: Putting Expand function to a good use

Post by JDLove »

I think I got it working ... I'll test it then post up the TI script for others if they need it
JD
CathyBoots
Posts: 19
Joined: Wed Aug 10, 2016 1:20 pm
OLAP Product: Tm1
Version: 10.2.2
Excel Version: Prof Plus 2013

Re: Putting Expand function to a good use

Post by CathyBoots »

I love this! It just saved me a lot of time :D Thank you!
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: Putting Expand function to a good use

Post by paulsimon »

Hi

An alternative to this approach that I have used in the past is to make the levels conform to a Child-Parent format in SQL. This can be done by UNIONing distinct pairs of each of the levels. The advantage of this approach is that you can then use one generic TI process to build the hierarchy for any dimension, regardless of the number of levels that it has. How efficient this is depends on indexing, the physical structure of the database, etc. For most cases, the number of records is only a few thousand for any one dimension, so for me the ease of maintenance argument wins out. If I decide to build dimensions a different way, then I only have one process to change. Having one process for dimension building ensures that all hierarchical dimensions are built the same way, which reduces training and makes them easier to navigate.

The EXPAND technique is useful for other cases though, such as column data loading, Unless I am missing something, I think that the example might be missing the WHILE statement but I think we all get the point and it is a very useful post.

In a lot of cases I am working with Excel/CSV rather than RDBMS sources for hierarchies so if they decide to define the dimension that way then the EXPAND technique is a good approach. However, I have recently had some success in persuading people to move away from the columns per level approach due to the frequent issue of double counting, eg

L1 L2 L3
1 A X
2 B Y
3 C Z

L1 L2 L3
1 A X
2 A Y
3 C Z

Data is typically updated at the base level, L3 in this case. Someone has moved Y from B to A but forgotten to change the L1 Grandparent. When the dimension is built we end up with L2-A linked to both L1-1 and L1-2, and we get double counting at the L0-All elements level.

People still like to see the multiple column structure, but behind the scenes we now have tabs in the master data spreadsheet with L3 to L2, L2 to L1, and then the master sheet has VLOOKUPs to generate the multi column view, but the key thing is that it is just a view, the underlying data is Child to Parent, which makes the double counting issue less likely to appear.

Regards

Paul Simon
User avatar
macsir
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: Putting Expand function to a good use

Post by macsir »

Yes, I agree. This is exactly the way I am doing for all dimension building processes. Another extra thing I add is, the base table with this multi-column table is still useful in DB or DW, which I don't want to change. I created a view with parent-child pair based on the table, which is loaded as source in TI. In that way, whenever people change the base table, the view is still right after changing.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
Post Reply