3 steps forward on reducing maintenance
You may remember using a previous generation of EPM planning products, maybe as part of an EPM suite. In my case, some of these involved creating many form designs and putting them into folders like this:
The point here is that designers would often produce several variations of the same form layout, each variant being just the columns that are different. So the budget forms would span one year, but show monthly columns. The long-term plan may show several years ahead, but the columns are years not months. Traditionally that meant lots of duplicating designs.
Why are Cube Views so much more than Forms?
Traditional EPM products typically featured a different experience and set of tools for building:
- Data Input Forms
- Reports
- Excel form layouts
That means that if you wanted a particular layout to be used as a data input form, but also as a report, and also as an Excel data retrieve, you may have to design the same layout in three different places.
With OneStream, the first step forward is that once you define the layout in a Cube View, you can view it as a Form, a printable Report, a Report Book, or as an Excel retrieve sheet. It’s all from one design layout. This is a massive step forward on reducing maintenance, but this is only the first of three steps forward.
The second step forward is that you often have similar layouts for budget forms, for forecast forms, for long-term-plan. OneStream introduced the concept of row and column templates right from the start. With these row and column templates, you can design a set of rows or a set of columns, and have them inherited by many cube views. That way you can modify one template, and have several forms, reports, excel layouts, or report books instantly reflect that change.
This is a massive benefit particularly for Planning & Forecasting, because you are dealing with different scenarios that look almost the same, but maybe just vary in the time frequency (columns). This way you save having to have a set of cube view designs for Forecasts, Budget and Long-term plans.
The third step forward
For forecasting & budgeting, you can go a step further and do something much more awesome than just using column templates, and that’s making the columns completely dynamic, and automatically vary by scenario, with a single cube view. Then the same cube view design works for Actuals, Budget, Forecast, Long-Term plans, whatever.
Intelligent Columns that Vary by Scenario
In OneStream you can define a rule that generates the list of columns on the fly, depending on what scenario you have selected on your workflow.
Now I design a single column template, that gets inherited by any cube view used for forecasting and budgeting.
It just simply says: “Show me a list of periods that are relevant for the current workflow scenario”
So if you are in your budget workflow:
If you are working on long-term (strategic) plan, open the same form:
If you are working on a mid-year forecast, open the same form:
All this, from a single cube view design.
The function GetPlanPeriodsFromScenario, you simply put into a business rule, so it can be made very specific to customer requirements.
Most of the information required is already in the scenario metadata, which can be queried in the business rule API.
- Data Input Frequency (monthly, quartery, yearly, weekly)
- Range (start period, end period)
- Number of no-input periods, e.g. 5 = 5 actual periods (no input) for forecast period 6.
By querying the scenario properties at view-time, you can make a really generic column layout for all cube views that handles all data scenarios. That way, as soon as you add an additional scenario, the data forms will automatically understand what time period columns should be displayed for it.
Matrix-Controlled Row Filtering
With reporting of Actual data, OneStream has the ability to make reports appear much more compact, by only showing rows (or columns) that actually contain data.
This makes the reports much more relevant to the particular entity or time periods. Which is great for Actual reporting, where most of the data is loaded rather than entered through forms. The trouble is, for driver-based-planning, a lot of the key drivers will be input by forms. So you cannot use row suppression, because you need the rows to appear before you can enter data into them.
In OneStream v4.2 there came a useful feature so a user can toggle the row suppression on and off.
The problem with this is that when you toggle the row suppression off, it will show every combination of members that are defined in the master data, causing you to see hundreds or thousands of empty rows. Then you have to scroll down and look for the correct empty row where you want to input data.
For driver based planning you need something more sophisticated. You want to show, for example, a form that lists all the products available, but only show products that are relevant for the entity being selected, regardless as to whether there is data there or not.
This is done using Matrix Row Filtering.
The idea is simple, use a matrix for a central or regional FP&A manager to specify what manufactured products should be switched on/off for display for each entity.
That way you can have a single form design, but different entities only see products (rows) that are relevant to them, regardless as to whether there is data shown or not.
How is this done?
The way to do this in cube views is to create a DynamicCalc member on a user-defined dimension, say UD8. Then have the last column of the cube view show this UD8 member.
This DynamicCalc formula looks up the matrix shown above, and returns either an Amount=1 ( if the product is switched on for this entity ), or return Nothing ( not zero ) if it is not there.
This fools the row suppression mechanism into thinking there is data in a row, even if there is no stored data.
With the ability to put any type of code in a DynamicCalc member, you can implement a very flexible, low maintenance, on-off switch for displayed rows, that works on-the-fly at view time.
About the author
Chris Loran works in the EPM field for over 13 years. From working with Hyperion at Hyperion Solutions, Oracle, Partake and EY to working with OneStream and Oracle HFM at Agium EPM. He is currently working for OneStream Software.
Within his Lead Consultant function at Agium EPM he worked on the OneStream implementation at Guardian Industries. They have one of the most extensively used OneStream platforms in which they consolidate, budget and perform advanced driver-based planning.