Skip to Content
Product Information
Author's profile photo Suryansh Bajpai

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

 

 

 

 

 

Assigned Tags

      12 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Mary Johnson
      Mary Johnson

      This is very helpful. My previous challenge was importing Excel file to create a model in SAP analytic cloud. But your guide has clarified a lot of things. Thank you for taking your time to put this together.

      Regions Bank hours

      Author's profile photo Suryansh Bajpai
      Suryansh Bajpai
      Blog Post Author

      Hi Mary

      I appreciate your feedback, happy to help the community.

       

      Thanks

      Suryansh Bajpai

      Author's profile photo Mallikarjuna Kuruva
      Mallikarjuna Kuruva

      Hi Suryaansh,

      I have attended SAC planning workshop training on last Wednesday, currently I am practicing same on my cloud system.

      We need to maintain Currency rate table  for the model,but for me it is getting as empty for that field. Which was populated automatically in the training. Not sure why I am getting like that, Am I did any thing wrong while creation? suggest me how to add SAP_Currency_Rates in the field.

       

      Author's profile photo Sridhar V
      Sridhar V

      Hi Mallikarjuna,

      You need to create a currency table first so that it is available to consume in the model.

      Go to  –> Create –> Currency –> create the table or import from SAP BPC (choose one of the option)

       

      Thanks

      SV

      Author's profile photo Joseph Yeruva
      Joseph Yeruva

      Thank you for the blog. Can you please give the link for sample file used in this blog.

      Author's profile photo Krishna Porandla
      Krishna Porandla

      Can you please share the Excel file used in the blog to practice.

      Appreciate your quick response.

      Author's profile photo Murali G
      Murali G

      Can you plz share excel file to download for the practice

      Author's profile photo Murali G
      Murali G

      Can any one plz share sales data excel file used in planning webinar ?

      Author's profile photo Ragunathan R
      Ragunathan R

      Nice blog to start practice. Appreciate your work.

      Author's profile photo Michael Wanyera
      Michael Wanyera

      Good article but lacks some steps and an excel file. Seems to be economical in disclosing some info.

      Author's profile photo Pradipta Satpathy
      Pradipta Satpathy

      Hi Suryansh,

      Can you please provide the link of excel file or the sample dadtaset that you have used?

      Thanks

      Author's profile photo Dolan Bhowmick Srimal
      Dolan Bhowmick Srimal

      Hi Suryansh,

      This is a good article. However, I had a doubt and would appreciate your help. Instead of doing a copy paste of the master data, is there a way to upload all the master data from the source file?

      Thanks.