How to Use DataStore Objects in Mixed Modeling Scenarios with SAP BW/4HANA
If you are working with SAP BW/4HANA, you might have encountered the need to use mixed modeling scenarios, where data stored in a DataStore object is consumed in a calculation view in SAP HANA and then again consumed in a CompositeProvider in SAP BW/4HANA. This blog post will explain the benefits and challenges of this approach, as well as the best practices and tips to optimize your models.
What is Mixed Modeling?
Mixed modeling is a term used for scenarios where data from different sources and schemas is combined and processed in SAP BW/4HANA and SAP HANA. For example, you might want to use data from an external database or a flat file in your SAP BW/4HANA model, or you might want to leverage the advanced features of SAP HANA calculation views in your SAP BW/4HANA reporting.
Mixed modeling can offer several advantages, such as:
- Flexibility: You can use the most suitable tool for each data source and requirement, without being limited by the capabilities of one system.
- Performance: You can benefit from the parallel processing and in-memory capabilities of SAP HANA, as well as the OLAP cache and pruning features of SAP BW/4HANA.
- Innovation: You can take advantage of the latest functionalities and enhancements of both systems, such as dynamic tiering, currency conversion, or machine learning.
However, mixed modeling also comes with some challenges and drawbacks, such as:
- Complexity: You need to manage multiple artifacts and dependencies across different systems, which can increase the maintenance effort and the risk of errors.
- Consistency: You need to ensure that the data and metadata are aligned and synchronized between SAP BW/4HANA and SAP HANA, which can require additional steps and checks.
- Compatibility: You need to consider the compatibility and interoperability of different versions and releases of SAP BW/4HANA and SAP HANA, which can limit your upgrade options.
Therefore, mixed modeling should be used only when there is a clear business justification and benefit, and when the alternatives are not feasible or optimal.
How to Use DataStore Objects in Mixed Modeling Scenarios?
One of the most common use cases for mixed modeling is to consume data from a DataStore object (DSO) in a calculation view in SAP HANA, and then consume that calculation view in a CompositeProvider in SAP BW/4HANA. This can be useful when you need to perform complex calculations or transformations that are not possible or efficient in a CompositeProvider.
A DSO is a persistent object that stores data at the lowest level of granularity. It can be used for data consolidation, harmonization, cleansing, or delta management. A DSO has three tables: an active table that contains the current data records, a change log table that contains the delta records, and an activation queue table that contains the new records waiting for activation.
When you create a DSO in SAP BW/4HANA, the system automatically generates several views for reporting and extraction purposes. These views are:
- The External SAP HANA View: This is a calculation view that provides access to the active table of the DSO. It also includes master data attributes and texts, currency conversion, analytic privileges, and hierarchy support. The name of this view starts with
- The External SAP HANA SQL View: This is a database view that provides access to the active table of the DSO. It does not include any additional features or semantics. The name of this view starts with
- The Internal Database Views: These are database views that provide access to the active table, the change log table, or both tables of the DSO. They are used internally by SAP BW/4HANA for reporting and extraction. The names of these views start with
The recommended way to consume a DSO in a calculation view is to use either the External SAP HANA View or the External SAP HANA SQL View. These views are stable and supported by SAP. You should not use the internal database views or the tables directly, as they are subject to change without notice.
The choice between the External SAP HANA View and the External SAP HANA SQL View depends on your requirements and preferences. The External SAP HANA View offers more features and semantics, but it also adds more complexity and overhead. The External SAP HANA SQL View offers less features but more simplicity and performance.
To illustrate how to use these views in mixed modeling scenarios, let’s consider an example based on this blog post¹ by Dominik Graus. In this example, we have a DSO called DGADSO that contains data for calendar years 2013 to 2023. Data for 2013, 2014, and 2015 is in the cold store. The DSO has temperature schema ‘Hot’ and ‘Cold’ and dynamic partitioning by 0CALYEAR.
The system generates two views for this DSO:
- The External SAP HANA View
- The External SAP HANA SQL View
We want to consume this DSO in a calculation view called DGACV, which performs some calculations and joins with other tables. Then, we want to consume this calculation view in a CompositeProvider called DGACP, which provides the data for reporting.
The following steps show how to create and use these artifacts in SAP BW/4HANA and SAP HANA:
- In SAP BW/4HANA, create the DSO DGADSO with the desired settings and load the data.
- In SAP HANA, create a new calculation view DGACV with the desired settings and logic. In the projection node, add the External SAP HANA View or the External SAP HANA SQL View of the DSO as a data source. You can also add other data sources or nodes as needed.
- In SAP BW/4HANA, create a new CompositeProvider DGACP with the desired settings and logic. In the source tab, add the calculation view DGACV as a data source. You can also add other data sources or nodes as needed.
- In SAP BW/4HANA, create a new query on top of the CompositeProvider DGACP and use it for reporting.
How to Optimize Models in Mixed Modeling Scenarios?
When using mixed modeling scenarios, there are some best practices and tips that can help you optimize your models and improve your performance. Here are some of them:
- Use partitioning: Partitioning can help you distribute and process large amounts of data more efficiently. You can use physical partitioning or logical partitioning (with semantic groups) for your DSOs, depending on your data volume and query patterns. You can also use partition pruning to filter out irrelevant partitions based on your selection criteria.
- Use dynamic tiering: Dynamic tiering can help you manage your data temperature and storage costs. You can use dynamic tiering to move cold data from your DSOs to an extended storage device, such as a disk or a cloud service. This way, you can reduce the memory footprint of your hot data and improve your query performance.
- Use currency conversion: Currency conversion can help you handle different currencies in your data and reports. You can use currency conversion in your External SAP HANA Views to convert your key figures to a target currency based on an exchange rate table or a fixed rate. You can also use currency conversion in your CompositeProviders to convert your key figures to a global currency or a user-specific currency.
- Use analytic privileges: Analytic privileges can help you secure and restrict access to your data based on user roles and attributes. You can use analytic privileges in your External SAP HANA Views to define filters and conditions that apply to your data sources. You can also use analytic privileges in your CompositeProviders to define filters and conditions that apply to your queries.
- Use hierarchy support: Hierarchy support can help you display and navigate your data in a hierarchical structure based on master data attributes. You can use hierarchy support in your External SAP HANA Views to define hierarchies for your dimensions and measures. You can also use hierarchy support in your CompositeProviders to define hierarchies for your characteristics and key figures.
Mixed modeling scenarios with DataStore objects in SAP BW/4HANA and SAP HANA can offer flexibility, performance, and innovation for your data models and reports. However, they also require careful planning, design, and optimization to ensure consistency, compatibility, and efficiency.
In this blog post, we have explained how to use DataStore objects in mixed modeling scenarios, as well as some best practices and tips to optimize your models. We hope you have found this post useful and informative.
If you have any questions or feedback, please feel free to leave a comment below.
This content is generated by AI.