Basic planning scenarios in BI planning solutions and scenarios

Article
07/22/2014

Applies to: SharePoint Server 2010 Enterprise

Microsoft Office 2010 presents some interesting support for basic planning and data entry for organizations. Planning and data entry scenarios relate to the capture of data that does not usually exist in line-of-business (LOB) or enterprise resource planning (ERP) software applications and include the following:

Data that relates to forward views (plans, budgets and forecasts) for an organization
Ad hoc data that is prepared and captured manually

For many years now Microsoft Excel was used extensively by Information Workers (IWs) to support both the previous requirements together with more sophisticated and complex business modeling activities.

In this article:

About planning scenarios
Benefits of the Business Intelligence platform
Scope of solution

About planning scenarios

Basic planning and data entry

Line managers have to provide data on the number of planned and forecasted new people hires.
Line managers have to provide data for their operational and expenditure (OPEX) plans and forecasts.
Marketing product managers have to provide comparative data that relates to market share, sales volumes, pricing, revenue and financial numbers on competitors and competitive products.
Managers have to provide data for targets for organizational key performance indicators (KPIs).

Sophisticated planning and data entry

An IW has to build a business model to let other managers to evaluate ‘on the fly’ the risk profile of how changes in business assumptions (for example, sale volumes, raw material prices, and so on) can affect profitability, cash flow and financial stability.
An organization has to enable IWs to enter new capital expenditure projects subject to assigned capital allowances and limits, and have management approve the projects that are based on comparative calculated project criteria.
An organization needs to enable procurement and plant managers to model the supply chain and production capacity based on worldwide demand sales volumes from the sales and marketing function in order to optimize profitability.
An organization needs to have weekly sales forecasts entered by their worldwide sales force of 2,000 salespeople with review and approval by the sales management team.
Complex security requirements for hundreds and upwards of thousands of users.
Large data submissions from IWs from hundreds of fact record updates to upwards of hundreds of thousands.

Other characteristics of basic versus sophisticated planning and data entry

If we consider the previous examples, there are some other aspects that distinguish basic and sophisticated planning and data entry scenarios. These include the following:

These scenarios do not incorporate many business calculations and rules compared with the amount of data being entered.
IWs are primarily entering data and do not need to generate calculated results based on the data entry.
IWs are entering numeric data and not entering new line items, product details, project details, new hire details.
The data the IWs are entering requires minimal or no review and approval process.
The model that is needed to support the data entry is a single model (typically a single sheet in an Excel spreadsheet); or to say it another way, the model has limited dimensionality of between two to five dimensions with relatively few members or items in each dimension.

These scenarios incorporate many calculation rules and with increased complexities. A single change in a data entry value may spawn a recalculation of the entire model. For example, a change in the value for an item such as ‘Planned Sales Volume’ will require sales revenues, volume based discounts, cost of sales and expenditure items based off volume to be recalculated. This in turn will then affect revenue levels, profit margins, cash flow, and more.
In sophisticated scenarios, IWs will often require a high degree of interactivity with calculated rules and results where they have to see calculated results as part of the data entry (What-if scenario)
In many situations involving people and project orientated planning, IWs have to enter not just the numeric data for the plan, but also add new people and projects and be able to change details, for example specifying a person’s grade level.
Sophisticated planning and data entry scenarios will affect the potential business and financial outcome of an organization and it is very important that these scenarios are adequately reviewed at the data level by the management of the organization.
The modeling needed for sophisticated planning scenarios can have complexity that spans the following:
- Multiple models, comparable to having multiple sheets in an Excel workbook and with linked Excel spreadsheets.
- Having many dimensions (more than five and more than ten or more) per model to support the planning scenario together with dimensions that contain members or items ranging from tens of thousands to more than hundreds of thousands.
- Varying detail of hierarchies; such as planning on product hierarchy at brand level and forecasting at SKU.

Benefits of the Business Intelligence platform

So what are the benefits and advantages of using Microsoft Office 2010 and SQL Server 2008 R2 for planning and data entry scenarios?

The important capability that Office 2010 and SQL Server 2008 R2 provide is enablement of data entry to a centralized data model, specifically SQL Server 2008 Analysis Services supported by the SQL Server relational database tables. Why is this important?

It is usual even in the most basic planning and data entry scenarios that organizations require more than one person entering data in to the same data entry form or template (usually an Excel spreadsheet). The implications for this include the following:

IWs have to create multiple workbooks to distribute to other IWs for data entry.
Data is potentially duplicated within each workbook.
Making changes and updates to a distributed spreadsheet model can be problematic, manual and time-consuming.
There is no data level security within the workbooks.
Data is stored individually in separate workbooks and has no central data storage capability.
Aggregating data from many distributed workbooks be problematic, manual and time-consuming.
The data that is collected from the process cannot be easily reused without manually manipulating it; for example, through copy and paste to other applications.
Business rules and organizational structures and assumptions implemented in each workbook adding risk to data quality.

So what are the benefits of using SQL Server as the centralized data model?

As previously highlighted SQL Server 2008 Analysis Services provides a centralized data model which makes it easier to maintain and update (by technical users) vs. distributed spread marts.
Data level security can be applied to the Analysis Services data model limiting which users are able to see which data and control over where they can enter data.
Analysis Services is the market leading OLAP engine and by including hierarchies within the dimensions, data is automatically aggregated.
Analysis Services supports many tools and data connectivity so the data that was entered and stored can be re-used with other applications.
Advantages for using Excel 2010 with Analysis Services for data entry and reporting including the following:
- IWs use the familiar Excel for data entry through PivotTable with write-back capabilities.
- IWs can design their own data entry forms against a consistent and prescribed data model. Note that in Excel 2010, creating pivot tables with a defined region for data entry, such as input area for a specified range of time periods is not natively available and must be done with a customization.
- IWs can still use all the ad hoc capabilities that Excel offers.

Using SharePoint, document review and approval can be used for the process workflow.

Scope of solution

For the scope of these articles, we will design a solution around a set of planning scenarios for a fictitious company named AdventureWorks. The solution will be called AdventureWorks Planning.

The example companies, organizations, products, people, locations, and events depicted herein are fictitious. No association with any real company, organization, product, person, locations, or events is intended or should be inferred.

We will demonstrate how the solution built on the Microsoft 2010 platform can meet the following business requirements for our fictitious company

Planning, budgeting and forecasting will support concurrent data submission from at least 10 IWs.
Data security must be applied by geography.
Forecasting done at monthly detail, budgeting at yearly.
Fiscal year starts January of each year.
Data entry for forecasting is performed at product SKU level. (Note that you can also enter data at various levels of a hierarchy, such as at product brand level if the hierarchy is defined as client computer through use of the “data member” functionality of SSAS. In our solution, we will be demonstrating SKU level entry.)
Data entry done by different geographies and in different currencies.
Single financial view of organization in different currency types across all geographies. (currency conversion).
Load actuals from source system, forecast period to start after account period close and stretch to fiscal year end. Account period close changes each month.
HR department to update pay rates by pay grade on an annual basis.
Line managers to use pay grade assumptions to adjust and allocate new headcount.