A data model is the foundation of every analysis you create. It is a high-level design that uncovers the analytic requirements of the customer. In Data Modelling you enhance your data and prepare it for analysis. SAP Analytics Cloud provides a range of options for Data Modelling.
You can create Planning and Analytic models in SAP Analytics Cloud.
Planning models allows you to set up budgets and forecasts, create private versions of data, copy-paste data, spread, distribute, and allocate.
Let us consider the below sample data. It is a pivoted table with columns Region, State, City, Store, Postal Code, Latitude, Product Category and Product showing the monthly sales information.
Let us see how this sample data can be modeled to be used in Story or Analytic Application.
Import your Data and Identify the Issues
SAP Analytics Cloud allows you to import data from various sources. You can check the full list here. You can create a model from the main menu in SAP Analytics Cloud. Since the sample data is an excel file, you need to choose the option Import a File from your computer and upload the sample data. Once the data is imported you can preview a subset of the data either in grid or card view.
The card view gives you an overview of all the columns imported. When you select a card the Details panel shows the details of the selected column.
- Column type
- Number of unique values (dimensions)
- The mean value (measures)
- Data quality indicated by a status bar
You can use the display option to search for a column and sort the dimensions and measures.
There are two major issues in the imported sample data.
1. Since the table is pivoted, Sales for each month are listed as separate measure columns.
2. Numeric Dimensions like Postal Code, Latitude and Longitude are identified as measures.
Unpivot the Columns
Select all the Monthly Sales columns and unpivot them. A new column for the month is created and the sales values are grouped in a single column which then can be renamed appropriately.
Identify Measures and Dimensions
Select the column that is wrongly marked as measure and change the type to Dimension.
With the use of the formula bar like excel, you can transform your data and remove any inaccuracies. The following are the five functions available.
You can also make use of the smart transformations suggested specifically for each column based on the preview data.
Validate the data and once there are no issues you can create the model and consume it in Story / Analytic Application. Apart from the basic transformations you can enrich your data with Hierarchies, Geo Locations, Formulas, etc. which is discussed in the next blog here.
To learn more about SAP Analytics Cloud, check out the series of blogs here.