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.

Leveraging Data Modelling in SAP Analytics Cloud – Part 1/2

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.

Leveraging Data Modelling in SAP Analytics Cloud – Part 1/2

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.

Leveraging Data Modelling in SAP Analytics Cloud – Part 1/2

Identify Measures and Dimensions

Select the column that is wrongly marked as measure and change the type to Dimension.

Leveraging Data Modelling in SAP Analytics Cloud – Part 1/2


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.

  1. Concatenate
  2. Split
  3. Extract
  4. Replace
  5. Change

You can also make use of the smart transformations suggested specifically for each column based on the preview data.

Leveraging Data Modelling in SAP Analytics Cloud – Part 1/2

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.

Corporate HQ:
5920 Windhaven Pkwy, Plano, TX 75093

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192


Copyright © Visual BI Solutions Inc.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!

Share This!

Share this with your friends and colleagues!