Using TI to build an Alternate Hierarchy

[StevieBenStevie]

I hope this is not too confusing but I have a dimension that gets rebuilt every morning from an odbc source. These are 155,000 pieces of Equipment at the element level rolling up as follows:
Top (1)
MCDs (12)
AGroups (35)
CClasses (1500)
Equipment (155,000+)

...and the new business view (BV) requirement is to also roll the 35 AGroups up to only 4 Alternate MCDs and a new top...
Top-BV (1)
MCD-BV (4)
AGroups (35)
CClasses (1500)
Equipment (155,000+)

So it really just involves ALSO rolling up the 35 AGroups into 4 new parents in an alternate Business View. This below is the Metadata tab that I am trying to add... out of the 4 new MCD-s, 3 are an exact match to the 12 and the fourth is ALL OTHER... so my guess is I will have to script all 35 AGroups into this new hiearchy.

It would be something like: IF(rename_MCD@='_TRUCKS'); THEN MAP INTO THE "ALL OTHER". Can I use % for or statement and say _TRUCKS or _TRAILERS or _WaterBalloons all go into the new ALL OTHER-BV? Is that the right approach? Anybody have any examples of anything like this?

Thanks in advance.
-S


#****Begin: Generated Statements***
rename_MCD='_'|Major_Construction_Description;
skey_EquipmentNumber=Equipment_Number|'-'|skey;
rename_skey='_'|skey;
EQUIP_DESC_SKEY=skey|subst(Description,7,20)|'-'|subst(skey_EquipmentNumber,1,6);
vAlternate_Top='All_Construction_Groups_BV';
vAlternate_All_Other='All Other_BV';
DIMENSIONELEMENTINSERT('Equipment2','',skey_EquipmentNumber,'n');
DIMENSIONELEMENTINSERT('Equipment2','',Category_Class,'c');
DIMENSIONELEMENTINSERT('Equipment2','',Analysis_Description,'c');
DIMENSIONELEMENTINSERT('Equipment2','',All_caption,'c');
DIMENSIONELEMENTINSERT('Equipment2','',rename_MCD,'c');
DIMENSIONELEMENTCOMPONENTADD('Equipment2',Category_Class,skey_EquipmentNumber,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Equipment2',Analysis_Description,Category_Class,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Equipment2',All_caption,rename_MCD,1.000000);
DIMENSIONELEMENTCOMPONENTADD('Equipment2',rename_MCD,Analysis_Description,1.000000);
#****End: Generated Statements****

[lotsaram]

Why do you believe you need an alternate hierarchy? All you need to do is add an additional level for 9 of the 12 MCDs.

There are 12 MCDs A to L, you just need to group D to L under "All Other"
Top
-A
-B
-C
-All Other
--D
--E
--F
--G
-- ...
--L

What's so difficult about that?

[StevieBenStevie]

So...the actual difficulty lies in this fact: the data gives 12 of these parents. I need to figure out how to make the other guys (E,F,G, etc) roll up to ALL OTHER.

[StevieBenStevie]

How does that happen? I need to figure out how to make the other guys (E,F,G, etc) roll up to ALL OTHER. I can add ALL OTHER just fine. But what next? Copy the metadata down below and turn everything into OTHER... but what next?

[lotsaram]

How does that happen? I need to figure out how to make the other guys (E,F,G, etc) roll up to ALL OTHER. I can add ALL OTHER just fine. But what next? Copy the metadata down below and turn everything into OTHER... but what next?

Step 1. COPY the code between the begin & end generated statements lines and PASTE below end generated statements.
Step 2. Go to the variables tab and change all values in the "type" column to "other".
Now you are actually in a position to modify the code with a conditional statement and resolve the issue (Step 3). A little bit more complicated but not much.
You should just need something like this

Code: Select all

IF( MCD @= 'Trucks' % MCD @= 'Trains' % MCD @= 'Busses' ); 
  DIMENSIONELEMENTCOMPONENTADD( cDim, All_caption, MCD,1 );
Else;
  DIMENSIONELEMENTCOMPONENTADD( cDim, 'All Other MCD', MCD,1 );
  DIMENSIONELEMENTCOMPONENTADD( cDim, All_caption, 'All Other MCD',1 );
EndIF;

( to replace the single line (DIMENSIONELEMENTCOMPONENTADD( cDim, All_caption, MCD,1 ); )

[StevieBenStevie]

thanks.