Alternate Hierarchies - Use Cases
The topic of Alternate Hierarchies is very interesting for me, as we are exploring this topic as a future development for the product
We are looking to build functionality that will allow multiple parents off a single child member and specify which hierarchies should be used in which part of the model
This will obviously save a lot of the complexity and mapping, as well as saving model space, because the data does not need to be repeated for the child member.
It is also very relevant for our new calculation engine as difference levels within hierarchies are a key component of the calculations needing to be performed
To help me capture a full set of use cases please could you reply with examples of how and why this functionality would help you (i.e. industry, use case, complexity etc.)
I have a lot "in my head" already, but it will be great to validate those against the field requirements
Many thanks in advance
Yes! Here are a couple use cases. This would be a huge win and save customers that use a data hub an immense amount of time.
First, let me preface with another limitation that should be considered and that is the inability to "push" data. By definition, any synchronization from the data hub will now require two processes on two separate dashboards (or NewUX pages). While automation is always a possibility, this is not practical with administration (e.g., master data management inside Anaplan). If you want all the pitfalls and "gotchas" of having to sync alternate hierarchies, reach out to me privately. I'll give you an earful. Also, I have documented all the steps required to sync DH to spoke for hierarchies that are administrated in the DH and synchronized in the spoke applications. It's a lot more work than most people realize, especially if you have all the proper auditing working.
Use Case #1 | Call Centers
Call centers, large ones, are usually broken up into smaller groups that are organized by the types of calls to be received (product or service) and by the skillset of the call agent. This would be the grain of the hierarchy. From there, there are several requirements to have this lowest level report into multiple parents. The first is a hierarchy that is used for forecasting demand. The second is a hierarchy that is used for reporting, usually by a product or service aggregation. The demand hierarchy is used to allocate budget across the service desks and/or 3rd parties. The product hierarchy is used for reporting and integration with the GL. Note: there sometimes is a third aggregation which is based on location to determine required headcount.
Use Case #2 | Retail
The lowest product level for retail is different for most retailers but generally, it is SKU for hardlines, and style-color for softlines. By the way, if the retailer needs both hardlines and soflines, there are additional hierarchy challenges. What typically happens from here is that this lowest level will have 20-50 properties assigned to them, with some of them having further aggregations (alternate hierarchies). The most typical example is a traditional hierarchy where product rolls up by product categories, like class, department, division, etc.. Then things start getting hard. For apparel, there is a concept of a season. Seasons are not time properties but rather, product properties. For example, a sweater belongs to the spring collection, or spring season. These collections are used in wholesale as "modules" that are used in sales to their customers. There might be an intermediary level called "delivery" which suggests a type of condition needed for floorsets and/or contractual obligations made with the manufacturer. For hardlines, there is sometimes a vendor rollup needed which may have multiple hierarchy levels. For example, buying imported goods vs domestic for the same product. As you can imagine the list is rather large.2
Data movements and "push" are not part of my product stream, but I am aware these are needed to facilitate cross model data movements and feeds from Data Hubs.
I will feed these in to the relevant streams
Hello David @DavidSmith
I happen to utilize alternate hierarchies quite a bit in my last project. I always disliked the fact that I had to duplicate a list, not only for model size perspective but also from a maintenance point; I had to ensure I have processes to synchronize the alternate list(s) with the original list. Below are 2 examples of when I actually used alternate hierarchies in my last project (Real Estate Investment Portfolio Management)
Business Case: Report and analyze financial and non-financial data of a Real Estate hotel investment Portfolio.
Data: We were given a master list of the Hotel Assets with various attributes such as:
- Brand (Sheratorm Hilton,.....)
- Type (Extended stay,....
- Market ( Airport, suburban, Urban,.....)
And a few other attributes
The natural hierarchy was Hotel --> portfolio
That's how we get the monthly actuals for each hotel and then calculated the financial P&L as well as non-financial data such as #rooms, Occupancy Rate, POR, PAR,... etc
The user wanted to see how the assets are performing based on some of the attributes mentioned above (Brand, Type, Market,.....) not only based on the managing portfolio
Alternate hierarchies came in handy here. I had one calculation module dimensioned by the Hotels, yet I was able to create multiple reporting modules/dashboards dimension by the alt lists without repeating the underlying P&L calculations.
Use Case: Quarterly Board Reporting of fund performance based on a hierarchy different from the hierarchy used by the financial team; Tying the financial performance of the Board report with that of the finance/accounting report.
An Asset could be acquired under a particular deal (Deal #), yet during the life of the fund, assets could move from one deal to another for reporting fund managers' performance comparisons for example. From an accounting perspective, the asset still exists under the original deal.
Again, we used the original hierarchy in the Accounting module and used an alternate hierarchy that group the assets under the appropriate reporting deal in the Quarterly Board model.
If you need any further clarifications/information about the details of the use cases please let me know2
A supply chain/procurement use case that I have seen an example of this is when a SKU should be under both a category and a supplier. Many procurement departments might group this under a category and assign a buyer to it at that level, but it is also critical to know what the total spend/SKU's look like from a supplier perspective. Right now, this is usually done by choosing the important one, and then the less important of the roll-ups just becomes a reporting hierarchy.
In the Trade and Promotion world, Most promotions are assigned to a retailer or a group of retailers, but they also have a type of promotion. In some cases depending on the requirements, we might have a type of promotion rolling up into a retailer, and that same couple of promotion types would be duplicated for each retailer. It would be nice to be able to roll those up elsewhere and still be able to segregate them by the supplier as well.
On the SPM side, similar use case where accounts might role up to an Account type, Region, Account Size, and a Rep. Obviously depending on some of these would be better suited as a property, but having the ability to have an account role-up a couple of places would cut down on the duplicative nature of some of the reporting created here.
A couple of other random thoughts/questions I have (and don't expect you to have answers on right now):
- How would Sync-ing on a page work? You could have multiple hierarchies displayed on a page, and syncing could get a little weird.
- How would selective access work? In my first example, there could be reasons to assign selective access to different roles, on different hierarchies.
I am sure I could come up with several other examples of where this would be a big-time help. Looking forward to this one when and if it comes to fruition!
Great stuff, keep 'em coming, and @jasonblinn good call out on the practical aspects of them. Definitely will include those areas in the thinking as we progress
Alternate hierarchies enable natural rollup of items that may depend upon the business or functional area:
- Geo - The Minneapolis office is in the north central sales region, the eastern professional services area, the americas administrative team, ... but in the G/L, the transactions are all coded to Minneapolis.
- Products and services rollup differently for procurement, for manufacturing, for sales, and for marketing.
- Cost center rollups in FP&A often have "prior org", "current org" and "planned re-org" hierarchies to allow comparison and restatement before / after reorg. In matrix organizations, Cost Centers may also need to roll up both according to management responsibility and by function.
- Accounts - alternate rollups in an accounts hierarchy can be used as a means of mapping accounts to summary report lines using the parent-child relationship.
Picklists - I'm not coming up with any examples off the top of my head, but there may be situations where alternate drill paths to attributes are useful. These would be attributes that sum, but not things like currencies since rollup of euros and pounds makes no sense.
Time - We sometime use alt-time lists for special situations. Fiscal Year for accounting vs Calendar Year for tax. The odd situation where a company changes their fiscal year end. (I've suffer through that one. It's a mess.)
Drilling - We currently address alt hierarchies by summing attributes, but that doesn't make it quite as easy for end users to expand an alternate hierarchy to leaf level or to mix multiple rollup nodes1
When looking at Financial Consolidation a company would always need a 'Statutory' rollup based on ownership and a 'Management' hierarchy based on how the company is actually managed (e.g. by geography, brand, ...). Each hierarchy rolls up 'companies' but with different parents/hierarchies1
Thank you all, these are super useful
To address a few of the questions:
- This is the start of the initial investigative phase - It is high priority, but it is way too early to be talking details, thinking of timelines or how it would look. That is all to come. I will come back to this thread at the appropriate time
- Time based hierarchies or slowly changing dimensions are very tricky in a multi-dimensional world, and I don't this solving for that directly - however, the before / after of changes could be possible
- In terms of selective access, again, too early to say, but valid to raise it as part of the discussion
Thank you again for your engagement
I have a couple examples of using alternative hierarchies:
1) S&OP use-cases
1.1 Products Properties and Reporting needs
Quite often Different departments look on a different aggregations of same SKU's
- S&OP: Category -> Brand -> Type -> Pack Set -> ... -> SKU
- Demand: Category -> Brand -> Type -> SKU
This is used by business to see what volumes will be needed by specific pack.
Agree with requirements that @JaredDolich shared concerning product hierarchies. This is quite typical for all S&OP use-cases. Reporting usually requires to slice and dice and see summaries by specific product properties which leads us to tailor reports and customise specific hierarchies to show proper properties.
1.2 Product Aggregates
This one a little different. I faced it particularly in Demand planning when customer do have a hierarchy of SKU's and would like a maintain both SKU details and aggregate of smaller SKU's as they dont need to forecast really each low level SKU, but rather do precise forecast of TOP100 and rest aggregate to groups. So actuals are coming by SKU, but Forecasting really on Top SKU + Aggregates. Example:
SKU 1 -> SKU 1
SKU 100 -> SKU 100
SKU 900 -> Group A
SKU 999 ->Group A
SKU 1000 ->Group B
2) Security segregation
One of the examples that I faced also in hospitality industry:
Hotels could be structured for planning purposes by Geography like:
Country -> Brand -> City -> Hotel
But managed and reviewed by Brands + Property of the Hotel Size (Big, Med, Small)
So from a process side planning is feeded from a bottom-up by hotel, but reporting and manager reviews are done by another properties of the hotels and they need to be Secured with selective access. And it was done through segregation of hierarchies and different security settings for each of it.1
First use case that comes to mind is here :
We have people who are in charge of cross organization performance and cost controlling : say their main focus is cost center a, b, c which is part of directory A.
But they need also to report across directions that cover cost centers a as well as multiple cost centers (d, p, o...).
This is typically currently addressed through list subsets, but these cannot agregate on multiple levels.
Hope this helps.1
Ex 1 - Dotted line management or overlay situations:
VP #1 is in charge of North America Sales. VP #2 is in charge of North America Support. VP #1 and VP #2 are peers and do not report to each other. VP #1 may need to see all of the support data in order to see which accounts are profitable for sales so needs to roll employees by region or overlay relationship nature
Ex 2 - Selective Access Roll Up Summary:
A , B, C are list members of the list "Letters". 1, 2, 3 are list members of the list "Numbers". "Letters" and "Numbers" roll up to list "Alphanumerics". Someone may have selective access on A, B, 3. They can easily see the 3 products separately but can not see the subtotal of "Letters" or total of "Alphanumerics" for A+B+3 because they don't have selective access to the parent list. To allow visibility of totals, a secondary list with a custom rollup is required and data modules has to be cloned to use the cloned list.
The ideal solution for this example may not be alternate hierarchies but more so allowing hierarchical roll ups to display sum of visible leaf members even if parent permission is not granted0
One of my customers requests Alternative Hierarchies to support their quarterly financial budgeting process, specifically to "freeze" the employee list in the final budget scenario. The controllers plan their budget based on the employees in each cost center, then once determined they would like the budget scenario frozen (while the controllers can continue to evaluate changes in an "estimate" scenario, where it is correct that the employee list is updated as changes occur.1