There are multiple ways of creating models in SAP Analytics Cloud. In this post, we’ll show you how to create an analytical model using an Excel file.
For this demo, we have an Excel file with sales transaction data for some products. We want to import this file into SAP Analytics Cloud and begin to create our model.
Importing an Excel file
From the Home screen, go to the Create Model section and choose the option Import a file from your computer.
Once we select the Import Model from File option, we are presented with two options:
- Local System
- File Server
For this example, we’ll select File Server. To learn more about how to configure a file server location, please check out our post.
Click the File Server connection from the dropdown and then select the file you want to import.
Data wrangling with a sample
Initially, only a sample amount of data will be loaded and displayed in the Data Preparation window.
In the data wangling window (Figure 4) we can perform multiple transformations, such as:
- Fixing anomalies
- Filling empty cells
- Replacing data
- Deleting records
- Converting data to number or text
- Changing date format
- Changing from upper case or lower case
- Assigning dimension types to the data columns i.e. measure or dimension
- Mapping existing dimensions to the incoming data
Once all the transformations are completed on the sample data, it will be applied to the entire dataset.
The blue panel on the right side shows the information such as how many dimensions and measures will be created. We can convert a dimension to a measure or vice versa depending on the data type in the data preparation window before creating the model.
We can give the model a name 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. Since we’re creating an Analytical Model, we won’t check the Planning Enabled option.
If we select an individual column in the Data Integration window, it will show all the information related to product data and the data quality. Here, we’ve selected the Product dimension and see all the data related information such as the dimension type, data quality, and data distribution. This helps with fixing any data quality issues before the data is loaded into SAP Analytics Cloud.
In the same Data Integration window, we can view the columns in either the card view or grid view.
Card view displays the columns as tiles (shown in the Figure 9).
If we select a dimension and then click on the Eye icon, it displays all the related information specifically for that dimension in the right-side blue panel
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.
Click on the three dots “…” to see additional transformation functions like Concatenate, Split, Replace and Chance. Use the functions as per the requirements.
In the same window, we can 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.
We can use the Replace transformation function on the Product Group dimension data cell. First, 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.
Similarly, some of the cells have Accessories spelled incorrectly so we can select the cell and use the smart transformation to correct the incorrectly spelled data value.
On the right side blue panel window, we can see the option to change the dimension type. For now, let’s keep Product as the dimension. Similarly check all the columns for the dimension type and for any data quality issues.
For the Dimensions, we can add Attributes as well. To do this, select the dimension type and click on the Add Dimension Attributes. Select Description Property, or create the Parent-Child Hierarchy.
Creating a model
Our data has been cleansed, dimensions and measures have been assigned, and we are now ready to view the summary of the model.
The summary contains the total sampled rows, total number of columns and dimension and measures, and any data quality issues will be shown as well. Once everything is finalized, click the Create Model button.
Our model is created and we are taken to the Modeler screen where we can see all the created Dimensions and Measures. If needed, we can add additional dimensions, change the model, or individual dimension properties.
Here is the quick preview of a few created dimensions in Figure 20. As you can see, the IDs are automatically generated. Since we created dimensions from the transaction data so the members are assigned the system generated IDs.
We can go to the preferences of each dimension individually, or the entire model, and change the properties. We can select the product dimension preference as shown in Figure 21.
Enabling Data Access Control
We can change the Dimension type or Description, and enable the Data Access Control option.
Once this option is enabled, we can see that two new columns i.e. Read and Write are added to the dimension grid. This allows us to specify at the member level who will have read or write (or both) permissions on the dimension members.
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.
We 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.
Team and Individual members are selected for the product PDS10 and they both will have the write permissions to the dimension member.
We can schedule the data refresh from the file server so that data can be refreshed with the latest data as per an hourly or minutes basis based on the requirements.
We can schedule a job to refresh the data on a daily basis. To do this, we 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 completely deletes the existing transaction data in the model and imports the new data into the model.
- Append 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, we can select the Append option to add new records to the model.
Under Schedule Settings (the Calendar icon) option select Repeating radio button and for the frequency we’ll keep it as Daily. Provide the Start Date, End Date and Start time for the schedule. Save the schedule settings and job will be created.
Figure 27 shows that data has been refreshed successfully by the scheduled job.
The model is created and under Datasource we can see the View More option, which indicates that the job to refresh data has been scheduled.
We can now use our model to create a story. To learn more about models and wrangling, please visit our additional resources.