Understanding the Model Development Process in IBM Cognos 8Ahren Stevens-Taylor
The Model Development Process is a proven step-by-step approach for designing and deploying planning models in an organization. This process enables us to chart various activities involved in identifying the organization’s planning requirements in order to devise functional and efficient modeling solutions.
The following diagram illustrates the Model Development Process and shows the typical stakeholders and IBM Cognos tools involved in the process:
In the previous diagram, we saw four typical roles in organizations that are currently using the IBM Cognos Planning model and applications. They are described briefly as:
- Analyst Modeler: Responsible for gathering business requirements—designing, building, and testing Analyst models, and managing the data workflow within the model.
- System or Contributor Administrator: Responsible for creating, maintaining, and securing Contributor applications translated from Analyst models.
- Business Users: Responsible for entering, submitting, and reviewing planning data. Users will be referred to as the Business Users or Planners.
- Support Team: Responsible for maintaining models and applications, during or after the initial roll-out.
Considerations for building an Analyst planning model
When purchasing a vehicle, you may consider many attributes before finalizing your decision. For example, you may determine the type of vehicle to buy (sedan, minivan, and so on) or may evaluate the commuting needs. Likewise, before beginning to build the planning model, you must consider some key factors about our planning processes. To build and deploy the correct planning models in an organization, Project Managers, Business Users, Modelers, and other project stakeholders should consider the following factors at the initial stage of the planning project:
- Planning functional models
- Planning cycles and horizons
- Planning approaches
Planning functional models
Every business organization uses a variety of planning models to produce its business plans. A number of planning models are common in most of the organizations. For example, many business organizations have some form of revenue, cost of sales, payroll, capital, and operating expense models. On the other hand, some models are unique to a particular industry and trade. For example, a pharmaceutical company may have a Clinical trial or R&D model, or an international shipping company may need an aircraft fleet cost-control model.
Other models may reflect an organization’s business focus. The organization may develop a model to project and control a particular cost that is critical to its business strategy. For instance, a beverage company that places a heavy emphasis on brand recognition may have a separate marketing model, or a consulting company that routinely rotates its employees to offices around the world may have a separate travel model. Whatever purpose the models serve, it is important that you understand the rationale underlying the organization’s use of them, so that you can build models that are more closely aligned to the organization’s business needs.
Planning cycles and horizons
You also need to be aware of the organization’s planning cycle and horizon. The planning cycle refers to the frequency by which an organization develops or updates its business plans. The planning horizon refers to how far into the future the organization plans. An organization may have multiple planning cycles, but may only plan for a single time horizon. The frequency with which an organization plans depends on many factors. For instance, organizations that operate in highly dynamic and competitive environments, such as technology companies, tend to have more frequent planning cycles. Companies in more stable environments, such as an alkaline batteries manufacturing company, tend to have less frequent cycles.
Planning horizons may be driven by the organization’s strategic focus or the nature of the business. For instance, the planning horizon of a pharmaceutical company’s R&D plan may span up to 20 years, which is the amount of time that a clinical drug may take to get from inception to testing and eventually to marketability. A construction company may require multi-year plans to coincide with the time it takes to construct a building. More commonly, organizations develop a plan once a year in the form of an annual budget. The organization then revisits and calibrates the plan mid-year, after several months of actual data has been gathered. Actual data is used to measure year-to-date performance against the plan, so that the organization can forecast for the remainder of the year. The typical planning horizon is twelve months, usually the organization’s fiscal year. If a long-range plan exists, the long-range plan is updated with changes to the annual plan or forecast.
Planning cycle refers to the frequency at which an organization develops or updates its business plans. Planning horizon refers to how far into the future the organization plans.
Knowing an organization’s planning cycle and horizon is important when building a model. Many organizations use cycle-specific models because the business assumptions and calculations tend to differ between planning cycles. For instance, an organization can have a P&L model for the annual budget and another for the mid-year forecast because an annual budget and mid-year forecast usually require different data and calculation requirements.
Knowing an organization’s planning cycle can give you an insight into how you may want to build your models. The organization may start with detailed plans once or twice a year. If rolling forecasts are prepared, the forecasts may be done at a higher level, for instance, at an account or organizational summary level. This means that you may have to create a detailed model and a summary model.
Knowing the planning horizon enables you to construct the appropriate timescale that can be used by other models. An organization that plans its revenue every quarter may also plan its expenses in the same way. An efficient planning model is built on standard data structures, such as timescale. Thus, timescales are an important consideration because they can be shared across several of the organization’s planning models.
Business organizations can use different approaches to plan their budgets and forecasts. You need to consider these approaches when building the model, as these approaches dictate how the model will be designed and deployed. Examples of common approaches are as follows:
- Zero-based budgeting: Each planner prepares estimates of their proposed revenue or expenses for a specific period of time as if they were planning for the first time. By starting from scratch at each budget cycle, for example, managers are required to take a closer look at all of their revenues and expenses.
- Driver based: Driver based planning models typically calculate plan numbers by adding, subtracting, or multiplying various drivers or metrics. Examples of drivers: number of units sold, price of a product, and so on.
- Top-down: Top Upper-level management sets the targets and pushes them to lower management who then pushes them further down the organization. Then the plans for achieving the targets are submitted up the chain of command for review and approval.
- Bottom-up: Lower-level management prepares the plans and then submits them up the chain of command for review and approval. The approval and rejection process follows until the plan and finalized.
Designing the model template in Analyst
A planning model is a set of Analyst objects whose purpose is to generate specific plans using a variety of data inputs, assumptions, and calculations. In practice, a model is named after the output it produces. An output can be a specific budget for product lines or it can be a category of expenses consisting of several general ledger accounts, such as payroll.
Once you have identified the model output, break it down into its inputs, assumptions, and calculations. For example, a salary plan may be the outcome of the inputs of employees and positions, their current salary, earned merit increases, and bonuses. The salary for newly-hired staff may be assumed based on their position. To produce the salary plan, the model would calculate the merit increases and bonuses for each employee by multiplying the salary by the merit and bonus percentages and then by adding the results to the salary. Then it would pull the appropriate salary for each new hire depending on position. Finally, the model would aggregate all of the employees’ and new hires’ salaries to come up with the salary plan. In this simplified example, four model functions are apparent: inputs, assumptions, calculations, and outputs. In fact, you can say that a model is a collection of these four functions. The IBM Cognos Planning Analyst tool allows you to build objects that collect inputs from users, designate assumed values, and perform calculations on them in order to produce the expected output.
Flowcharting the model structure
Before building an effective planning model, it is important to develop a detailed flowchart that logically illustrates all of the model’s structural components. Just as an architect develops a building’s blueprints before even breaking the ground, you must begin with the model’s blueprints. Often, many modelers skip this important step and begin constructing the objects, without a clear path to the final outcome. Unfortunately, such haste results in a disorganized and inefficient model. A poorly-designed model can adversely impact an application’s performance and cause a downstream effect on user productivity. The consequence can be severe. When the model is deployed to hundreds or thousands of users, a single instance of inefficiency will multiply at an equivalent scale.
Flowcharting helps you to avoid these problems. It gives you a glimpse of the final product and forces you to think through the various factors and issues that must be addressed before starting to build the model. A disciplined and methodical approach can steer you away from many of model building’s hidden pitfalls. Indeed, a well thought out flowchart can cut the build time significantly by minimizing rework and trial and error.
Flowcharting can lead you to uncovering the important design elements, such as the dimensions, datastore, and data flow. A good flowchart should show the sources of data inputs, and whether they are entered by the planner or originate from other data sources such as an ERP system or a general ledger system. The flowchart should also illustrate the way that data will be stored and used, how it enters the model, and how it flows from source to target. Finally, the flowchart should describe the different ways in which data can be viewed so that you can gather the various dimensions that need to be included in the model. For instance, data can be viewed by cost center, departments, or profit centers. Alternatively, it can be viewed across time (days, weeks, months, years) or by versions (this year, last year, plan, scenarios).
Some developers may refer to model flowcharts as model schematics or Data Flow Diagrams (DFD).
You, the Modeler, typically initiate this design step in the model development process after learning and understanding the key business planning requirements. You then ‘white-board’ the design of the model template, and then document the design specification in a document called a Detailed Design Specification (DDS). Finally, you take the design specification and implement it in IBM Cognos Planning Analyst using the Analyst’s features and functionality.
The concept of multi dimensionality
IBM Cognos Planning is based on a multi-dimensional data structure in which data is organized around specific attributes, or dimensions. In the following table, data is organized around Account, Year, Version, Cost Center, and Month. Each record in the table contains data by account, year, version, cost center, and month.
One of the most common ways of presenting multi-dimensional data is in the form of a cube. In a multi-dimensional cube, data is displayed as one slice at a time along two or more dimensions. Each slice represents a subset of the population. Those familiar with Excel pivot tables should have little problem grasping this concept. However, those who are only familiar with spreadsheets can still find some similarities. In a spreadsheet, the rows and columns are actually two separate dimensions. A third dimension, the worksheet, gives you a three-dimensional view of data. If you enter data into the first cell in a spreadsheet, you are actually entering the data along three dimensions—Sheet 1, Column A, and Row 1. Hence, when you reference that cell, Excel denotes it as Sheet1!A1.
A multi-dimensional cube lets you view data the same way. But a cube can have several dimensions. Each dimension contains a list of related data such as accounts, version, cost center, or time period. When two or more dimensions intersect, the intersection represents a record or view of the data. For instance, a cost center dimension may list all the cost centers in the organization. A second dimension lists a group of expense accounts, a third lists 12 months, and a fourth lists the version (Plan or Actual). The intersection of these dimensions gives you data by cost center, by account, by month, and by version. The following Excel pivot table is an example of a multi-dimensional cube. Here you see a slice of the cube with the following dimensions: Account, Cost Center, Month, and Version.
In a multi-dimensional cube, you can arrange data in a variety of ways by swapping rows, columns, and pages. This is a powerful feature that facilitates in-depth data analysis. Those who have worked with multi-dimensional cubes understand their benefits. Multi-dimensional cubes can help you sift through masses of data to find valuable information. IBM Cognos Planning takes multi dimensionality a step further by leveraging its features to enforce rules and standards in order to make model maintenance easier.
Analyst is the tool that lets you create the planning template that the users will use to enter their plans, while Contributor is the tool that lets you replicate the templates and deploy them to a number of users based on a defined hierarchy. The plans are stored in a central database, and users connect to it through the Web. In a spreadsheet environment, similarities exist. You have a master template that you can use to build the worksheets. The worksheets are stored in a central folder, within sub-folders that are organized according to a hierarchy. Users connect to the shared folder to access their worksheets.
Understanding dimensions, datastore, and data flow
Analyst objects are the building blocks of the planning model. These objects enable you to define the data structure, store and calculate the data, and move data from source to targets. There are a host of objects in Analyst, each offering useful capabilities. However, the key objects are the D-List, D-Cube, and D-Link. These objects are indispensable to a model and thus deserve special attention.
Determining dimensions: D-List
The D-List is the basic building block of the model. In Analyst, dimensions are referred to as D-Lists. Each item in a D-List represents an attribute of the data. In a D-List, we decide what data to include in the model and how the data will behave. The data could be something that will be entered by the planner; it could be pre-populated, or it could be calculated. For example, to build a model of your personal expenses, you may have a list of expense categories (travel, food, and entertainment), you may want to track your spending over time (month, quarter, and year), and you may want to compare different versions of spending (actual and planned). Each of these lists of items could be a D-List. In the Spending Category D-List, you might include a Total that sums up Travel, Food, and Entertainment (see the following screenshot). In the Versions D-List, you may want a “Variance” between actual and planned values. There is virtually no restriction to the type of data that you can include. However there are certain principles to adhere to when creating D-Lists.
The first step in constructing a model is to identify the dimensions that will be used. There are many sources of information that will give you an idea of the dimensions that you need. Data entry templates from the organization’s existing planning systems or Excel spreadsheets can suggest many ways in which data is gathered. The spreadsheet can also reveal the calculations used. Performance reports can be used to determine what the model outputs will be. Often, simply inquiring about the business can be a good start. Consider that you’re working on a project that requires you to design and build a revenue forecasting model for a Fortune 100 global consumer electronics retailer. One approach to determining the dimensions of this forecasting model is to ask the following questions:
- What does the company sell? The dimensions could contain a list of consumer electronics products, such as MP3 players and laptops, product categories such as audio and computers, or even brands.
- Who is the company selling to? The retailer’s customer list could be a dimension.
- Where does the company operate? Dimensions may contain a list stores, states, cities, countries, global regions, or market segments.
- What is the forecasting timeline? The timeline dimensions may be weeks, months, quarters, or years.
The words “D-List” and “dimension” are often used interchangeably. When used in the context of a cube, “dimension” is often more appropriate.
Building the datastore: D-Cubes
Whereas the D-List is where the data is defined, the D-Cube is where the data is stored. After you have decided what data will be included in the model, you determine how the data will be stored. The D-Cube is formed by two or more D-Lists. A typical planning model consists of several cubes. The cubes store a particular set of data and perform a specific function. For example, an Employee cube may store data about employees. A P&L cube may contain revenue and expense data. D-Cubes can be functionally classified as either an input cube that allows data entry, a calculation cube that processes data, or an output cube that displays the result. The Employee cube can be broken into an Employee Input cube (see the following example), Employee Calculation cube, and Employee Summary cube.
The words “D-Cube” and “cube” are often used interchangeably. Except for the terminology, there is no distinction between the two. “D-Cubes” are usually used in an Analyst setting, but “cubes” can work as well.
The key to building D-Cubes is to understand their primary function. Is the cube a place where planners will enter data? Will it be used simply to stage data? Will it be used to calculate inputs and feed the result somewhere else? Will it be used to present data in a report format for reviewers? These important questions must be answered before building the cube.
Another factor to think about is data. Data is stored in a cube. Consequently, the cube structure needs to follow the format of the data source that will be feeding it. As a modeler, you need to understand what type of data will be going into the model. For instance, planners need data to compare and analyze planning and actual information. They would like to see actual year-to-date sales compared to next-year projections. During the initial design process, you may decide to work with the data provider to review the source data and develop a process to extract, load, and validate data in planning models.
Perhaps the most important consideration is size. In a multi-dimensional data structure, size is always a constraint. Size has a direct impact on performance; the greater the size, the more time it will take to process data and transmit it over the web. In fact, performance can be such a tremendous constraint that it affects the way the model is designed.
Controlling data flow: D-Links
In a model that shares data among several cubes, data must flow from one cube to another. The D-link is an object that moves data. Similar to a data transformation or ETL tool, the D-Link maps dimension items in the source to dimension items in the target, enabling you to control the flow of data within the model. For multi-dimensional cubes where data sparseness can be a problem, the D-Link has a practical purpose. The D-Link allows you to break a large cube into smaller, specialized cubes while still making the same data available. Most models use function-specific cubes, where outputs from one cube are inputs to another. The D-Link connects input, calculation, and output cubes, bringing them together to allow the seamless movement of data. Any cube that requires data in order to perform its function can retrieve data without going outside of the model. Because data can be reused, it only needs to enter the model once, thereby simplifying the data import process. The D-Link’s ability to transport data is not limited to cubes. D-links can import data from a database, an ASCII file, an Excel spreadsheet, or a Contributor application.
The words “D-Link” and “link” are often used interchangeably. Except for the terminology, there is no distinction between the two. “D-Link” is usually used in the context of Analyst.
What makes an optimal model?
The saying goes: “There is more than one way to skin a cat.” The same can be said about model building. There are myriad ways to create the same output by using a combination of inputs, assumptions, and calculations. IBM Cognos Planning allows you to create highly complex models using its advanced forecasting algorithms and scenario planning facilities. With this capability at your disposal, you may be tempted to build a model that “does everything at the push of the button”. While such automation can appear impressive, it is often accompanied with many problems. Complex models make ownership and maintenance difficult. A highly-customized model can become so inflexible that when it’s time to enhance it, starting from scratch is an easier option, rather than building on its current form. Support and maintenance can also become a nightmare when you need to go through a laundry list of tasks to prepare for the next cycle. The tendency towards over-automation and over-customization, must be tempered with caution. More often than not, the model that “does everything” also requires everything to support and maintain it.
So what is an optimal model? The answer is one that delivers planning information in a timely manner at the lowest possible cost. Although delivering better information has always been at the forefront of every planning project, the cost of delivering it tends to be elusive. To be sure, the financial cost of the system is closely monitored, but there are costs hidden within the system’s inner workings that cannot be quantified and are often left to persist. The cost can take many forms: What is the cost of a poorly designed model? What is the cost of a Contributor application taking twice as long to process? What is the cost of thousands of users waiting an extra 10 seconds each time they can download a planning model? These costs must be taken into consideration when building the model. You, as a Modeler, must not only build a model that does its job, you must do so without placing an undue burden on these cost factors.
Principles of model building
If you ask ten people what makes an optimal model, you are likely to get ten different answers. This is not surprising. The quest for the one-size-fits-all formula has been a long one, owing mostly to the differences in the ways that organizations plan, but also to the openness of the tool and the absence of a shared body of knowledge. Although there are no hard and fast rules, there are three guiding principles that can help lead you down the correct path.
An optimal model must be built with an eye towards efficiency. An efficient model is one that takes the shortest path to performing its task. Usually this means fewer objects in the model. But it could mean other things: Data flows in one direction, D-Cubes perform clear and specific functions, calculations are more intuitive and easy to understand, D-Lists contain as few dimension items as possible, redundancies are non-existent, and data is organized in a logical fashion. Efficiency and simplicity go hand-in-hand. Simplicity eliminates clutter. It begs the question: Is this absolutely necessary? To a savvy Modeler, the concept of simplicity may be counter-intuitive and run contrary to his nature. Yet the ability to take complex processes and re-engineer them down to a few moving parts is indispensable to model building. Indeed, it is a higher skill, one that compels you to abandon conventional wisdom, think out of the box, and explore unfamiliar territories.
An optimal model is one that performs its task faster using the same resources. Performance combines effectiveness with timeliness. This means delivering the right information at the right time. The model must be able to process data and respond to user requests within reasonable time and without unnecessary delays. Although not everyone will agree on what “reasonable time” means, everyone can agree on what constitutes “unnecessary delays”. It is the difference between how the model performs and how it should perform. A model that is built on a weak foundation almost always bears extra processing overhead that takes additional time. There are essentially three areas where performance is most visible:
- Application processing
- Web client access
- Web client processing
Application processing refers to the server batch process that implements changes to the model, or loads data. Web client access is the point where users connect to the database to retrieve or save their plans. Web client processing is where users actually work with their planning templates, entering data and switching from cube to cube. All of these areas have a direct impact on user productivity, so that any lag in performance creates cost in some form.
An optimal model is one that requires the least amount of effort to set up and maintain. In a constantly-changing business landscape, organizations must be able to adapt to new environments quickly. Competitive pressures may push organizations to shorten their planning cycles or drive them to a new strategic direction. Planning models must reflect new realities in order to accurately project the future. They must therefore be flexible and easy to maintain. An optimal model is built on the premise that change is constant. The model must allow for its assumptions and calculations to change without a complete overhaul. It must use standards and share objects so that changes can cascade rapidly throughout its various parts. The model should enable a non-developer to easily take ownership of it without the need for advanced training.
These principles can be self-reinforcing. For instance, an efficient model usually performs faster and is easier to maintain. However, they are not exclusive and trade-offs can occur. When two good approaches contradict, you must weigh the benefit of one over the other and accept the trade-off. In a way, modeling is an art. No strict rules govern how a model should be built, lending the entire exercise to one’s own creativity. As a modeler, you should look to these principles for guidance, while keeping a close watch on other factors. In the final analysis, the planning system, like any other system, must be viewed in the light of its benefits, as well as its cost.