Introducing SAP Analytics Cloud

Discussing Data Modeling

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Use data modelling to create data models

SAP Analytics Cloud Models

Styles of Model

A model represents a table of data. Example: Sales, Expense, and HR etc.

SAP Analytics Cloud recognizes the following styles of models:

Planning Model

Planning models are preconfigured with dimensions for time and version. They offer support for multi-currency and security features at the level of both model and dimension. When working with a planning model in a story, users with planning privileges can create their own versions of model data. These users can also write data to the model by typing new values, copying and pasting data, and using data actions.

Analytics Model

Unlike the planning model, the analytics model does not support categories, and does not require a time dimension. It is used for stories and analytic applications that are read-only, for data presentation and analysis purposes.

SAP Analytics Cloud Data Sources

The figure below shows the comparison between datasets, planning models, and analytic models.

Note

Both datasets and models can be used as data sources in stories.

A shareable live dataset can be created for SAP HANA. Live datasets can also be created from a story based on SAP Data Warehouse Cloud.

Creating an SAP Analytics Cloud Model

The features of SAC models are as follows:

  • Transparency of data

  • Re-usable

  • Data can be enhanced

  • Required for planning

  • Flexible: Can be used in various data integration scenarios

Watch this video to learn about the data source options for creating a simple SAP Analytics Cloud model.

Viewing Options

When creating a model, SAP Analytics Cloud has two viewing options:

  • Structure View

  • Data Foundation View

Click each highlighted option to navigate between the Structure view and the Data Foundation view.

Let's go into the details of each viewing option.

Structure View

The Structure view shows you a star schema diagram representing the contents of your model. This view helps you visualize how your fact data, attributes, and properties all relate to each other.

At a glance, you can see the model's dimensions surrounding the data foundation.

Additionally the dimension box shows you more information based on the dimension type. For example, the version dimensions shows how many public versions there are, and whether there are any private versions. For date dimensions, the information about granularity and default hierarchy, and (if added) whether the Fiscal Year setting has been applied.

If you want to add a new dimension or existing dimension to your model, you can do that from the toolbar, or from the Schema view.

Data Foundation View

The Data Foundation view shows you the fact table containing the raw, non-aggregated transactional data loaded into your model. The total number of results is the total number of rows of data, not including any filtering you have applied, across all versions.

In planning models, you can also switch between the different public versions of your data. For example, to see if data exists for a selected version

The Structure and Data Foundation views work together with the dimension list and details panel to give you a consistent picture of your data. For example, if you select a dimension in the schema diagram, the other views all focus on that dimension.

The New Model

The classic account model only allows one data/measure column in the transaction data table and therefore relied heavily on accounts to describe values for data such as revenue, cost, and administration.

The new model allows multiple data columns. For example, local currency amount, group currency amount, and quantity. Not all modeling features are supported in the new model, but will be added shortly. Eventually, only the new model will be available, but classic account models can be converted to New Models.

Benefits of the New Model

The New Model offers the following main benefits, both for analytic and planning use cases:

  • Flexible model structure - Since both accounts and measures are available as structures for your data, you can display your data more precisely for a variety of use cases, such as:
    • Accurate aggregation: Aggregating data over measures instead of accounts opens up some new options. For example, you can generate your balance sheet and profit and loss statement out of a single model.
    • Explicit data types: Measures can be set up as integers or decimals. By setting different types of data for different measures like monetary amount, number of units, operating hours, etc, you can avoid incorrect data; for example, using decimals when planning on headcount values.
    • Disaggregation of data for both integer and decimal measures: With an integer measure, you can avoid decimals while still distributing the entire value
  • Optional account dimension - When you structure your data with measures, you can add your accounts to a generic dimension instead of an account dimension. This option allows you to avoid limitations on the account dimension.
  • Improved calculations
    • Calculated measures in models: Since measure calculations can be added to your model, you can reuse them across different stories and analytic applications and also add them to some data action steps.
    • Calculations on numeric dimension properties: Using this feature, you can change account values from positive to negative based on dimension properties. For example, you can create calculations that show either positive or negative values for expenses, which makes it easier for different types of users like controllers or accountants to analyze and plan on the same data.
  • Enhanced currency features
    • Base currencies and conversion measures in models: You can add multiple base currency measures to your model and then add currency conversion measures on top of them. This way, you can model and plan on multiple currencies like transaction, local, or group currencies. With currency conversions available in the model, you can use them in formulas as well as data action copy steps.
    • Planning across currencies: In tables, you can plan on any base measure or conversion measure, such as local, transaction, and group currencies, and instantly see the results across dependent currencies. And you can apply currency conversion while copying data between base measures with a data action.
  • Data integration - The measure model matches the structure of data from several other SAP systems more closely, including SAP S/4 HANA, SAP BW, and SAP IBP. Data integration is generally faster and requires fewer transforms because you can import data from multiple measures directly instead of turning them into account members.
  • More clear terminology for charts and tables - By removing non-financial data from members of the account dimension, you can make data in charts and tables easier to understand for your non-accounting/financial viewers and content creators.
  • Flexible model structure - Since both accounts and measures are available as structures for your data, you can display your data more precisely for a variety of use cases, such as:
    • Accurate aggregation: Aggregating data over measures instead of accounts opens up some new options. For example, you can generate your balance sheet and profit and loss statement out of a single model.
    • Explicit data types: Measures can be set up as integers or decimals. By setting different types of data for different measures like monetary amount, number of units, operating hours, etc, you can avoid incorrect data; for example, using decimals when planning on headcount values.
    • Disaggregation of data for both integer and decimal measures: With an integer measure, you can avoid decimals while still distributing the entire value
  • Optional account dimension - When you structure your data with measures, you can add your accounts to a generic dimension instead of an account dimension. This option allows you to avoid limitations on the account dimension.
  • Improved calculations
    • Calculated measures in models: Since measure calculations can be added to your model, you can reuse them across different stories and analytic applications and also add them to some data action steps.
    • Calculations on numeric dimension properties: Using this feature, you can change account values from positive to negative based on dimension properties. For example, you can create calculations that show either positive or negative values for expenses, which makes it easier for different types of users like controllers or accountants to analyze and plan on the same data.
  • Enhanced currency features
    • Base currencies and conversion measures in models: You can add multiple base currency measures to your model and then add currency conversion measures on top of them. This way, you can model and plan on multiple currencies like transaction, local, or group currencies. With currency conversions available in the model, you can use them in formulas as well as data action copy steps.
    • Planning across currencies: In tables, you can plan on any base measure or conversion measure, such as local, transaction, and group currencies, and instantly see the results across dependent currencies. And you can apply currency conversion while copying data between base measures with a data action.
  • Data integration - The measure model matches the structure of data from several other SAP systems more closely, including SAP S/4 HANA, SAP BW, and SAP IBP. Data integration is generally faster and requires fewer transforms because you can import data from multiple measures directly instead of turning them into account members.
  • More clear terminology for charts and tables - By removing non-financial data from members of the account dimension, you can make data in charts and tables easier to understand for your non-accounting/financial viewers and content creators.

In the figure New Model Measures Price and Volume are the two data columns in the underlying transaction data table.

In the figure New Model Calculations the Price_x_Volume calculated measure is available to any story using this model. The calculated values are not stored in the underlying transaction data table.

In the figure New Model - Currency Conversion the converted measure is calculated on-the-fly and is not stored in the database.

Limitations of the New Model

As of February of 2022, the following limitations exist for the new model:

Limitations to Account/Measures based Formulas and Calculations

  • The Link Formula between models is not supported. An alternative is to use the LINK function in an advanced formulas step or cross-model copy.
  • Formulas and calculations on the Account dimension are similar to the classic account model. Currently the time-dependent formulas Year-over-Year, Compound Annual Growth Rate, and Simple Moving Average are not supported when using measures.
  • Cross-model copy steps in Data Actions do not support copying between classic account models and models with measures. Instead, you can use the LINK function in an advanced formulas step.

Limitation to Stories and Applications

  • Blending is not supported when the model contains an Account dimension.
  • Table Thresholds are not supported when the model contains an Account dimension.
  • Geo maps are not supported.
  • There is no support for copying and pasting data between currency enabled measures.
  • Measures can only be sorted via Edit Member Order.
  • Stories originally created using classic account models are not migrated and must be adjusted to the new model.

Limitations to Import of Data

  • Dimension members must exist first. Prior to fact data import, you need to create dimension members in the modeler, or import data to a global dimension.
  • Models created from a CSV/Excel file or data source are still created as classic account model.

Limitations to integration with Microsoft Office

  • Using the new model type with SAP Analytics Cloud, add-in for Microsoft Office, you must define a default currency for currency variables in the model.
  • If you migrate a classic account model already used with the add-in, you need to re-insert that model into an existing workbook created with SAP Analytics Cloud, add-in for Microsoft Office.
  • You cannot use the new model type with the add-ins SAP Analysis for Microsoft Office and SAP Analysis for Microsoft Office, edition for SAP Analytics Cloud.

SAP Analytics Cloud Dimensions

In SAP Analytics Cloud, dimensions and measures are data objects that represent categorical, transactional and numerical data in a dataset; for example, Products, Sales or Revenue. A model can have any number of dimensions.

Measure is a dimension that represents transactional data (note that this data is not visible in the Modeler). In a classic account model, model values are stored in a single default measure, and you use the account structure to add calculations, specify units, and set aggregation types for all the data. In the new model type, measures are exposed as single entities, and you can add and configure multiple measures with aggregation and units to fit your data.

Dimensions can be private or public.

Let's compare private and public dimensions.

SAP Analytics Cloud Dimension Types

Dimensions can be qualified as a specific type of dimension. Depending on the type selected, you can configure various properties and possibly create hierarchies for the dimension.

Apart from the built-in dimensions for timestamp and versions, all dimensions have three basic columns (properties): ID, Description, and Hierarchy. These cannot be deleted, but additional properties can be added as required.

Now, let's look at each dimension type in detail.

The Account dimension type includes a set of unique properties:

  • Account Type
  • Rate Type
  • Units and currencies
  • Formula
  • Hierarchy
  • Aggregation information
Note
There are many aggregation types available in SAC: average, last, first, rank, sum. Aggregation behavior can be specified in account dimensions, calculated measures in stories as well as the New Model.

Dimension Formulas

Formulas allow you to create new measures based on existing measures. They are similar to Measure Calculations in a story, but creating them in a model makes the calculation available to all story designers using that model.

Watch this video to learn how to use a dimension formula that has been created in a model.

SAP Analytics Cloud Hierarchies

Hierarchies

A hierarchy is used to establish relationships within your data. For example, suppose you have sales data for the following:

  • Global

  • Continent

  • Country

  • State/Province

  • City

A hierarchy allows you to organize these geographic areas into logical levels (also called nodes), from largest area to the most granular.

Hierarchies are formed by the existence of a property in the dimension. The hierarchy property is a standard feature of all dimension types so that you can structure your data. The Hierarchy property is used to store the parent ID value.

The account dimension has only a single hierarchy, but you can add additional hierarchies to all other dimension types by selecting Add Hierarchy from the Add menu (+ symbol) on the toolbar. When you choose this option, a new Hierarchy property is inserted in the grid, and you must enter the name of the new hierarchy in the column header row. If more than one hierarchy is defined, users can use the Manage Hierarchies feature, or apply filter settings to select which hierarchy to apply and to switch between hierarchies.

The hierarchy is visualized in the preview panel. You do not need to enter or edit the data in the data grid to make a hierarchy. You can use drag and drop in the preview panel to arrange the members and build the relationships. If more than one hierarchy has been defined, you can select the one you want to display in the preview from the drop-down list.

Types of Hierarchy

Two types of hierarchy are available:

  • Level-Based Hierarchy

    A level-based hierarchy organizes multiple dimensions into levels, such as country, state, and city.

  • Parent-Child Hierarchy

    A parent-child hierarchy organizes the members of a single dimension into a set of parent-child relationships.

Level-Based Hierarchy

The figure Level-Based Hierarchy shows dimensions with a level-based hierarchy (structure hierarchy).

Watch this video to learn how to create a level-based hierarchy.

Parent-Child Hierarchy

The figure Parent-Child Hierarchy shows dimensions with parent-child hierarchy (value hierarchy).

Parent-child hierarchies can be maintained in various ways:

  • Manually in SAP Analytics Cloud
  • Import from files
  • Import from SAP systems, such as SAP BW and SAP S/4HANA
Note

When working with parent-child hierarchies for a planning model, avoid situations where data can be booked directly to a parent node.

These situations include the following:

  • Structuring two or more hierarchies so that a member is a leaf node in one hierarchy and a parent node in a different hierarchy.

  • Updating a hierarchy so that a leaf node that has a value booked to it is changed to a parent node.

General Information about Hierarchies

A dimension can have one or more level-based hierarchies, or one or more parent-child hierarchies, but not both.

The following types of dimensions do not support custom hierarchies:

  • The account dimension has only a single hierarchy.

  • For the date dimension, hierarchies are predefined based on the model granularity, and whether you have enabled fiscal time for the model. You can specify a default hierarchy in the settings for the date dimension.

  • The version dimension does not have a hierarchy, as different versions are separate and do not have parent-child relationships.

Data Wrangling

As previously discuss in this course, data used in SAC models for stories and analytical applications may need to be modified for reporting and analysis purposes. For example, data from a source system that is imported into an SAC model may have multiple columns for employee names — first name and last name. But for stories and analytic applications, designers may need only one column that combines both first and last names.

Wrangling Functions in a Data Model

When importing data into SAC, the data wrangling options include:

  • Transformations: You can click on a column or cell and create a transformation. The options are: concatenation, split, extract, replace, change, filter.

  • Custom Expression Editor: You can create formulas. This editor has all of the transformation options and more advanced data transformation.

Transformation Example

The figure Transformation Example is an example of using the classic wrangling interface.

Custom Expression Editor

Sometimes you may need to use a more robust editor to manipulate data. The Custom Expression Editor provides this functionality.

The figure Formula and Expression Editor Example is an example of using the Formula and Expression Editor interface.

An example of the syntax to add a prefix to a dimension is as follows:

Code snippet
CONCAT("EXT-",[COSTCTR])
Copy code

An example of syntax to convert a date field format from 20200315 to 01/15/2020:

Code snippet
CONCAT(SUBSTRING([Posting Date],4,2),CONCAT('/',CONCAT(SUBSTRING([Posting Date] ,6 ,2 ),CONCAT('/',SUBSTRING([Posting Date] ,0 ,4 ))))) 
Copy code

Combine Data from Two Sources

Another option for wrangling data includes combining data from two sources.

The figure Wrangling - Combine Data from Two Sources is an example of this concept.

Let's look at an example of data wrangling that combines data from two sources.

Watch this video to learn how to create a new model based on an Excel file.

Create an Analytic Model from a File and Wrangle Data

Prior to an upcoming board meeting, the HR department handed you an Excel file to incorporate into the board presentation. You must upload the file to a story and use a series of transformations to clean up and format the data.

In this exercise, you will acquire and wrangle data from an Excel file in SAP Analytics Cloud. You will learn how a series of quick transformations can help clean up data in the application.

Task Flow

In this exercise, you will perform the following tasks:

  • Import data from a file.
  • Explain data manipulation (grid view).
  • Create quick transformations to concatenate and extract the data.
  • Create a level-based hierarchy

Save progress to your learning plan by logging in or creating an account