FP&A Technologies: (iii) Multidimensional Databases
Multidimensional:
Modern planning systems are set up in terms of the business dimensions in which the organisation being planned operates. This includes the organisational structure; the accounts used to plan and report results; the time periods in which it reports, such as weekly, monthly or seasonal; the versions of data to be held, including actual, budget or forecast; and any line of business or major product grouping.
It is the intersection of these ‘dimensions’ that define a particular value; for example,
actual sales
of
product A
by
department North
, in
July 2012
. Each item is a member of a business dimension.
When it comes to reporting or the setting up of a data entry grid, the administrator simply states what dimensions are to form the column, row and ‘off grid’ member. For example, a report can be set up to show actual versus budgeted figures for each month as columns; the accounts making up the profit and loss (P&L) as rows, and the ‘off-grid’ member can be departmentalised, which means the report will display each department on a new page.
As well as providing fixed reports, some users will be allowed to swap the rows and columns in the same way as Excel pivot tables. This enables them to view performance from a range of business perspectives but without having to duplicate the data or ask for new reports to be developed.
Business Hierarchies:
Dimension members can be arranged as one of more hierarchies. The total company members can be defined as the aggregation of, say, four divisions, which themselves can be defined as the aggregation of other departments. These hierarchies can then be used to consolidate data from those entities at the bottom of the structure to give intermediate consolidated results. Some of the more advanced systems can also store multiple hierarchies to hold, for example, this year’s and last year’s organisation structure. This enables results from last year to be consolidated according to this year’s structure while still preserving results in last year’s format.
Named-based Rules:
Rules can also be defined for each member. This typically happens on the accounts dimension, where rules can be set up to calculate subtotals and ratios. They can even access members in other dimensions and at different hierarchy levels, thus allowing the creation of allocation rules that span multiple structures. What makes these rules different from a spreadsheet is that each rule uses specific member names, so users and administrators alike easily understand what is being calculated. It also means that as new members are added, existing rules don’t change and the integrity of results is preserved.
Multi-user, Role-based Security:
Enterprise planning systems recognise that multiple people will be accessing them, each with different roles and responsibilities. This means that a system can be used by many people from across the organisation, but that each person is automatically controlled in terms of access to the data and features they are allowed to use.
Unlimited Size:
Today’s planning systems have limits that are much greater than those found in Excel. It means that the design of the system need not be limited by the technology, although performance may be compromised if they are allowed to become too large.
Excel Access:
In general, users like Excel for its formatting, charting and note making capabilities. Because of this, most planning systems allow access via Excel, however the link is usually secure that limits users to what they are allowed to see as defined in the application security system.
Multidimensional Planning Technologies:
The above points describe the capabilities of most business intelligence (BI) systems such as Hyperion Essbase, IBM TM/1, and Microsoft SQL/AS. However, these only provide a ‘toolkit’ approach to planning. For this reason, software vendors add the following capabilities to BI systems to make them more suitable for planning:
Financial Intelligence
Financial intelligence refers to the ability to deal with financial numbers. That includes handling different currencies, performing currency conversion at multiple rate types, detecting and posting exchange gain/losses, as well as the recognition of different account types, including balance sheet, P&L and statistical types. This greatly simplifies the setting up of calculation rules as these then automatically deal with data in the right way; for example whether accounts are summed over time, converted or consolidated.
Process Control
The last capability is in the area of process control – when users are allowed to enter data – and who should then approve their submissions before it is consolidated with other results. For example, budgets may be required by a certain date. Performance management systems automatically remind users and chase them up as deadlines approach. As they complete their submissions these systems validate their answers and then inform those involved in approval. Once submitted, users are no longer allowed to enter or change data unless the approver rejects their submissions.
At all times, administrators are able to see the status of the data; whether data entry has started, whether it has been approved, whether the submission is late and so on. They can also see audit trails of how data has changed over time.