Step by step guide to create a Planning Model in Sap Analytics Cloud using Excel file

Hi All

In this blog we shall look a scenario to create a Planning Model in Sap Analytics Cloud for Planning. We shall use the test data provided by SAP to populate the data in our model.

Here we go–

Step 1 –  Login into the SAC for Planning

Step 2 – Creating a Planning Model

a- Go to widget –> Select Create –> Model

b- Select “Start with a blank Model”

c- Below screen will open, Now from this point we shall start adding Dimensions to the Planning Model. These Dimensions will be enhanced with Hierarchies and Attributes.

d – Click on “Add New Dimension” and provide the name of Dimension as per the requirement, In this case we shall create 5 Dimensions- MEASURES, PRODUCT, CUSTOMER, SALESORG and ENTITY.

Create “Measure” dimension of type Account, This dimension will hold all the KPIs like Sales Revenue and other Transactional Data. Click on Add.

We shall maintain these KPIs later in this blog and continue with creating other dimensions.

Now Add more dimensions of generic type which will hold the Master Data.

Properties of the Dimension can be changed with the “Dimension Setting” panel on the right.

Click on “PRODUCT” dimension from the list and copy paste Master Data in Product Dimension from Excel File.

Similarly other 3 Dimensions will be created.

e – Create “CUSTOMER” dimension. In this dimension we shall be using Level based hierarchy and for that, properties to be created for Customer Dimension.

After adding new dimension Customer, click on “Create Property”.

In this case L1, L2 and L2 levels will be created.

Click on “CUSTOMER” dimension from the list and copy paste Master Data in Customer Dimension from Excel File.

Now Level based Hierarchy can be created on Customer dimension and for that click on “Create Hierarchy” and select “Level-based Hierarchy”.

Click on “+” button and create a new Level Based Hierarchy “H1” and then click Ok.

f- Create “SALESORG” dimension. Sales Organisations are generally linked to “Legal Entity” for Financial Accounting or Financial Planning, hence “ENTITY” property will be created in SALESORG dimension.

Hierarchy will also be created SALESORG dimension.

Sales Organisations can have different local currencies, hence we select “Enable Currency” button.

Now copy paste the Master Data in the dimension. Hierarchy can be displayed by clicking on “Hierarchy Management” button.

g – Finally the “ENTITY” dimension will be created. We are creating this dimension just for the linkage in our planning model.

“Country” property will be added to this dimension if we want to create dashboards on this model.

Hierarchy will be enabled for reporting purpose. Currency will be enabled.

Similarly copy paste the master data from excel file to this dimension.

We can check the hierarchy for this dimension with “Hierarchy Management” button.

Step 3 – Managing Currency Translation

To manage currency translation of the model, click on “Model Preferences” button.

Go to the “Currency” tab and enable “Currency Conversion”

Select “Currency Rate Table” and click on Ok.

Step 4 – Maintaining planning KPIs

In this step we shall maintain our planning KPIs in the “MEASURE” dimension and to do that click on the dimension and copy paste the measures(Key Figures).

We can see there are various operations are available related to measures eg. Formula, Aggregation, Exception Aggregation.

Step 5 – Saving the Model’

Click on “Save” icon on the model page and provide a suitable name for your model and save at a suitable location.

Step 6 – Uploading Transactional Data into the Planning Model

To add Transactional data into our planning model, click on “Data Management” tab on model homepage.

In our model we are using Excel file to add transactional data into our model and for that click on “Import Data” and select “File”.

Browse correct file and select the correct sheet (if your file have multiple sheets) to load the data and click on “Import”.

Data uploaded successfully and added to draft sources. (This works as a staging location).

Step 7 – Data Mapping

In this step we shall map the fields from the source to our model dimensions.

Double Click on the Uploaded file in the “Draft Sources”

Generally system tries to match the respective fields automatically like in our case, but if there is some mismatch we can do that manually by simply dragging the fields from our Data Source and dropping on the model dimensions.

Import Methods options on the right can be used when there is already data available in the model and we are loading some new data on top of that.

Now to map the versions, click on “Map Versions”

Select category as “Actuals” and click Ok.

To have a Data Preview, Click on “Grid View”. If we want to perform any transformation on our data,we can do in Data Preview.

Click on “Finish Mapping” button on the right side panel and now the data will be imported from Staging area to the Fact Table of the underlying model. Import job has successful in our case, if there is issue we can check that in the status.

Import Jobs can be scheduled for periodic refresh of the data from the “Schedule Setting” option.

Till this step we have created a Planning Model, defined dimensions, created Hierarchies, created Properties, enabled Currency Conversion, Mapped the model dimensions, loaded the Master Data and Transactional Data.

This model can be further used in Stories to fully leverage the planning functionalities.

In the next blog I’ll create a Planning Story and consume this model and data to showcase the planning functionalities.

Thanks