Alternate hierarchies in cube rules change the game. Indeed, not-so-new hierarchies available in Planning Analytics since version 2.0 have greatly aided and simplified our ability to summarize and analyze cube data.
Why use alternate hierarchies in cube rules?
In the past, it was necessary to create and maintain separate summary cubes with added dimensions for every conceivable product/customer/time attribute. Moreover, it was challenging to create a cube that has years and months split out in separate dimensions or combined into one. With alternate hierarchies in cube rules, we can metaphorically have our cake and eat it, too.
The cube snapshot below illustrates how we can use hierarchies on a store’s dimension of a sales forecasting cube. This cube segments and summarizes sales figures by territory, state, and channel. In this case, there is no need to build a separate summary cube with additional dimensions:
Creating hierarchies based on attributes is easy to do in PAW. Further, dimensions created with a hierarchy-first focus are visually cleaner and easier to maintain. Historically, hierarchies had to only consider a dimension as a single hierarchy with potentially multiple consolidation hierarchies. But referencing hierarchies in cube rules is straightforward and not difficult.
Hierarchy demonstration
Below, we demonstrate how to reference hierarchies in PA cube rules. First, let us consider a sales forecasting cube with sales forecasted by month and store. The stores themselves are associated to territories. Because of that, some expenses like advertising are planned at a territory level in a separate cube. We will allocate the advertising expense forecasted at the territory level to the individual stores, based on a store’s product sales percentage of the territory total product sales for a given month.
Alternate hierarchies in cube rules
Prior to implementation of hierarchies, our Stores dimension likely would have had consolidations corresponding to the different territories that could be referenced in the cube rule by querying the territory attribute of a store. A cube rule to calculate a store’s percent of territory total sales might have looked like this:
With hierarchies we still have territory consolidations.The difference is, they exist in a separate hierarchy in order to leverage advanced slicing functionality. To calculate a store’s percent of territory total sales, we will still reference the territory attribute of the store. Additionally, the cube rule will still use a DB reference. However, we will need to hierarchize the store argument of the DB reference. This is necessary for Planning Analytics to know in which dimension hierarchy it can find the territory consolidation. The syntax to hierarchize any element argument in a DB reference is <hierarchy>:<element>. The hierarchy name should be in single quotes, then colon, then the element argument. In this example, we want to query the territory total using the Territory hierarchy. The specified territory is still determined by the store’s territory attribute.
You can reference multiple hierarchies from the same dimension by separating the arguments with commas and encapsulating the list in parentheses. In the below example, I have a cube rule that references both the territory and channel hierarchies of the store dimension to calculate a store’s percentage of its channel within its territory.
We can help!
As you can see, referencing one or multiple hierarchies in a cube rule is straightforward and easy. If you are not currently leveraging hierarchies in your Planning Analytics environment or are struggling to implement them in a meaningful way, please reach out. We are happy to walk through this great functionality with you.
