The 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. No longer do we need to create and maintain separate summary cubes with added dimensions for every conceivable product/customer/time attribute. No longer do we need to fret about creating a cube that has years and months split out in separate dimensions or combined into one. With hierarchies we can metaphorically have our cake and eat it too. The cube snapshot below illustrates how we can use hierarchies on a stores dimension of a sales forecasting cube to segment and summarize sales figures by territory, state, and channel without needing to build a separate summary cube with additional dimensions.
Creating hierarchies based on attributes is easy to do in PAW and dimensions created with a hierarchy-first focus are visually cleaner and easier to maintain. Hierarchies can however be confusing for new PA developers as well as seasoned ones who historically have had to only consider a dimension as a single hierarchy with potentially multiple consolidation hierarchies and not a collection of separate hierarchies. Referencing hierarchies in rules is straightforward and not difficult.
To demonstrate how to reference hierarchies in PA cube rules, let us consider a sales forecasting cube with sales forecasted by month and store. The stores themselves are associated to territories and 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.
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, but 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 and the cube rule will still use a DB reference, but 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 and 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 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 and we’d be happy to walk through this great functionality with you.