Throughout the many engagements with customers working with IBM Cognos Planning applications it has become apparent that there are certain modeling techniques that can be applied to most applications. In all cases, all of the applications have been bespoke to the individual client requirements, yet the requirement to design and implement a budgeting or forecasting process, which is easy to maintain and update, remains the same. The following 5 modeling techniques can be used with any existing IBM Cognos Planning implementation to reduce the administrative burden (please note that all 5 relate to Contributor implementations):
1. Introduce a Month’s Attributes D-Cube
A month’s attributes cube is made up of two dimensions. The month’s attributes dimension, which contains all of the calculations to control the model; and the timescale dimension which should not contain any calculations at all. The cube generally relies on one input – the current month. This can be created in a one cell cube that is d-list formatted to the timescale dimension used in the month’s attributes cube and the data is transferred to the month’s attributes cube with a d-link. Using a variety of built-in-functions (BIFs) and conditional formulas you can achieve results to show which months are actual months, forecast months, months to be read only, current month, current month lags etc. All calculations are created in the month’s attributes dimension and most items will be d-list formatted with flags to be used in links as we will see later.
Now that the month’s attributes cube is in place, administration of the model can be made easier with the following techniques.
Note – the month’s attributes will become an assumption cube so remember to hide the cube using access tables.
2. Automate Read Only Cells for Actual Periods
One of the advantages of implementing a month’s attributes process is to automate read-only cells for actual data. Actual data will tend to be imported into the model from an existing source system and therefore end users should not be able to influence this data. Existing models may currently use access tables to mitigate the risk of overwriting imported data, but this will require the access tables to be updated on a monthly basis.
The automation of this process is achieved by introducing an internal link to each cube that contains actual data. The month’s attributes cube is used as a d-cube allocation using the actual month attribute to map the timescale dimensions within the d-link. The effect is to make the actual months the target of a link and as a result, the contributor grid will show these months as read-only. The data within the actual month timeframe will not be changed in any way as all other dimensions in the internal link will be mapped using matched descriptions.
3. Control Links
Following on from the technique to mark actual months as read-only, we can now establish a process to allow data to be imported into the actual month periods without impacting the dataflow of the application. An example of where this may be an issue is a link between a Profit and Loss d-cube and a Balance Sheet d-cube. The P&L and Balance Sheet process in reality may be a complex process within a transaction based accounting system. A planning application should aim to emulate the process but be simplified i.e. not a transaction system. The actual balance sheet for the latest month will need to be imported, but the link from the P&L may flow data into the actual periods for certain items. Importing can fail due to items being the target of a link. To overcome this the following technique can be implemented.
Review each of the links in the application to identify the links that apply to the forecast process. Using the month’s attributes cube as a d-cube allocation using the Forecast Months attribute, you can prevent the link from targeting actual periods. This will then allow the administrator to import actual data into the target cube without the risk of the data being overwritten by dataflow within the application.
This technique can also be used to move actual data only throughout the model using the Actual Month attribute within the d-cube allocation.
4. Manage and Maintain D-Cube Size
Managing the size of an application to maintain performance is a key responsibility of the administrator. An application can become oversized easily if the application is not monitored or managed. A technique to assist with the management of the application size is to review the time dimension used in each of the cubes. If a cube exists to only hold actual data, the cube does not need forecast months. Likewise, if a forecast calculation or process is being run, the cube, potentially, does not need to contain actual months.
The month’s attributes cube can be used as a source for d-list updates. Within the month’s attributes cube it is possible to create calculations such as Total Year, Total Forecast, Total Actual, Quarter Totals etc. Using these items as Parent items in the d-list import attributes you can achieve automated calculations when updating the d-lists. Within the import settings you can select which items you may wish to import to the d-list to be updated such as Forecast Months or Actual Months, along with any Parent items if required. Following a monthly rollover, the d-lists can be updated which will increase/decrease the number of items in the d-list and update the calculations.
This technique can also be used in conjunction with actual data for other dimensions in the model, allowing the model to become a living blueprint in line with the activities of the business.
5. Develop a Manager Screen to run the Administration
IBM Cognos Planning Manager is a simple tool designed to aid the end users in day-to-day modeling activity. It is quite often overlooked in a Contributor implementation; however, a simple series of Manager screens can be very effective in the administrative processes that require regular attention.
With the techniques listed above, a Manager screen can be developed to allow the end user to state what the current month is, followed by a process to update the month’s attributes cube. Instructions can be added to the macro to prompt the end user to execute Contributor Administration Console macros to complete the process and roll the model forward. This step would fulfill the first three techniques above.
In addition, a macro could be written to update all of the timescale dimensions (or other moving dimensions) followed by a prompt to run more Contributor Administration Console macros to achieve the results described in technique 4.
As a result, the administrator does not need extensive knowledge of Analyst (although in-house product knowledge is encouraged). The processes can be shared easily with other team members and succession planning is a less daunting and time-consuming exercise.
Conclusion
Administering an IBM Cognos Planning application need not be a time consuming exercise on a monthly basis. With a good design brief and strong product knowledge it is possible to automate most (if not all) of the required administration tasks. The techniques above should be included within the design of a system if there are requirements to control the model behavior identified in the project scope. The above techniques can then be implemented in hours rather than days during the build.
If you would like further information to enhance your IBM Cognos Planning systems please leave your comments and someone from the Planit CPM team will be in touch to discuss.
