Question on Hierarchy

[manu0521]

HI Tm1 Experts,
I wanted to ask this question on how you guys would address this scenario

I have a custoemr dimension and it ah following levels

Account Territory Area Agency Region

so I build a hierarchy like

Region 001
Agency1
Area1
Territory1
Account1

Accounts are always unique , some times what happens there is an issue in source which will create something like this

Region 002
Agency1
Area2
Territory2
Account2

An acccount will have an agency but that could belong to a different region .

so in tm1 it will add agency1 under 2 regions 001 and 002 .


so to avoid this we always had to concatenate the levels together as it will be unique

something like
Region1
Region1-Agency1
Region2
Region2 - Agency1

the above works well and also identifies the wrong assignments and since we build the whole dimension by removing the hierarchy and putting back the elements.

But the issue here is the descriptions are too long , users were requesting if they just have agency 1 on level2 , but then it will create 2 entries in ur case when regions are different .I will not 100% no if the elements all will have a unique parent .


Now the request from user is to have an alias atleast which will give the level alone name and they can build charts based on that.
But even for alias has to be unique right .in my case for

Region1-Agency1
Region2-Agency1 both will have an alias name Agency1 wich will still throw error ,only way is i add an attribute ,but not sure how easy will be for them to use in chart.

How do you guys handle this .

Thanks,

[declanr]

If your users work with an interface that allows the use of alternate hierarchies I would scrap the current set up and replace it with a dimension that just contains the core roll ups - which sounds to be location based info. And then an additional hierarchy linking the n-level element to an Agency (you might need more than 1 alternate hierarchy depending on how things are set up to you.)

With your current set up as it is, you can’t give them the alias that they want (due to the fact it needs to be unique as you have highlighted.)
If the setup must stay the same then your best bet would probably be to do something with a small random(ish) code concatenated with the description that they want.
E.g rather than Region 1 - Agency 1 you could have R1 - Agency 1 etc.

Or depending on what the user actually NEEDS... do they really need an alias? If it’s just charts they need then how are they building the charts? If it’s in excel they can do it with an string attribute instead of an alias and then they don’t need to be unique.

[PavoGa]

We have pretty much the same issue and while you could do it the way you talking with unique consolidation elements, we do an extended variation of alternate hierarchies as mentioned by Declan. In our case, the business plans/forecasts at a higher level than individual accounts and cost centers and as some of those are driver based on actuals, we need to be able to consolidate the detailed components in order to plan at the higher level.

At the same time, they are constantly tweaking the components of their planning elements, which are uniquely named/aliased. But they also want to see a historical comparison (what did the planning elements look like before this change?).

We do this with alternate hierarchies and the two dimensions with this requirement have multiple additional hierarchies. We have some fairly complicated hierarchy management to ensure our views and processes use the correct hierarchies for whatever task is at hand, but have a half dozen or so utility processes with parameters to control what all we wish to have done.

[manu0521]

HI thanks for the suggestions , can you tell me a little bit detail on how to deal this with alternate hierarchy with one simple example .
So does the core part gets mainitained in one dimension .

[declanr]

It's pretty quick and dirty but if you hook up the below code to the attached CSV it should give you an idea of what you could do.

Code: Select all

#Section Prolog
#****Begin: Generated Statements***
#****End: Generated Statements****

#-----------------------------------------------------
# Set Constants and Variable
#-----------------------------------------------------

cDimName = 'Account';
cHierName = 'Agency';
cTopElement = 'All Accounts';
cTopHierarchy = 'All Agencies';

#-----------------------------------------------------
# Create Dimension
#-----------------------------------------------------

If ( DimensionExists ( cDimName ) = 0 );
	DimensionCreate ( cDimName);
Else;
	# Replace with unwind in real world
	DimensionDeleteAllElements ( cDimName);
EndIf;
DimensionElementInsert ( cDimName, '', cTopElement, 'C' );
DimensionSortOrder ( cDimName,'ByName', 'Ascending', 'ByHierarchy', 'Descending' );

#-----------------------------------------------------
# Create Hierarchy
#-----------------------------------------------------

If ( HierarchyExists ( cDimName, cHierName ) = 0 );
	HierarchyCreate ( cDimName, cHierName );
Else;
	# Replace with unwind in real world
    HierarchyDeleteAllElements ( cDimName, cHierName );
EndIf;
HierarchyElementInsert ( cDimName, cHierName, '', cTopHierarchy, 'C');
HierarchySortOrder ( cDimName, cHierName, 'ByName', 'Ascending', 'ByHierarchy', 'Descending' );



#Section Metadata
#****Begin: Generated Statements***
#****End: Generated Statements****

#-----------------------------------------------------
# Add Elements to Dimension
#-----------------------------------------------------

DimensionElementInsert ( cDimName, '', vsAccount, 'N' );
DimensionElementInsert ( cDimName, '', vsTerritory, 'C' );
DimensionElementInsert ( cDimName, '', vsRegion, 'C' );

DimensionElementComponentAdd ( cDimName, cTopElement, vsRegion, 1 );
DimensionElementComponentAdd ( cDimName, vsRegion, vsTerritory, 1 );
DimensionElementComponentAdd ( cDimName, vsTerritory, vsAccount, 1 );


#-----------------------------------------------------
# Add Elements to Hierarchy
#-----------------------------------------------------

HierarchyElementInsert ( cDimName, cHierName, '', vsAgency, 'C');
HierarchyElementInsert ( cDimName, cHierName, '', vsAgencyRegion, 'C');
HierarchyElementInsert ( cDimName, cHierName, '', vsAccount, 'N');

HierarchyElementCOmponentAdd ( cDimName, cHierName, cTopHierarchy, vsAgencyRegion, 1 );
HierarchyElementCOmponentAdd ( cDimName, cHierName, vsAgencyRegion, vsAgency, 1 );
HierarchyElementCOmponentAdd ( cDimName, cHierName, vsAgency, vsAccount, 1 );


#Section Data
#****Begin: Generated Statements***
#****End: Generated Statements****

#Section Epilog
#****Begin: Generated Statements***
#****End: Generated Statements****

You will want to review the hierarchy functions:
https://www.ibm.com/docs/en/planning-an ... -functions


Essentially it just works like building an additional dimension but the hierarchy "shares" the leaf elements with the main dimension. This way you can view a cube with all of a dimensions hierarchies to get a full break down but you can also choose to ignore that they exist all together.
This comes in very handy when doing planning vs reporting where the requirements for how you want to view the data are often different.

The easiest example of this that I often use to explain is a Time dimension and how you want to display data.
To get some charts to display how you want you may need a single time dimension with Month and Year combined.
But for other charts you may want to have Month and Year as separate dimensions (e.g. if you have a line chart and you want a line for each year.)
This is where hierarchies can really prove beneficial.