Simple Modeling for Everyone Using Cell Overlay
by John Hormaechea , Director of Product Marketing at Pyramid Analytics
Customers often ask “can you change that value for cost allocations” or “I want to set up custom calculation that performs an action, like aggregation, that may not be the actual value.” This can require A LOT of MDX at best and can’t be done at worst. Fortunately, BI Office has a great, but sometimes-forgotten feature called Cell Overlay found in the Advanced Calculation Designer that provides a response to this and other questions using a wizard interface.
Cell Overlay Overview
Cell Overlays can be used to manipulate the value of cells with alternative values to achieve an analytic outcome. Some classic examples include:
- "What if" scenarios: change values to see how related results would appear.
- Custom aggregations: set a parent to the value of the last child element, rather than the sum of its child elements.
- Complex calculations: trended values that are the average-calculation of all previous elements for a given element, rather than the actual value itself.
Click on “Cell Overlay” option in the first Calculation Designer window.
You will be presented with this window:
The window displays two tabs:
- Scope: You can select the target selection of the data model (or cube) that will be affected by the new formulation.
- Formulation: You can define the custom calculation that will affect the elements specified in the scope.
Designing a Cell Overlay
1. In the Scope tab, you can select the target section of the data model that the cell overlay will affect. The section can comprise:
· Specific elements from some or all hierarchies: If selecting a hard-coded element, only 1 element can be selected from a given hierarchy.
· Sets of elements from some or all hierarchies: If using sets, only descendant and level selections can be used to pick a set of elements. Named or Custom Sets cannot be used.
2. Next, you can define the calculation in the Formulation tab. The formulation can be designed around any mathematical equation comprised of data points, numeric functions or other functions. Refer to the explanation on custom members for more detail on the equation editing options.
3. Then, fill in the Metadata panel (at the bottom of the window):
· On the final part of the wizard you have three areas to complete.
· You can provide a Name and Description for the new Cell Overlay.
· Then, choose the Access permissions. For further information about Security and Access permissions, click here.
· Test: this button allows you to test the new calculation’s syntax (indicated with red arrow above)
o If the syntax is correct, the response will be, “Query passed!”.
o If the syntax in incorrect, there will be an error message.
· You can select the checkbox for Cumulative to enable users to multi-select and combine columns and/or rows for query outcomes when using parameters.
· Click OK to finalize the Cell Overlay. The application will automatically display the list of Cell Overlays you have built.
And that is it! You can now use the set or member in any of your analyses. Consider using the Cell Overlay when you must do simple number adjustments or combine with other scenarios like Forecasting. More examples of Cell Overlay in use include Samples of Cost Allocation found HERE and Bubble-Up exception found HERE.
-Let's say you want to use a cell overlay to change the input for student enrollment. I could see you tie that into a view that, depending on student enrollment, might tell the breakeven point for student activity costs, athletic passes to get into athletic events, or student cafeteria plans.