Create Analytical Model using Excel File and Schedule data Refresh from File Server

Creating Analytical Model using the Excel File

SAP® Analytics Cloud is a next-generation software-as-a-service (SaaS) solution for Analytics, Planning and Predictive features. It is built on SAP HANA® Cloud Platform. SAC can connect to different types of on-prem and cloud based data sources. Within SAC we have 2 types of connections i.e. the Acquisition type of connection where data is downloaded into the SAC and Live connection where data is real time.

As the first step setup SAC tenant, second step is to create the connection between SAC and Data Source. After the connection is established between SAC and Data source, the third step is to create models on top of Data sources. Once the Model is created, one can create Analytics, do Planning on the financial data and use Predictive features to generate some predictive analytics using the model.

In SAC we have 2 types of Models. The Analytical Model and Planning Model. A Model consists of Dimensions and Measures.

Analytical Model : It consists of Account dimension which is the required dimension and can have ‘n’ number of generic dimensions.

Planning Model: It consists of Time, Category, Account and Organizational dimensions which are the required dimensions. Like Analytical Model it can have ‘n’ number of generic dimensions.

While we can create Models from different data sources, in this blog, we will show how to create model and load the Transaction data using Excel file in SAP Analytics Cloud Model.

There are multiple ways of creating a Model in SAC. We will try to create Analytical Model in SAC using the “Import a File from your computer” option i.e. create Analytical model using a Excel file.

We have a file with data and using this file we will create a model in the SAP Analytics Cloud. Figure 1 shows the Excel file Sales transaction data for Products.

Figure: 1

Let’s login into the SAP Analytics Cloud tenant, from the Home screen go to the create model section. Here we can see multiple options to create a Model. We will choose the option “Import a file from your computer” as we will create the model using a file.

Figure 2

We can load the file from the local computer or from the file server. We will try to load the file from the File server.

Once we click on the “Import Model from File” option window it shows two options: “Local System” and “File Server”. Let’s select “File Server”.

Check the blog on How to configure a file server location : (https://blogs.sap.com/2017/10/24/how-to-configure-file-server-for-importing-data-and-exporting-a-model/ )

Figure 3:

We have already configured the file server location within SAC and connection is created. Select the File server connection from the dropdown and the folder where the file with data is stored as shown in the Figure 4.

Figure 4

Figure 5

Initially only a sample amount of data will be loaded and displayed in the Data Preparation window.

Figure 6

Figure 7 shows the data wrangling / data preparation window. In the data wangling window you can perform multiple transformation like fixing anomalies, fill empty cells or replace data, delete records, convert data to number or text or change date format, change the records to upper case or lower case, assign dimension types to the data columns i.e. measure or dimension, map existing dimensions to the incoming data and once all the transformations are completed on the sample data, it will be applied to the entire dataset as well.

The right side blue panel shows the information like how many dimensions and measures will be created. You can convert a dimension to measure or vice versa depending on the data type in the data preparation window before creating the model.

Figure 7

Let’s give a name to the model : “Sales_Model_SS” and specify the default currency in the data preparation window. If it’s a Planning model then make sure to check the box “Planning Enabled” option. Here we are creating Analytical Model so we won’t check the option “Planning Enabled”.

    Figure 8

Figure 9

In the Data Integration window if we select the individual column it will show all the information related to product data and the data quality. Here let’s select the Product column dimension and see all data related information like the Dimension type, data quality and data distribution. This helps in fixing any data quality issues before the data is loaded into SAC

Figure 10

In the same Data Integration window we can view the columns in the card view or grid view.

Figure 11

If we select the card view, all the columns are shown as shown in the figure 12.

Figure 12

Select a dimension and click on the “eye” icon to focus on the dimension. On the right side blue panel shows all the related information about the dimension. Let’s go back to the grid by clicking on the grid icon.

Figure 13

Transformations : To perform transformations on the data, select the desired column or the dimension and click on the transformation icon. This will show the transformation options like Duplicate column, Delete column and Delete rows.

Figure 14

Click on the three dots “…” to see additional transformation functions like Concatenate, Split, Replace and Chance. Use the functions as per the requirements.

Figure 15

In the same window we can also create the Geo / Location dimension either by using the Coordinates i.e. Latitude and Longitude or By Area i.e. create the Location dimension using the Country or State names and this option currently is only available to US region only.

Figure 16

Let’s try to use Replace transformation function on the Product Group dimension data cell. Select the “Bag and Outdoor” data cell, select the “Replace transformation” function and in the formula provide the correct data value i.e. “Bag & Outdoor as shown in the figure.

  Figure 17

Figure 18

Similarly some of the cells have Accessories spelled wrong so let’s select the cell and you can see the smart transformation suggestions by clicking on the three dots “…”. Simply select the suggestions for “Accessories” and “Office” to correct the wrongly spelled data values.

Figure 19

Figure 20

Figure 21

Figure 22

Figure 22 shows final data where all the data values have been corrected i.e. “Accessories”, “Office” and “Bag & Outdoor”.

On the right side blue panel window , you can see the option to change the dimension type. For now let’s keep “Product” as the dimension. Similarly all the columns are checked for the dimension type and for any data quality issues.

Figure 23

For the Dimensions we can add Attributes as well. To do this select the dimension type and click on the “Add Dimension Attributes” and select the Description option or make it a property or create the parent-child Hierarchy. After selecting the Attribute type, select the column from the dropdown. In this blog we have selected all the columns and assigned them as dimension and measures. We are not assigning any attributes to the Dimensions.

Figure 24

Data has been cleansed , dimensions and measures have been assigned. Lets name model as “Sales_Model_SS” as shown in the figure 25. You can observe the summary of the model i.e. the total sampled rows, total number of columns and dimension and measures. If there are any data quality issues those will be shown as well. Once everything is finalized, lets click on the “Create Model” button to create the Model.

Figure 25

Now Model is created and it takes us to the Modeler screen where it shows all the created Model Dimensions and Measures. Here you can add additional dimensions, change the Model or individual dimension properties if needed.

Here is the quick preview of few created dimension in Figure 26. As you can see, the “ID”s are automatically generated. Since we created dimensions from the transaction data so the members are assigned the system generated ID’s.

Figure 26

We can go to the preferences of each dimension individually or the Model and change the properties. Let’s select the product dimension preference as shown in the Figure 27.

Figure 27

We can change the Dimension type or Description. Let’s enable the Data Access Control option here (Figure 28). This options enables us to set the data access security at the member level. Once this option is enabled you can see that 2 new columns i.e. “Read” and “Write” are added to the dimension grid. You can specify at the member level who will have read or write or both permissions on the dimension members.

Figure 28

Figure 29

We can assign individual members or teams as well. Select the “PDS10” member and assign a Team and individual member i.e. Team name and Individual member name under the Write column for the member PDS10. You can select the cell and click on the browse button and a new window with teams and individual name will appear. From that window select the desired team or a member or both. This means that the members of the team and individual member will have Write permission to the selected product member.

Figure 30

Team and Individual members are selected for the product “PDS10” and they both will have the write permissions to the dimension member.

Figure 31

Model is successfully created and data is loaded as well.

Data Schedule:

We can schedule the data refresh from the file server so that data can be refreshed with latest data fortnightly or hourly or minutes basis based on the requirements.

Figure 32

Let’s now schedule a job to refresh the data on daily basis. To do this let’s go to the Datasource tab and select the Datasource. On the right side panel under “Settings” – “Import Settings”, we can see 2 options i.e. “Clean and Replace” and Append.

“Clean and Replace” option completely deletes the existing transaction data in the model and imports the new data into the model.

“Append” option simply adds the data into the model. It doesn’t delete any existing data within the model.

Since we have already loaded data into the Model so lets’ select the “Append” option to add new records to the model.

Figure 33

Under Schedule Settings (the Calendar icon) option select “Repeating” radio button and for the frequency let’s keep it as “Daily”. Provide Start Date, End Date and Start time for the schedule. Save the schedule settings and job will be created.

Figure 34

Figure 35 shows that data has been refreshed successfully by the scheduled job.

Figure 35

Model is created and under Datasource you can see the “View More” option which indicates that the job to refresh data has been scheduled.

Figure 36

Let’s use the Model and create a sample story to make sure data has been loaded correctly. From the Home screen lets go to the Story section.

Figure 37

Let’s create a new Story and select the page type as “Canvas”.

Figure 38

Select the “Chart” object

Figure 39

Let’s select the created model from the dropdown box.

Figure 40

Let’s add the dimensions and measures to the chart.

Chart Type: Bar Chart

Measures: Net_Sales

Dimensions: Product and Product Group.

Check for the Data integrity and for any data quality issues.

Figure 41

We have successfully created a model from the Excel file stored in the file server and appended the data into the Model by scheduling a job. We created a simple story and added a chart to make sure data has been loaded correctly into the model.