Designing and Creating Dimensions

In SAP Analytics Cloud, dimensions usually represent qualitative master data. Product, cost center, and employee are all examples of dimensions. A model can, and typically does, have several dimensions.

A measure represents transactional or quantitative data; for example, price, revenue, number of customers. Measures are distinct from dimensions, so you can add and configure multiple measures with aggregation and units to fit your data.

In an account-based model, all the numeric values are stored in a single default dimension usually named Account, and you use the financial account structure to determine what each value represents. You can also add calculations, specify units, and set aggregation types for those values. Semantically, an Account dimension type may be referred to as a measure because it fulfills a similar purpose; however, it is really a dimension.

Dimensions can be public or private.

Public dimensions are used much more frequently in SAP Analytics Cloud modeling than private dimensions simply because of their versatility. As independent entities, they can be managed and used much more easily than private dimensions. Private dimensions, however, are excellent choices for single- or infrequent-use situations.

Public dimensions are stand-alone entities that are created independently and can be used in multiple models. They are saved separately from models, meaning models only reference public dimensions. This means that if a model that contains a public dimension is deleted, the public dimension remains intact because it is not dependent on the model. Also, if you copy a model that contains public dimensions, then the public dimensions referenced in the model are not duplicated.

Typically, the values for the dimension members are imported into the dimension. Because the data is imported, it is often scheduled to repeat the import process on a regular basis to ensure that the dimension always reflect accurate information.

Public dimension tables are typically stored in the file repository in the My Files→Public→Dimension Tables folder, so that they can be easily reused. However, they can also be saved elsewhere in the file repository and shared with the required users.

Private dimensions are created directly in a model, so they are model-specific and cannot be reused in other models. If the model containing the private dimension is deleted, the dimension is also deleted because it is part of the model. The same is true if you copy the model; the private dimension is duplicated in the copy.

Since the dimension is created during the model creation process, the data values for the dimension members are not populated until the data for the entire model structure is imported into it. And while data imports into models can be scheduled, the private dimension cannot be singled out for its own scheduled import.

Dimensions are always qualified as a specific type. This Type qualification gives SAP Analytics Cloud information on how the dimension should function within the system. Depending on the type selected, you can configure various properties and possibly create hierarchies for the dimension.

When creating a public dimension, you can select the Account, Generic, and Organization type.

There are two other dimension types required by planning models that the system creates automatically for every planning model: Version and Date.

The Account dimension type typically conforms to financial-type accounts or accounting-related data such as utility costs or number of product returns.

• There are several system-generated properties, and more can be added.
• The default hierarchy is system generated, and more can be added.
• It is mandatory in an account model but optional in a standard model.
• Only one per model.

The Generic dimension type is used for any data that logically cannot be qualified as any other dimension type. You can also use the Generic dimension type if you need more than one of the other dimensions types in your model. For example, your model may need multiple dimensions related to an organizational structure, but since a model can have only one Organization dimension type, all the other organization-related dimensions can be qualified as Generic.

• Properties can be added as needed.
• As many hierarchies as needed can be added.
• It is optional in a model.
• More than one per model is allowed.

The Organization dimension type represents an organizational structure, such as cost centers, profit centers, or business units.

• The Currency and Person Responsible properties are system-generated, and more can be added.
• As many hierarchies as needed can be added.
• It is optional in a model.
• Only one per model.

The Version dimension type is system-generated for all planning models and defines the data versions for planning activities: Planning, Actual, Forecast, etc.

• Properties can be added as needed.
• Hierarchies are not applicable to this dimension type.
• It is mandatory (system-generated) in all planning models; not applicable for analytic models.
• Only one per model.

The Date dimension type is a system-generated dimension that defines the start and end dates of the model's time frame and specifies the granularity of time used in the model (years, quarters, months, weeks, or days).

• Properties:
  • Account model: system-generated only.
  • Standard model: added as needed, but only when user-managed.
• Hierarchies:
  • Account model: system-generated only based on calendar time but can be changed to fiscal time.
  • Standard model: added as needed, but only when user-managed.
• It is mandatory (and system-generated) in all planning models, but optional for analytic models.
• More than one per model is allowed. It is helpful if you need to compare dates for different scenarios, such as Order Date versus Shipping Date.

Each dimension type has unique properties that are system-generated, and depending on the dimension type, you can easily add as many custom properties as you need.

While ID, Description, and Hierarchy appear as a columns in the dimension table, they are not properties.

The following properties are system-generated:

• Currency
• Person Responsible

These properties are used for currency conversion, data access control, data locking ownership, and to hide unauthorized hierarchy nodes.

The following example displays properties for the Organization dimension type.

The following example displays properties for the Account dimension type.

The following properties are system-generated:

• Formula : Used to calculate values such as Gross Margin %.
• Account Type : Used to allow sign reversal from positive to negative.
• Calculated On
• Aggregation Type*
• Exception Aggregation*
• Scale
• Decimal Places
• Units Type
• Hide

These properties are used for currency conversion, data access control, data locking ownership, and to hide unauthorized hierarchy nodes.

* There are many aggregation types available in SAP Analytics Cloud: average, last, first, rank, sum, etc. Aggregation behavior can be specified in account dimensions, calculated measures in stories, as well as a measure-based model.

There are no system-generated properties, but you can add any that you need, and they can be used for currency conversion, data access control, data locking ownership, and to hide unauthorized hierarchy nodes.

The Category property is system-generated.

This property is used for data access control and data locking ownership.

There are many system-generated properties related to time such as Day, Week, Month, Day Name, and more.

These properties are used for time hierarchies and data mapping.

Hierarchies let you structure your data. You can create hierarchies during the data preparation stage, or within the modeler after you’ve created a model. While creating hierarchies during the data preparation stage instead of in the modeler is faster and more automated, any subsequent adjustments or changes need to be made in the modeler.

Two types of hierarchy are available:

1. Level-based hierarchy: A level-based hierarchy organizes the members of a dimension into levels, such as Country, State, and City.
2. Parent-child hierarchy: A parent-child hierarchy organizes the members of a dimension into a set of parent-child relationships.

In this lesson, we use a simplified example of geographical data for city, state, and country to show you the difference between the two hierarchy types.

You can add level-based hierarchies to generic dimensions and organization dimensions. A level-based hierarchy organizes the members of a dimension into levels, such as Country, State, and City. In this example, a hierarchy allows you to organize these geographic areas into logical levels (also called nodes), from the largest area to the most granular.

The following example shows the Stores dimension with four properties. When you create a level-based hierarchy, you drag and drop the properties into the hierarchy in the Hierarchy Builder. In the table widget on the right, you can see the cities have been organized into the appropriate state.

The following video, we explore a second way to create a level-based hierarchy by combining individual dimensions to create one dimension with multiple properties.

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

The following example shows the Stores dimension, but this time with a list of members. In the dimension table on the right, you can see the cities have been organized into the appropriate state, which are all part of the country.

You create a parent-child hierarchy from the dimension table, starting the same was as a level-based hierarchy. In the example, you can see:

1. A dimension with members including Country, State, and City.
2. When you add a parent-child hierarchy, a new column is inserted into the grid. Once the parent-child hierarchy is created, select it to create the structure.
3. In Hierarchy Maintenance, drag and drop members to the build the parent-child relationships visually.
4. If more than one hierarchy has been defined, you can select which one to work with from the dropdown list.
5. The parent-child hierarchy appears in the newly created column. You can also update the hierarchy from the dimension table by manually inserting the members.

Parent-child hierarchies can be maintained manually in SAP Analytics Cloud or imported from files or SAP systems.

Now that we covered level-based and parent-child hierarchies, there are a few other points to consider:

• You can set or change the default hierarchy for any type of dimension, for both level-based and parent child-hierarchies, in the hierarchy builder.
• In a standard model, multiple account hierarchies are allowed, but an account model, the Account dimension type has only one system-generated parent-child hierarchy.
• Dimension types that allow multiple hierarchies can have either one or more level-based hierarchies or one or more parent-child hierarchies, but not a combination of both.
• Types of dimensions that don't support custom hierarchies:
  • For the Date dimension type, 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. In a standard model, additional custom hierarchies may be added. We will cover custom time hierarchies in more detail later in the course.
  • The Version dimension type does not have a hierarchy, because different versions are independent from one another and do not have parent-child relationships.

You can find more information on hierarchies, including managing hierarchies and various hierarchy scenarios in the About Hierarchies section of the SAP Help Portal.