Working with Views for Multi-Dimensional Modeling

A view with semantic usage dimension is called a dimension view. It contains attributes that describe information about a business object such as a product or customer. For example, a product dimension view can contain attributes like color, weight, category, and price.

There are two variants of the dimension view:

• Standard: for general master data.
• Fiscal Time: for modeling fiscal time periods. It requires exactly two key attributes: a fiscal year variant identifier and a fiscal year period identifier.

A dimension view has the following properties:

• At least one key column must be set to uniquely identify each dimension member.
• All non-key columns become attributes available for analysis in an analytic model.
• Attributes can be time-dependent, allowing dimension members to change over time.

A Compound Key is a key made up of two or more columns that together uniquely identify a single record in a dimension and is used when source data contains values that are only unique in combination with another field.

A Representative Key is a single column within a compound key that holds the most granular level of data and is used when a dimension has multiple key columns and analytic tools (such as SAP Analytics Cloud) need a single field to access and represent the dimension members.

A view with semantic usage text is called a text view. It contains language-dependent descriptions that translate attribute values at query time in other entities into human-readable text in the user's language. For example, a product text view can translate a product ID into a product description displayed in different languages.

A text view has the following properties:

• It must include at least one identifier key column (matching the dimension it describes) and a language key column.
• The language field must carry the semantic type 'Language'.
• All text fields must be marked as text columns (semantic type 'Text').
• Up to three text lengths are supported: short (up to 20 characters), medium (up to 40 characters), and long (over 40 characters).

Hierarchies present parent-child relationships for dimension members, enabling drill-down and drill-up navigation in BI clients such as SAP Analytics Cloud. Hierarchies give business users the ability to navigate from a high-level summary down to a detailed level.

There are two approaches available in SAP Datasphere:

• Parent-Child Hierarchy: a parent-child hierarchy is recursive, and can have any number of levels. It requires a parent column and a child column of the same data type. The child column must be a key.

• Level-Based Hierarchy: a level-based hierarchy is non-recursive, and has a fixed number of levels. It is defined by specifying two or more level columns from highest to lowest level (for example year, quarter, month, day). It is not supported for dimensions with more than one key column.

Related to hierarchies, two options for semantic usage can be used:

• Hierarchy: to define a view with a parent-child hierarchy.

• Hierarchy with Directory: contains one or more parent-child hierarchies and has an association to a directory dimension containing a list of those hierarchies. This supports heterogeneous hierarchies where nodes can belong to multiple different dimensions. For example, a cost center hierarchy where nodes represent countries, regions, cost center groups, and individual cost centers.

If the hierarchy columns are already inside your dimension view, you can define the hierarchy directly there without creating a separate hierarchy view. You can choose between a parent-child hierarchy or level-based hierarchy.

If the hierarchy structure lives in a separate table or view you can use an association from the dimension view to a hierarchy view.

Associations are the semantic links that connect master data views to each other and to fact views. An association describes the relationship between two entities.

It offers the following features:

• Association of dimension to dimension enables a snowflake model where one dimension references another (for example city dimension references country dimension).
• Association of dimension with text enables the automatic display of dimension member descriptions in the user's language when an analytic model is consumed in SAP Analytics Cloud. When defining text associations, one attribute in the dimension must be mapped to each text entity identifier key column.
• Hierarchies (defined in a separate hierarchy view) can be associated to a dimension. Each key attribute in the dimension must be mapped to the hierarchy child attribute key column.

Associations are virtual. An important performance benefit of associations is join pruning. SAP HANA only executes joins to dimension tables that are actually needed for the current query. For example, if a user's report only uses product and time dimensions, the customer and region dimension joins are skipped entirely. This significantly improves query performance, especially with large datasets.

A view with semantic usage type fact is called a fact view and must have at least one measure defined, which is a column that can be aggregated (for example SUM, COUNT, AVG). The remaining columns are classified as attributes with appropriate semantic types.

A fact view sits at the center of the star schema, and can have associations pointing to one or more dimension, text, or hierarchy views. You can create associations to first-level dimensions (directly associated with the fact) as well as deeper dimensions reachable by following further associations across multiple levels.

Fact views are modeled in the propagation layer where individual datasets are combined, semantically enriched, and prepared for analytical consumption. A single fact view should serve as the source for multiple analytic models, each tailored to a specific business question without duplicating the underlying data model. An analytic model then combines the fact view and its associated dimensions into a rich, multi-dimensional model ready for consumption in SAP Analytics Cloud stories and reports.