Chapter 1. Planning Application Scenario
We will configure the application for the following business case.
A company has a budget for the current year in a Microsoft Excel workbook.
The company has actual data for January - October in an accounting system.
CFO needs an updated cost forecast for November - December and the entire year.
CFO wants to see detailed and consolidated data, actuals versus forecast and budget versus forecast.
The company has three cost centers with hierarchical structures.
The company operates in Germany, Austria, Switzerland, and France.
The company has four key account managers (KAM) responsible for each country.
The desired workbook should look like this:
The table contains Budget vs. Actual for January - October, and Budget vs. Forecast for November - December.
Key account managers must have the possibility to see the budget and actual data by countries in a read-only mode and edit forecast data for November - December.
This example is based on the model created by MultiBase GmbH (https://multibase.de).
MultiBase GmbH has a great team of experts in reporting and analytics, ETL, and database management.
I appreciate the permission to use their model.
We can define the following model to implement business case requirements.
The target database must contain the following dimensions:
The Accounts dimension must contain members like these for each cost center:
- Cost Center 1 (CC1)
The Times dimension must contain months and a year total like
- 2017.01 ...
The Categories dimension must contain the following categories:
The Entities dimension must contain the following members:
- All KAMs
- KAM (Germany)
- KAM (Austria)
- KAM (Switzerland)
- KAM (France)
The Regions dimension must contain the following members:
- All Countries
At this time we would combine the Entities and Countries dimensions. However, these are different business entities. So, we have two dimensions to have the possibility to extend the model in the future.