The SAP Datasphere Analytic Model
SAP Datasphere is the new generation of the SAP cloud product for Data Warehousing, which was published in March of 2023. It is the successor to the SAP Data Warehouse Cloud (DWC), and therefore serves as a counterpart to the established on-premise-products SAP BW on HANA and SAP BW/4HANA. The scope of functions is still somewhat restricted, but is growing steadily so that the Datasphere has potential to be an analytics product for more and more companies.
SAP considers the Datasphere to be the future product in the analytics environment to which all customers should migrate by 2040 at the latest, the planned end of support for BW/4HANA. Whether this will actually happen remains to be seen.
The Analytic Model and its positioning
The Analytic Model has been added to the Data Builder as a new modeling object that combines the functions of existing objects and which SAP calls “THE go-to analytic consumption entity for both Data Layer & Business Layer” (source: SAP).
The Data Builder primarily consists of tables and views to which a semantic use is assigned. The semantic “Analytical Dataset” describes the use for analytic reporting, e.g. with reporting applications such as SAC. The “Relational Dataset,” in contrast, is to be considered simply a table for use in other models or transmission to external systems. While the Relational Dataset continues to exist, the functions of the Analytical Dataset will be transferred to the new “Analytic Model,” and the Analytical Dataset will therefore become obsolete in the near future.
The Business Builder is designed to make it possible for certain business users with technical expertise to be able to create and edit data models, and combine central company data with their own data. Objects in the Business Builders are generally based on objects in the Data Builder. The following objects are currently available: Dimension, Analytical Dataset (not to be confused with the semantic Analytical Dataset in the Data Builder), Fact Model, Consumption Model and Perspective. Of these, Fact Model and Consumption Model will be transferred to the new Analytic Model, meaning these objects will also be obsolete in the foreseeable future. The Perspective as an interface to Reporting will also be replaced by the Analytic Model.
The new Analytic Model, therefore, is the successor for a total of four object types in the Data Builder and Business Builder.
The Analytic Model will be created in the Data Builder and can combine its objects. However, the plan is for objects in the Business Builder to also be able to be used as the source in the future. Therefore, the previous architecture that sees the Business Builder as being above the Data Builder will be replaced in that objects from the Business Builder can, in turn, be used in the Data Builder. It remains to be seen how this will impact the planned scenario in which business users edit objects in the Business Builder. If each change in the Business Builder requires an adjustment to the IT in the corresponding Analytic Model, this will not promote the push towards agility, and will significantly restrict self-service.
The functions of the Analytic Model
This section will first address the significant functions of the Analytic Model and illustrate their usage.
1. Definition of restricted and calculated key figures
Calculated columns are a frequently used modeling option, and are available in graphic and SQL Views of all kinds. In comparison to the Views in the Data Builder, however, calculation in the Analytic Model is not conducted on the single row level, but rather based on the aggregated data, depending on the drill down report and any set exception aggregation. Depending on the specific Data Model, this can have different results. It is important to check carefully in the design which calculation is completed and where. The option to define restricted key figures is entirely new in the Data Builder.
For Business Builder users, however, restricted and calculated key figures is nothing new; these functions were already and remain available there.
2. Analytic data preview in the familiar layout of the SAC Data Analyzer
Data preview is available in Views of the Data Builder. This option is even available at each individual node in graphic views. This preview is, however, always on the single set level without the option of aggregation. The data preview in the Analytic Model, in contrast, is based on the Data Analyzer, which is well known from SAC, and is suitable for previewing aggregated data, whereby Drilldown to the individual set level is also possible.
There is already the option of previewing aggregated data in the Business Builder, although this preview is very restricted compared to the new Analytic Model. The Analytic Model offers a true advantage over previous objects in this respect.
3. Join with texts and attributes, across multiple levels and with time dependency
So-called Associations can be used to associate a field in the transaction data to a master data object (Dimension or Text), and thereby provide texts and attributes for the analysis. This corresponds to the use of Info objects with texts and attributes in SAP BW systems, and was already possible for the existing objects in the Data Builder and Business Builder. However, the new feature is that Associations are also available as Attributes for fields of an associated Dimension. This is possible across multiple levels, and is therefore a significant advantage over previous DWC objects and even over SAP BW, which allows this only over two levels in the form of transitive attributes. The following figure, for example, shows a fact table “SalesView Test,” in which the Dimension Products is associated via the field PRODUCTID. The Product Dimension, in turn, has associated the Dimension with User with the field CREATEDBY and CHANGEDBY with master data for the user.
Texts and attributes can be created with corresponding validity intervals on a time-dependent basis. The date of validity is defined via a corresponding variable when executing the report.
Unfortunately, thus far it is not possible to rename the fields, so the example contains the fields “First name” and “Last name” twice: once for the creator and once for the last person who made changes.
4. Data Pruning of associated dimensions: only actually required Joins are executed
This is also a function familiar from the Business Builder, which is now also available in the Data Builder. Regardless of which dimensions are associated in the Data Model, if applicable across many levels, only the tables of such dimensions are actually read and linked to the transaction data that are necessary for the requested drill down report.
In other objects of the Data Builder associated dimensions quickly result in poor performance, in particular with large quantities of data, because all dimensions are always loaded and joined with each other.
Since the Analytic Model is executed in the MDS Engine, unfortunately it is not easy to create and analyze an SQL Statement in the development environment, as it is the case with Views. However, different Calculation Views are visible via the database explorer that reflect the Analytic Model and can be used for performance analyses (cf. Figure 3). Alternatively, the SQL function SYS.EXECUTE_MDS can transfer the corresponding JSON statement as a parameter and then the results can be analyzed.
To test the Pruning function, first a query is created that only displays the gross value per period. The associated PlanViz file confirms that only the tables SalesOrders and SalesOrderItems are read (cf. Figure 4).
The product or user table is only read if attributes of the associated dimensions are included in the query.
This confirms that actually only the required dimensions are accessed; in comparison to the previous objects of the Data Builder, it is no longer harmful to associate all sources with one another.
Based on the four key functions, we have shown that the Analytic Model offers multiple new functions for the Data Builder. Compared to the Business Builder, however, the advantages are very limited and the disadvantages are fundamental, in the form of missing functions. SAP is aware of this and plans to integrate all functions of the Consumption Model into the Analytic Model, in order to replace the Fact and Consumption Model of the Business Builder after the Analytical Dataset of the Data Builder.
Currently, the missing functions include:
- Integrating multiple data sources for transaction data
- Using the Analytic Model as a source for other Analytic Models
- Sharing the Analytic Model with other Spaces
By the time the Analytic Model covers all functions of the Business Layer objects, at the latest, it will be the central object for Reporting. To keep later migration work as minimal as possible, it is important to already consider implementing new data models with the Analytic Model now.
What will become of the Business Builder?
It is not possible to fully answer this question here. In the author’s opinion, the Analytic Model greatly improves the Data Builder, and leaves many question marks when it comes to the Business Builder. In the near future, the full scope of functions of the Business Builder objects will be available in the Analytic Model, so the data models can also be implemented without a Business Builder. There is also the advantage of a differentiation in responsibilities between IT and department. But what advantage is it for the department to create Dimensions and Analytical Datasets if every structural change then has to be updated by IT in the Analytic Model? One possibility would be a separate Space in which the department create their own Analytic Models with business-specific logics based on Analytic Models shared by IT. Therefore, the end of the Business Builder seems to be foreseeable.
This post was first published at https://www.nagarro-es.com/en/analytics/the-sap-datasphere-analytic-model