The focus of this course is the analytical query. Before we get into the details of the analytical query, let's develop a basic understanding of the underlying data model that the analytical query consumes.
Hint
You may skip this unit if you are already familiar with the ABAP CDS based star schema definition, or you prefer to focus first on the analytical query. You can always return to this unit later.
A CDS analytical projection view defines an analytical query. But what are the data sources for the analytical query?
The data source for an analytical query is a cube.
A cube is the central component in the well-known industry-standard data model known as a star schema. A star schema provides a comprehensive data set that is based on a specific business subject or a business process, such as order-to-cash or procure-to-pay. A star schema is a reusable data source that can be consumed by many analytical queries.
The star schema provides a data model that supports sophisticated multidimensional analysis. Multidimensional analysis allows business users to slice and dice their data using various perspectives. For example: sales by region EMEA, or returns by country and product type for 2024.
A business user can also explore the data by drilling-down or rolling-up using a hierarchy. For example, using a geographic hierarchy, a business user might begin by displaying a summary of the sales for each continent. Then, they might choose a continent that looks interesting and drill down to display sales for each country in the chosen continent. Finally, they choose am interesting country and drill down to display sales for each city in the chosen country. As the business user drills down the hierarchy following their chosen path, they expose more granular data to gain better insight.
When you develop a CDS analytical projection view, you select the attributes and measures from the underlying star schema and then apply query actions on that data, such as filtering, sorting and aggregating, to generate powerful insights.
CDS analytical projection views are often built to define an analytical query that answers a specific business question. For example: Which products were the top sellers this year, but were poor sellers last year?
In contrast to a query, a star schema provides a broad data set for an entire business subject. A star schema is a highly reusable data model that is consumed by multiple queries. Each query selects only the data that it needs from the star schema. We create many analytical queries on top of a star schema to generate focused, business-context data sets that can be consumed by analytical applications. Analytical applications do not consume the star schema.
Launch the video to learn about the star schema.
To summarize, the key capabilities of a star schema include:
Combine all relevant data sources into one consistent structure using joins and unions.
Add additional technical and business semantics to the raw data to ensure it is interpreted in the correct way by the analytical tools.
Define associations to the related dimensions to enhance the data stored in the cube by adding descriptive attributes.
Define relevant data authorizations to the data for each business role.
At the center of a star schema is the core transactional data which is represented by the cube. The cube is bound to one or more dimensions.
You implement a star schema by developing a cube view, multiple dimension views, and optionally text views and hierarchy views.
Although the focus of this Learning Journey is not the development of a star schema, anyone who develops a CDS analytical query should be familiar with the elements of the star schema so they know what data is available.
Let's take a quick look at each element:
Cube View
Provides transactional or business event data such as orders, shipments, and payments.
Includes one or more numerical values, known as measures, such as sales revenue and quantity sold. Some measures include a unit of measurement or a currency to give them meaning.
- Includes key attributes based on the main entities of the transaction, such as customer, product, order date. The values of the attributes are often meaningless to the business user and an association to a dimension view is required to provide meaning.
Dimension View
Dimension can be regarded as master data.
Dimensions provide the basis for analyzing data in the cube using a combination of attributes across one or more dimensions.
As well as the key value such as product ID, dimensions also include additional display attributes, such as product color, weight, price and so forth, which give further insights for data exploration.
Attributes can be defined as time-dependent so that you can track the historical values of those dimensions that will change over time, such as the increasing price of a product.
We have so far covered the basic star schema. Let's now go deeper and investigate the extended star schema.
The extended star schema includes additional views that provide useful information to the business user. These views include text views and hierarchy views.
Text View
A text view provides descriptions that give meaning to each attribute. For example, to provide a product description for each product number, or to provide an employee name for each personnel number.
Text views can also manage language-dependency. This means business users will only see the descriptions of each attribute in their local language.
Hierarchy View
Each dimension can have an association to a hierarchy view. For example, the dimension product might be associated with a hierarchy view that organizes the components into a structure that describes the bill of materials.
The hierarchy view can be used to structure the data in a query to support drill-down and roll-up by the business user.