Merging of multiple elements

Post Reply
sreesuku
Posts: 35
Joined: Wed Sep 14, 2011 7:45 am
OLAP Product: Cognos TM1
Version: 9.5.2
Excel Version: 2003

Merging of multiple elements

Post by sreesuku » Wed Jul 04, 2018 1:36 pm

Hi All,

I have a requirement to merge multiple Dept IDs in to one based on some matching strings and sum the numeric values.
I have given a preview of my current data below. (I am also Attaching the screen shot of the file for clarity)

Current
Dept X Y P Q N1 N2
1 AA XX PQ MN 10 30
2 AA XX RS OP 20 40
3 BB YY AB GH 10 20
4 BB YY CD HI 20 30
5 BB YY EF JK 30 40

Updated
Dept X Y P Q N1 N2
6 AA XX Def Def 30 70
7 BB YY Def Def 60 90

Here Dept is a dimension, X,Y, P, Q are strings and N1 and N2 numeric measures. All are part of another dimension called 'Measures'.

I need to merge multiple depts in to single one if their X, Y string values are same and put a default value (Def) for P,Q Strings.
I also need to sum the numeric measures and the new element need to be created in dept dimension with a unique ID (which is one more than the current highest dept id)

In this case, IDs 1,2 are combined (as they have same X,Y string combination) to get ID 6 (One more than current highest id 5) and numeric measure 1 and 2 sum of N1 and N2 of Dept IDs 1,2 respectively (10+20 and 30+40)
Similarly IDs 3,4,5 are combined (as they have same X,Y string combination) to get ID 6 (One more than current highest id 6) and numeric measure 1 and 2 sum of N1 and N2 of Dept 3,4,5 respectively (10+20+30 and 20+30+40)

Any guidance on the approach with some sample code will be helpful.
Attachments
Preview of data.JPG
Preview of data.JPG (119.54 KiB) Viewed 449 times

User avatar
PavoGa
Community Contributor
Posts: 262
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0
Excel Version: 2013
Location: Cleveland, Tennessee

Re: Merging of multiple elements

Post by PavoGa » Thu Jul 05, 2018 12:48 pm

I suggest desired outcome may be better served by rethinking your cube structure. It seems that the Dept dim is nothing more than a place holder to collect the numeric measures for the various combinations of X, Y, P & Q and not a real part of an organization structure?

Regardless, my thought is this: create dimensions for X, Y P & Q with their associated elements, add a consolidation to each "All Whatevers" that each element rolls in to. Load the data appropriately and you can create a view that displays the data either at the detail level of X or Y, X, Y and P/Q, etc, etc. If the need for a Dept Id is paramount, then (again not knowing your full requirements) I would consider the cube I have described being used by a TI to create another cube that has the Dept dimension. The Dep dim can be modified on the fly within the same TI to add the required Dept IDs.

While not knowing how much data you are dealing with, if you are dealing with large volumes and the X, Y, P, Q values are used dimensionally in other parts of your model, the cube I described will perform much faster and may be significantly easier to use within a dimensional model.
Ty
Cleveland, TN

sreesuku
Posts: 35
Joined: Wed Sep 14, 2011 7:45 am
OLAP Product: Cognos TM1
Version: 9.5.2
Excel Version: 2003

Re: Merging of multiple elements

Post by sreesuku » Fri Jul 06, 2018 11:22 am

Hi,
Thanks a lot for the reply.

Changing the cube structure or creating a new cube cannot be done in the current structure.
I have given dept ID as an example, its just dimension containing an ID field.

Also the number of numeric and string measures which are getting captured are more. We have 200 plus such items, I have only listed 4 to make things clear. So making all of them to dimensions is not feasible.

any other suggestions are welcome

Wim Gielis
MVP
Posts: 1829
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Merging of multiple elements

Post by Wim Gielis » Fri Jul 06, 2018 11:47 am

sreesuku,

Please post your actual process code, following the guidelines for posting (http://www.tm1forum.com/viewtopic.php?f=3&t=1037)
You can't expect someone to write all this for you, nor that people guess or write pseudo-code.
Please indicate what results you obtain and what is wrong/correct in these results.
You joined this board 7 years ago, we can expect at least some level of TI knowledge.

Thanks.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

Post Reply