In this step, we look at the data required, create dimensions and develop model hierarchy.
We start with evaluating our data and estimate what kind of drivers we want in our model. The highest level KPIs will look like this:
All models need data in order to work. This high level data, such as needed for the tutorial, is available in the most of companies:
- Total Revenue by Product & Country
- Total Volume by Product & Country
- Total COGS by Product & Country
- A Personnel/Non-personnel cost split for each product
- The total number of person days as well as the daily rates per consulting services, staff group & country
This does not match 1-to-1 what we need for our driver model. However, we can derive additional information from the total data. For example, the average price of product can be calculated as revenue divided by volume.
Project Creation, Data Upload & Dimensions
Before the modelling and uploading the actual data, we need to create our dimensions. What dimension we use depends on the use case of the model. It is usually trade-off number of dimensions and number of nodes. In our case, we want to have following dimensions:
|Staff Group||Division of employees into tariff groups|
|Offerings||Classification of revenue streams and associated costs. The first level distinguishes whether it is a product or a service, the second level contains the product and service families.|
|Location||Countries by Region.|
Dimensions can be created through the dimension management interface.
First of all, create a dimension called 'Staff' and call the Level 1 'StaffGroup'. Then upload Staff.xlsx to fill in all the different values.
Next create a dimension called 'Offerings' naming the Level 1 'Product'. Now add the level 'ProductGroup' above Product. Afterwards you can upload Offerings.xlsx.
Now we can create the final dimension called 'Location' with a Level 1 called 'Country'. Add the level 'Region' above Country, like you did on the previous dimension. Upload Location.xlsx to finish this step.
The resulting dimensions should look as follows:
We follow it by uploading the Base data.xlsx. Please remember to name the Datasource 'Base Data' as shown below.
Defining the model hierarchy
Our main goal is to simulate the Profit & Loss (P&L) KPIs. However, putting everything into one single model would make the relationships nontransparent. Therefore, we create a following hierarchy:
To create such hierarchy, we create 6 empty models in Valsight:
The linking of submodels (as explained in Working with Submodels) is done via the 'Configure Submodels' button which is highlighted in the previous picture.
The following list explains which Submodels need to be linked:
1. Base Data
1. Base Data
|3.1 Products||2. Drivers|
|3.2 Services||2. Drivers|
|3.3 OPEX||2. Drivers|
3.1 Products, 3.2 Services, 3.3 OPEX
Congratulation on creating your first sub-model structure. Now go ahead and move to 2. MDE Submodels and fill it with data.