Dear SAP BW enthusiasts!
Do you remember the “BEx Reporting” flag in the maintenance of ODS objects in SAP BW 3.x years ago? Even if this is not the case, this blog post might be interesting for you: The flag I refered to has evolved to the often underestimated ADSO property called “Master Data Check”. Here I would like to briefly summarize its significance.
The general term “SAP BW” below refers to SAP BW 7.4 or 7.5 powered by HANA and SAP BW/4HANA (any release) as well.
As you might know, SAP BW relies on SIDs, technical surrogate IDs to manage data. In German it is simply “Stammdatenidentifikationsnummer”
. Amongst other benefits, they ensure the reusability of centrally managed InfoObject master data in SAP BW. These SIDs also boost the query performance, because processing these technical IDs is more efficient compared to the semantic master data values. For this latter reason, the BW query runtime provided by the BW Analytic Manager (aka “OLAP Engine”) relies on SIDs and cannot provide reporting without them.
In the past, with the “BEx Reporting” flag you could simply control if the data should be available for reporting or not. If yes, SIDs were created during the data loading process.
In BW 7.x, ODS objects were renamed to DataStore objects (DSO) and this feature was enhanced: There are three modes for the so-called “SID Generation” property:
Finally, the ADSO provides the same three modes. However, the big difference is that the setting is not on the object level as in the past, but rather on the field level. As a result of this, you can define for an ADSO for each single InfoObject if SIDs are generated and when.
SID generation for InfoObjects:
The Master Data Check property is available Characteristics only, except of “Attribute only” or “High Cardinality”.
SID generation for Fields:
The Master Data Check property does not apply to fields. During reporting SIDs are created on-the-fly to enable processing by the OLAP engine. In general, fields are available for reporting under certain conditions as listed in SAP note 2185212.
ADSOs provide the additional mode to persist the SIDs in an additional column in the data model. Unlike InfoCubes in the past, the ADSO does not manage any SIDs in data model at all, but it relies on the semantic values only. As a consequence, during reporting the SIDs for all data have to be derived by additional JOIN-operations. In cases where these JOINs must be executed very frequently this can have a significant negative impact on the performance. To avoid this, the option “Master Data Check During Load/Activation and Persist SID in DataStore” creates an additional INT4 column in the ADSO table of active data for the purpose of saving the InfoObject SIDs as well. This column is filled during activation. See below example for the InfoObject “Product ID”:
Persisting SIDs is especially beneficial if some of the following conditions are met:
Conversion to SAP BW/4HANA
If classic DSOs are transferred into ADSOs with the SAP transfer tools provided for InPlace/Shell/Remote conversions, the “SID-Generation” mode is migrated 1:1 to the corresponding “Master Data Check” for all InfoObjects. So in the resulting ADSOs all Characteristics will have the same value for this property.
A little customizing option is provided by SAP here: You can use a RSADMIN parameter to decide globally which master data check mode should be used in the target ADSO if the source is a classic DSO with the SID Generation set to “Never Create SIDs”. Refer to SAP note 3024244 for more details.
Changing the “Master Data Check” mode
As long as ADSOs are empty changing them is not problem at all. However, if they contain data already, things can get very complex especially if you would like to change to from a “lower” mode to “higher” one (e.g. from “No Master Data Check/No Reporting” to any other mode). In this case additional remodeling jobs are required to adapt the system to the new mode. For more details refer to my blog Role of Remodeling in the ADSO Change Management Process.
Summary
If reporting performance is on the table, the ADSO Master Data Check provides some interesting options for improvement. It is good to have a proper understanding of this feature to avoid complexity during changes of your data models at a later point of time.
References
For additional details refer to following SAP sources:
Okumaya devam et...
Do you remember the “BEx Reporting” flag in the maintenance of ODS objects in SAP BW 3.x years ago? Even if this is not the case, this blog post might be interesting for you: The flag I refered to has evolved to the often underestimated ADSO property called “Master Data Check”. Here I would like to briefly summarize its significance.
The general term “SAP BW” below refers to SAP BW 7.4 or 7.5 powered by HANA and SAP BW/4HANA (any release) as well.
As you might know, SAP BW relies on SIDs, technical surrogate IDs to manage data. In German it is simply “Stammdatenidentifikationsnummer”
. Amongst other benefits, they ensure the reusability of centrally managed InfoObject master data in SAP BW. These SIDs also boost the query performance, because processing these technical IDs is more efficient compared to the semantic master data values. For this latter reason, the BW query runtime provided by the BW Analytic Manager (aka “OLAP Engine”) relies on SIDs and cannot provide reporting without them.In the past, with the “BEx Reporting” flag you could simply control if the data should be available for reporting or not. If yes, SIDs were created during the data loading process.
In BW 7.x, ODS objects were renamed to DataStore objects (DSO) and this feature was enhanced: There are three modes for the so-called “SID Generation” property:
- During Reporting: Loading data is fast, but reporting might be slower, because missing SIDs must be created when a BEx Query processes the data the first time.
Suitable for objects rarely used for reporting. - During Activation: SIDs are created during the data activation, so data loading will take longer. However, reporting is not impacted as all SIDs are already available.
Suitable for objects regularly used for reporting. - Never Create SIDs: The DSO is not available for reporting. You cannot define a BEx Query on it and it is impossible to add it to a MultiProvider/CompositeProvider.
Suitable for objects never used for reporting, e.g. in the corporate memory.
Finally, the ADSO provides the same three modes. However, the big difference is that the setting is not on the object level as in the past, but rather on the field level. As a result of this, you can define for an ADSO for each single InfoObject if SIDs are generated and when.
SID generation for InfoObjects:
The Master Data Check property is available Characteristics only, except of “Attribute only” or “High Cardinality”.
SID generation for Fields:
The Master Data Check property does not apply to fields. During reporting SIDs are created on-the-fly to enable processing by the OLAP engine. In general, fields are available for reporting under certain conditions as listed in SAP note 2185212.
ADSOs provide the additional mode to persist the SIDs in an additional column in the data model. Unlike InfoCubes in the past, the ADSO does not manage any SIDs in data model at all, but it relies on the semantic values only. As a consequence, during reporting the SIDs for all data have to be derived by additional JOIN-operations. In cases where these JOINs must be executed very frequently this can have a significant negative impact on the performance. To avoid this, the option “Master Data Check During Load/Activation and Persist SID in DataStore” creates an additional INT4 column in the ADSO table of active data for the purpose of saving the InfoObject SIDs as well. This column is filled during activation. See below example for the InfoObject “Product ID”:
Persisting SIDs is especially beneficial if some of the following conditions are met:
- The ADSO manages a high volume of data (roughly >100m records).
- There is a core InfoObject with a high cardinality.
- There is a core InfoObject with frequently used navigational attributes.
- There is a core InfoObject with complex hierarchies.
- There is a core InfoObject which is compounded to another InfoObject.
Conversion to SAP BW/4HANA
If classic DSOs are transferred into ADSOs with the SAP transfer tools provided for InPlace/Shell/Remote conversions, the “SID-Generation” mode is migrated 1:1 to the corresponding “Master Data Check” for all InfoObjects. So in the resulting ADSOs all Characteristics will have the same value for this property.
A little customizing option is provided by SAP here: You can use a RSADMIN parameter to decide globally which master data check mode should be used in the target ADSO if the source is a classic DSO with the SID Generation set to “Never Create SIDs”. Refer to SAP note 3024244 for more details.
Changing the “Master Data Check” mode
As long as ADSOs are empty changing them is not problem at all. However, if they contain data already, things can get very complex especially if you would like to change to from a “lower” mode to “higher” one (e.g. from “No Master Data Check/No Reporting” to any other mode). In this case additional remodeling jobs are required to adapt the system to the new mode. For more details refer to my blog Role of Remodeling in the ADSO Change Management Process.
Summary
If reporting performance is on the table, the ADSO Master Data Check provides some interesting options for improvement. It is good to have a proper understanding of this feature to avoid complexity during changes of your data models at a later point of time.
References
For additional details refer to following SAP sources:
- SAP Blog – SID Tables in SAP BW
- SAP BW 7.5 help – DataStore object (classic) settings
- SAP BW/4HANA help – Creating Advanced DataStore objects
- SAP BW/4HANA help – Analytic Manager (aka OLAP engine)
- SAP Wiki – Analytic Manager
- SAP note 01681396 – How To Analyze the Query Performance
- SAP note 2400004 – How To Check ‘HANA Pushdown’ of OLAP features
- SAP note 2185212 – ADSO: Recommendations and restrictions regarding reporting
- SAP note 3024244 – ADSO: Master data check mode for classic DSO without SIDs
Thanks for Clemens, Sören and Dirk for your input to this blog post.
Okumaya devam et...