Hands-On Tutorial: Predictive Planning
Planning our sales for the next quarter of different product categories and countries is a time-consuming task since each will show different variations, thus requiring a separate planning for each product category and country. So why not use machine learning to predict these values for the next quarter to help our planning colleagues to make the planning more accurate and help them to be more efficient? Therefore, SAP Analytics Cloud with Smart Predict offers a toolset to the business user (in this example our planning colleagues) that addresses common predictive use cases. With this the business analyst can solve predictive use cases on his own.
This guide will walk us through the process of enriching a planning model with a sales forecasting for an online shopping portal that sells beauty and pharma products in 6 categories in 3 different countries.
Remark: The explained feature is delivered on wave 2020.11 and for now available on some tenant but for others it is planned QRC availability is for mid-August. So, it can happen that this feature is not available on your SAP Analytics Cloud tenant yet, but definitely something to look forward to?. While you are waiting and still want to do some time series forecast in SAP Analytics Cloud have a look at this blog post which uses a similar data set and shows you how to do the prediction in Smart Predict on a data set rather than on a planning model:https://blogs.sap.com/2018/10/31/hands-on-tutorial-sap-smart-predict-product-forecast/
|Sales||Amount of sales by product category|
|ProductCategory||Description shows the product category|
|CountryCode||Code C1-C3 for the country where the product was sold|
First, log on to a SAP analytics Cloud instance.
On the pop up we select the source file “product_forecast_planning.csv”, click “Import”.
Now, that we have uploaded the data set, we can start to finalize our model. We can check that all columns are detected correctly which means in detail: “Date” as a Date with the format “YYYYMM”, “Sales” as Measure and “ProductCategory” and “CountryCode” as Generic Dimensions. Furthermore, we need to select “Enable Planning” on the menu on the right to make sure that we can use this model for our planning purposed.
After this we click on “Create Model” on the lower right corner and confirm our decision by selecting “Create” on the pop up. To save this model we select a folder and give it a name like product_forecast_planning on the pop up that now appeared. Then we click “ok” and now we see that our model is being created.
On the screen that now appears we have the option to get an overview of the build model and also make some adjustments and few other things, but since such aspects are not on focus for this Hand-On tutorial we will start directly by creating a story based on this model.
We navigate to the menu and select “Stories” followed by “Canvas”.
We want to compare the time series forecast of product groups or different product categories from the same product group.
On the pop up we click on “Select other model” to choose the model we just built and then we click “ok”.
Our screen should now look like this:
Before we adjust the table so that it fits to our needs, we want to create a new and private version for our sales planning. To do this we click on the 3 dots under “More” on the upper bar and select “Version Management”. Small hint: make sure the table is selected. Otherwise, we can’t select the “Version Management”.
On the right we now click on “Copy”.
On the pop up we give the new version a name like “sales forecast” and select the Category “Forecast”.
After clicking on “ok”, we now see in the panel on the right that a private version was created.
We can close this in the lower right corner, so that we now can adjust our table so that it shows more than just one value. First, we click on “Add Measure/Dimension” in the builder panel on the right.
On the dropdown menu we select “CountryCode” and “ProductCategory”.
Now, we can see the sum of the actual values for each product category in each country.
This is a good start, but we want to see it on a monthly basis and plan the next quarter.
For this we choose the “forecast layout” from the dropdown menu which appears by clicking on the “CrossTable” icon in the builder panel on the right and confirm our selection with ”Ok”.
On the “Forecast Layout” panel that now appears on the right we select “Last booked actuals” as “Cot-over Date” and select “3” and “Month” for “Look ahead additional”. Then we click “Apply”.
Now our table should look like this:
We can click on the arrow behind “Q3” to see the monthly fields. As we can see the values for Q3 are empty.
For these 3 Months we now want to generate a forecast to support our business analysts to build their business plans faster and more accurate. But before we start the predictions, we need to save our story, by clicking on the little disc icon and selecting “Save”. On the pop up we select the folder where we want to save it and give it a name like “product_planning”.
Now we can start our predictions by selecting “… More” and then “Predictive Scenario” on the menu.
A predictive scenario set of use cases with common characteristics. SAP Analytic Cloud’s Smart Predict currently offers 3 predictive Scenarios: Classification, Regression, Time Series. The tool gives you guidance with a quick explanation and an example for each Scenario.
The user now has to follow 3 simple steps:
- Choose the predictive scenario that matches his use case.
- Train the model with historic data, i.e. use a data set where sales figures are known. The statistical algorithm will “learn” from this data set, i.e. find trends, seasonal variations and fluctuations that characterize the sale of a certain product. There should be enough (3-5 years) data available to learn from.
- Save the forecast back to the model, i.e. forecast sales for a given period of times. The statistical algorithm will apply the patterns learnt in the previous step to the new data and predict sales for the chosen number of time periods.
The variable that contains the sales in the learning phase and is predicted in the application phase is called the target variable.
The following screen shot shows the three options classification, regression and time series. Under each option there is a description to make it easier for the user to select the right scenario for each use case. In this exercise, we want to do a sales forecast for each product. Based on the descriptions of predictive scenario types, you can see that a time series will be able to address our needs. So we select Time Series at the bottom.
On the pop up we give the model a name, e.g. “product_planning_forecast” and save it in our preferred folder.
Now we can create our Predictions.
On the “Settings” panel on the right we need to select the input data for our predictive scenario. This data contains historical data that we use for training.
We select our planning Model “product_forecast_planning” from the folder.
The signal variable is our target variable, i.e. the variable to predict. We select “Sales” as the signal variable.
The date variable contains the time dimension. In this example the date variable refers to the date column in the data set.
Since we want to predict our Q3 on a monthly basis, we select “3” as “Number of Forecast”.
The “Entity” variable allows us to automatically create one time series for each member of an entity. Furthermore, we can select multiple entities. In our example we want to create one time series per country code and the product category. Therefore, we select “CountryCode” and “ProductCategory”.
We could define the last date for the training dataset, but we stick with the default setting, so we select “Process” as “All Observations” and “Until” as “Last Observation”.
We could choose the date of the last observation and we could define the last date. But for this scenario we leave it as it is.
Let’s run the predictive model with these settings.
We click “Train&Forecast” on the bottom right.
Please be patient since this might take a couple of minutes.
Upon completion of the training process, we see that a forecast was created for each country and product category. Among others, the horizon-Wide MAPE (Mean Absolute Percentage Error) is shown. It is the evaluation of the “error” made when using the model to estimate the future values of the signal. In this case you see the mean MAPE value of all the segments, the average quality of the segmented time series is 4.08%. In short just like in school we have to give our predictive model points. Based on these points the forecasts are listed in top and bottom entities.
We select one from the top segments, e.g. C6-perfume.
When forwarded to the next screen we see the detailed time series chart as well as all forecasts.
We see the following information in this line chart:
|Signal||The green curve||The information contained in the training dataset|
|Predicted Signal||The blue curve||The signal predicted by the generated model|
|Error Bars||The blue area around the end of the blue curve||
The zone of possible error where the predicted signal could be. The error bars are only displayed for the forecasts.
Note – The error bars are equal to twice the standard deviation computed on the Validation dataset.
|Outlier||A red circle||
A point where the predictive curve is very distant from the real curve.
Note – An outlier is detected when the absolute value of the residuals is over three times the value of the standard deviation computed on the Estimation dataset.
We go back and have a look at the bottom segments to see the prediction here. We can also check the other time series of interest. For some you may find outliers that have been detected by the tool. Outliers are values that we could not explain with the given data. These are indicators that we might include more explanatory variables like information about special events, marketing campaigns etc.
The outliers are points where the predictive curve is very distant from the real curve. They are represented by a red circle on the plot.
We could also use the drop-down menu on the top left to switch the entities.
We can also select signal analysis which displays shows the detected signals like cycles, trend and fluctuation.
The trend is the general orientation of the signal.
The cycles are periodic elements that can be found at least twice in the training dataset.
The fluctuation is what is left when the trend and the cycles have been extracted.
After we had a deeper look at the forecast and were convinced of its quality, we now can save it in our planning model. We click on the little factory icon on the top left to select the version, in this case our private version “sales forecast”, we created in the beginning and click on “Save”.
On the bottom we can see that the model is being applied. When it is finished, we have a look at the result.
We navigate to the menu and select “Files”.
We navigate to the folder where we saved our Story “product_planning” we build before and select it.
Our values for Q3 are now filled with the forecast we just created so we can use them as a starting point for our sales planning. Of course, we can adjust them manually in case our forecast is not sufficient, or we know things our data was not aware of. This way we can combine both our forecast based on the historic data and the expertise of our planning colleagues to get the best results.
As a last remark in this tutorial let me point out that SAP Analytics Cloud has a lot more visualization options than just the table we focused on in this scenario. So, you could enrich this story with more plots to make some findings and insights easy to consume and make better business decisions. Be creative and add the company logo or adjust the styling to the company’s corporate identity, add more data, nice charts etc. Just try it out.
The official SAP analytics Cloud tutorial and the playlist really helped me to get started:
Have fun with doing your own predictions and building amazing dashboards.
Great Blog Post Sarah! This hands-on approach really helps to get started with Predictive Planning in the SAC.
Very Comprehensive Sarah. Well done!
Very detail. Thanks so much
This is wonderful, thank you Sarah!
Great Blog! Very well explained. One small typo “create one time series per product category and the product category” – Product Category got repeated twice replacing one “Country Code”.
Hello Shashank, Predictive Planning can create up to 1000 time series models in one go. Feel free to contact me to explore your specific scenario together.
Antoine Chabert, Smart Predict Product Manager
Very nice...informative blog
cloud analytics is on forefront ....across all business solutions ....especially for top management of client end it makes difference....
Short update just in case that Predictive Planning is available on all SAP Analytics Cloud deployments since August 15th. We also have exciting developments coming in release 2020.20. https://www.sapanalytics.cloud/product_updates/release-2020-20/. Kind regards, Antoine Chabert (Predictive Planning Product Manager)
Thank you Sarah
Please can I have the csv file used for this presentation ?
How can i directly download the csv and load the file into SAC.. Or How can i directly use the GitHub Weblink data into SAC..
Kindly Help me out..
Hi, you can use the GitHub file once you downloaded it.
importing the CSV-File from Github hangs at 28%.
What am I doing wrong? Tried to import it with "autoselection" for the trim key...
Hi Uwe, for me when opening the link in Chrome, https://raw.githubusercontent.com/antoinechabert/predictive/master/product_forecast_planning.csv this shows like the following and I can copy/paste in a CSV editor or directly into Excel.
thank you for your quick reply. I can download the content from GitHub, that is not the point. But I cannot import the downloaded CSV into a Dataset. When trying to import into my SAC Trail, it stops at 28% and does not continue loading.
Hi, I'll give it a try. Best regards Antoine
Hi Uwe, I just tried it and it worked like a charm. I did not use a trial tenant though.
I suspect that there might be a temporary problem on trial tenants related to the data import service.
Maybe you can give it a try at a later stage. Also why not trying to use an Excel file instead of the CSV? If you face the same problem with Excel, this might confirm the existence of a problem with the data import service...
I tried to import Excel-Files, as well. With the same result.
I will try it again tomorrow and hope that the issue will be fixed, so far it is related to the import service.
Thank you very much for the moment.
The problem is with the SAC Trial tenant, it fails consistently with data imports. I already contacted the guys from "support" via various channels (re-directs to other teams, etc) with no results whatsoever.
This is really very frustrating!
Hi Uwe, Hi Walter, I suggest you raise this then as a dedicated question on the SAP Community using the tag "SAP Analytics Cloud".
The problem is not related to predictive as it appears.
I understand the frustration, let's try to raise awareness about this.
Thanks in advance & best regards
As WM already mentioned, this is really annoying.
I am actually preparing to go for the certification, but it seems useless as far as I cannot prepare for it in the right manner.
You must admit it would be a shame if I cannot use that tool in the way it should work before the trial expires...
I recently tried to write a question about this issue to the community, but the form could not be submitted due to some ******** no one understands.
By now, I have more trouble than progress with SAP. As I will not spend hours to find a right place for discussing about this issue, please move it to someone related, or send me some contact details / blogs where this stuff belongs to.
Thank you very much in advance.
Hi Uwe, I fully understand & share your frustration, yet as said I do not think the best route to solve it is through the comments of an unrelated blog.
To your question, someone else raised the same concern and we have an answer from one of our support engineers Peter KREUTLEIN - see https://answers.sap.com/questions/13810200/error-when-importing-any-data-source-either-csv-or.html. Thanks for your understanding and I hope this problem will be resolved soon.
Kind regards, Antoine
Thanks a lot for the nice blog. Very useful step by step illustration.
When training the model, I can see the Time granularity is forced to "day" (same as in my model). The result of such parameter makes the number of Forecast defined as 3 days. Therefore I have June 2nd, 3rd and 4th as forecasted values to push to my story/model. I would rather have months of July, August and September.
How to achieve this? Do I need to alter the data in the initial data import?
Many thanks in advance,
Hi Hatem, you are fully correct. You need to make sure the date formats you use correspond to monthly values, not daily values. Then the rest will flow (month in the planning model, month in the predictive model).
Thanks Antoine for the quick reply. That's clear now.
Another question regarding the Story layout. When setting up the Cross-tab Forecast layout in the table structure we define the "look ahead" value as 3 months. I tried changing it to 0 or 6 and I get the same result (apparently). I am not sure what it does?
Thanks for your clarifications
Hi, please can you post your question as a dedicated question in the SAP community. Doing this through post comments is not compliant with the SAP community recommended practices. Thanks for your understanding. Best regards Antoine
I tried to alter the initial data set and have all records show a date field with only mm.yyyy
After importing the granularity is set automatically to Day and my dates show as dd.mm.yyyy in my model.
How can I have the model granularity set to monthly or at least have the time granularity in my predictive model set to month?
Hi Hatem, when you create the planning model from the data, you have a way to specify the date format as MM.YYYY in the planning model - did you try this? if you would like to stick to the original data, you have to perform a data transformation to split the column based on . just keep the MM.YYYY part and it will recognize the right date format.
Can a Classification or Regression predictive scenario be based model data? It seems only Data Sets are available to be selected.
Correct - as of now Classification & Regression scenarios can only use datasets as a source. Hatem Ghanem in general can you please be so kind not to raise questions in blog comments but create dedicated questions? The community's etiquette asks for this. Thanks for the consideration, Antoine
The data file used in this article can be found here: https://github.com/antoinechabert/predictive/blob/master/product_forecast_planning.csv. Enjoy!