IBM Cognos 8 Planning — Save 50%
Engineer a clear-cut strategy for achieving best-in-class results using IBM Cognos 8 Planning with this book and eBook
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". This article by Rich Babaran summarizes the key steps of the Model Development Process. Over the course of this article, you will learn how to:
- Design the model template in Analyst
- Build the Contributor application
- Enter and review plans in the Contributor Web user interface
- Publish and report on planning data
- Maintain the planning models
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.
eBook Price: $35.99
Book Price: $59.99
Building the Contributor application
The second step in the model development process is to build the Contributor application by using the Analyst template model. You, as a Contributor administrator, build the Contributor application using the New Application Wizard.
An Analyst model becomes an application in the Contributor program.
The terms "model" and "application" are used interchangeably in practice. In many organizations, a modeler can perform the role of a Contributor administrator, while in other organizations these two roles are separate.
There is a special dimension called e.List. This plays an important role in designing the planning model. Although the e.List is simply a dimension in an Analyst planning model, such as sales territory, region, or sales person, this dimension plays a critical role when deploying the Analyst model to business users in Contributor. This special dimension controls the distribution of the Analyst template to business users, referred as Contributors, and also secures business users' access to an application.
Analogously, let's say you want to send an HR newsletter to all of the department heads in your organization. Carry out the following steps:
- Create a newsletter (Analyst model template).
- Print out copies of the newsletter (copying templates in Contributor application).
- Determine who will be the recipients of the newsletter.
In this example, the e.List becomes the list of recipients. Thus, once the Analyst planning model template is ready and the Contributor application is built, the e.List controls who the recipients of the model template are. An e.List typically represents the organization hierarchy, for example, regions, cost centers, profit centers, and departments. In the following screenshot, Contributors represented by countries will log on to the Contributor web site and access the Analyst template model for planning and forecasting. The following e.List screen shows an organizational hierarchy in a tree format:
After the Contributor application has been built by using the Create New Application Wizard in the Contributor Administrator Console, you (as a Contributor administrator) can now configure the application. Typically, the Contributor configuration options increase the Contributor web site usability and access controls. You may also secure your planning data to ensure that only authorized users can see the planning web site and planning data.
The Contributor administrator configures and controls the Contributor application in the Contributor Administrator Console program.
It is common to find multiple planning models or applications in an organization. For example, there may be an application for sales and another for cost of sales, and it is not uncommon for a business user to analyze the impact of sales changes on the cost of sales application. The IBM Cognos Planning tool provides linking similar to a copy-and-paste function that can be used to transfer data from one application to another application. Therefore, in our example above, various linking techniques can be used to transfer data from the Sales application to the Cost of Sales application. Two Contributor data transfer or linking features are an admin link and a system link. The admin link is initiated by the Contributor administrator while the system link is run by users. You, as a Contributor administrator, can run the admin link manually or schedule it to run automatically. In the recent versions of IBM Cognos Planning program, a user can run an admin link indirectly, if permitted to do so by an administrator. Users manually execute System links.
Entering and reviewing plans in the Contributor Web user interface
Once the Contributor application or web site is ready, business users can then enter the budget or forecast for their respective organizational units. The entry and review process ties to an organization's planning cycle. An organization, for example, may require their business users to plan monthly, yearly, or within some other timeframe.
In Contributor, two business users' roles exist: the planner and the reviewer. The planner enters data in the Contributor application in the Contributor web client. The reviewer reviews the submissions of reviewers or planners. For example, a Minnesota sales manager (planner) is responsible for submitting their budget to the mid-western sales region manager (reviewer).
The following screenshot gives a brief overview of the Contributor web site user interface:
- Tree and e.List: The tree on the leftmost side of the screen shows the areas for which business users are responsible for contributing (Contributions) and reviewing (Reviews), in a hierarchical form.
- The table on the rightmost side of the screen provides information, such as the workflow state of the item, the current owner, the reviewer, and when the item last changed.
- Menus provide access to common functions, such as copy and paste, print, and submit budget.
- The toolbar provides shortcuts to the same functionality as the menus.
- Tabs contain information organized by subjects, for example, in a sales application, you may have tabs for sales assumptions, sales input, and sales summary. A tab on the Contributor web site corresponds to a D-Cube in the Analyst program.
- Dimensions are lists of related items such as Profit and Loss items, months, products, customers, and cost centers. Dimensions also contain necessary calculations, such as total sales or gross profit.
- Data Entry and View cells: Business users enter or view planning information in this area. This area can be available for input (white cells) and read-only (gray cells).
Publishing and reporting planning data
Contributor, by design, is a planning data collection tool and though it creates, aggregates, and summarizes the data well, business planners will still need a robust reporting solution for querying and reporting planning data. IBM Cognos, as a BI leader, provides various tools, such as Analysis Studio, Report Studios, and Query Studios, to meet business users' reporting and analysis needs. The following paragraphs highlight the processes of integrating the planning data with the BI tools.
In the initial stage of a IBM Cognos multi-tools implementation, both the Business Intelligence (BI) modeler and you, as a planning modeler, work together to understand the users' reporting and analysis requirements. After learning the reporting requirements, both you and the BI modeler determine the Contributor data delivery options and work together to create the planning model and BI reports/analysis.
Once the planning model is ready, business users submit their plans and forecasts on the Contributor web site. The Contributor program stores users' submissions in one of the following types of supported database: MS SQL server, Oracle, and IBM DB2.
The Contributor submissions are stored in a proprietary XML format that cannot easily be accessed for reporting purposes. The data is held in a complicated format that cannot easily be read. There is a method for accessing this data using the Planning Data Service (formally the Contributor Data Server), but it is a slow process and is more suitable for ad hoc querying rather than for a full-scale BI reporting solution. For a more flexible BI reporting solution, the data can be published to a separate star schema database. The techniques for accessing live data or published data are summarized as follows:
- Live data: This method allows you to read live Contributor data through the Planning Data Service, and does not need to be published. A package can be created that contains the connection to the live Contributor data in two ways.
- Published data: The Contributor data can be published to a separate star schema database. The publish process collects the data stored in XML format and moves it to a star schema database for more flexible reporting.
Maintaining the planning models
The planning models periodically require minor to major modifications and maintenance. Most of the modifications are completed to accommodate changing business needs. Unlike ERP systems, which are used for transactional activities, planning models are generally used for a specific period of time. For example, a typical budget cycle could last only three to four weeks in a year. Most of the model modifications are needed before the planning cycle starts, and these modifications could affect both Analyst models and Contributor application configurations. Modelers, Contributor administrators, or a specialized support team, typically maintain or update planning models.
Maintaining the planning models and application may require many administrative activities, which will vary depending on the complexity of the models and the business needs. However, the following maintenance activities are common in many organizations using the IBM Cognos Planning tools:
- Updating the model template: As business requirements grow or diminish, the current model design may no longer satisfy business needs. Hence, the design of the model may require modifications and revisions. BI models willalso require downstream changes.
- Updating business assumptions: Even if there is no change in the model design, typical business cycles will require some assumption updates, for example, changes in the tax rates, days to calculate depreciation, and so on.
- e.List/users or data security changes,: Organizations often change their organizational chart and structure, and the planning model should immediately adapt to these changes. As an e.List stores the organization's hierarchy, this dimension requires constant updates to meet the business's needs. Users often move and transfer their jobs, and so their security access and privileges would need to be updated frequently.
IBM Cognos Planning offers automation features for scheduling various administrative tasks to facilitate maintenance of the applications. You, as a Contributor administrator, or the support team, can work with their IT department to automate various routine tasks, for example, publishing or transferring data between applications using admin links.
Example: ABC Company
To illustrate the concepts discussed, we will use a simple Profit and Loss model of our fictitious company, ABC Company. This model makes use of the common Analyst objects and functions. It will also be the template upon which we will build the Contributor web application. You can download this model from Packt Publishing's web site, http://www.packtpub.com/files/code/6842_Code.zip. The following are details of the company.
- Employees: 4,000
- Expense Departments: 1,100
- Products: 350
- Planning Cycle: Yearly budget
- Planning approach: Bottom up
- Number of Planners: 35 planners across the United States
- Fiscal Period: Calendar
- Model Requirements: In May 2009, the Director of ABC Company's Financial Planning Department has selected the IBM Cognos Planning tool to create the budget template for their 35 users. He/she asked you to work with the business users and design, and deploy the revenue and expense planning models for 2010 fiscal year. Some specific requirements are explained in the following section:
- Revenue planning:
- Plan next year (2010) revenue by products
- Apply current year (2009) drivers and price information
- Provide 'what if' capability to users
- Assume inflation of 4.5%
- Number of revenue planners = 15
- Expense planning:
- Plan next year (2010) expenses by accounts
- Apply current year (2009) actuals to drive next year expenses
- Provide 'what if' capability to users
- Number of expense planners = 20
- Consolidate P&L:
- Summarize next year (2010) revenue and expense plan
- Compare next year (2010) plan against the current year (2009) and actuals
- One canned P&L by departments and organization for the President and VPs
- One Analysis report to analyze revenue plan for revenue planners
In this article, we discussed the Model Development Process. Initially, we talked about the key factors that you need to watch out for, before designing and building the IBM Cognos Planning model.
We devoted the rest of the article to discussing the key Model Development Process, and the following steps were carried out:
- We explained the designing of the Analyst model, the importance of model flowcharting, the concepts of dimensions, datastores, data flow, and the principles of good Analyst model design
- We described the steps needed to create the Contributor application from the Analyst model
- We saw how the business users will enter and review the Contributor application
- We talked about the publishing process, and how to use Contributor planning data for analysis and reporting using IBM Cognos BI tools
- We discussed the maintenance of planning models and reports
If you have read this article you may be interested to view :
- Storing Planning Data in IBM Cognos: D-Cube (Part 1)
- Storing Planning Data in IBM Cognos: D-Cube (Part 2)
- Reporting Planning Data in IBM Cognos 8: Publish and BI Integration
eBook Price: $35.99
Book Price: $59.99
About the Author :
Rich Babaran has over 20 years of experience in financial modeling and analysis, planning and forecasting, performance measurement development, workflow modelling, and process improvement. He has spent the last 9 years helping organizations improve their planning processes using IBM Cognos Planning. Rich has a Bachelor's degree in Economics and an MBA in Finance. Rich is a partner at Agile Strategic Business Consulting.
The authors and Packt Publishing would like to extend their thanks to Janosys Inc. for their gracious support in the development of this book.
Books From Packt