Discussing Data Modeling

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.

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.

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.

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 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.

• 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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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:

CONCAT("EXT-",[COSTCTR])

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

CONCAT(SUBSTRING([Posting Date],4,2),CONCAT('/',CONCAT(SUBSTRING([Posting Date] ,6 ,2 ),CONCAT('/',SUBSTRING([Posting Date] ,0 ,4 ))))) 

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.