Putting Expand function to a good use

EvgenyT · Post by **EvgenyT** » Tue Sep 10, 2013 1:36 am

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

macsir · Post by **macsir** » Tue Sep 10, 2013 1:42 am

Thanks for sharing although can be used under good name patterns.

lotsaram · 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.

EvgenyT · Post by **EvgenyT** » Tue Sep 10, 2013 6:14 am

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" ...

David Usherwood · Post by **David Usherwood** » Tue Sep 10, 2013 7:01 am

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 · Post by **dwood09** » Tue Dec 10, 2013 5:32 am

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 · Post by **harrytm1** » Wed Dec 11, 2013 7:28 am

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

JDLove · Post by **JDLove** » Thu Jan 16, 2014 9:57 am

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 · Post by **JDLove** » Thu Jan 16, 2014 1:00 pm

I think I got it working ... I'll test it then post up the TI script for others if they need it
JD

CathyBoots · Post by **CathyBoots** » Fri Apr 27, 2018 2:57 pm

I love this! It just saved me a lot of time

Thank you!

paulsimon · Post by **paulsimon** » Sat Apr 28, 2018 5:02 pm

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

macsir · Post by **macsir** » Mon Apr 30, 2018 12:41 am

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.