aDSO: Validity and Reference-Point tables

As mentioned in my post Storage data target type in BW Request Management there are several tables that store data for aDSO objects. Except those very well know like active, inbound, change log tables there are also others.

In case the aDSO is type of Inventory:

There are also below two tables available (followed by namespace):

Validity Table: /BIC/A<technical name>4

Reference Point Table: /BIC/A<technical name>5

 

A purpose of the inventory-enabled aDSO is to manage noncumulative key figures. A non-cumulative measure, in the context of data analysis or statistics, refers to a metric or variable that does not accumulate or aggregate over time or across categories. In other words, it represents a single point or snapshot value rather than a total or sum.

E.g. if there is a need to analyzing sales data for a particular day, the number of units sold on that day would be a non-cumulative measure. It doesn't consider sales from previous days; it just reflects the sales for that specific day.

Non-cumulative measures are often used when you need to examine data at a specific point in time or within a specific category without considering historical or cumulative values. They are particularly useful for analyzing trends, patterns, or comparisons within discrete units of analysis.

The tables are available also via t-code RSMNG in Utilities menu: Display Validity tab and Reference-Point tab:

BW/4HANA 2.0: External SAP HANA SQL View

There is a new feature in BW/4HANA 2.0 starting with Initial Shipment Stack. In this version of the SAP BW4/HANA 2.0 is external SAP HANA SQL View introduced. The new view follows one of principles of SAP BW4/HANA, which is openness.

In older releases of SAP BW4/HANA there wasn’t dedicated SQL view to support an extraction from aDSO objects. As SAP doesn’t recommends to use active table of the aDSO for extraction (see SAP Note 1682131) due to the fact that those tables are no publicly released. In the case of SAP BW4/HANA versions lower than BW/4HANA 2.0 SAP recommends to either use the HANA Modeler to import BW InfoProvider metadata and generate a HANA Model or to generate the HANA Model for BW InfoProvider from the BW backend.

Now with the respect to BW/4HANA 2.0 the new feature brings a new SQL view that is supposed to be used for an extraction of SAP BW objects like aDSOs or InfoObjects (see SAP Note 2723506). A naming convention for the new SQL view ends *8. That means on top of other DDIC tables related to the aDSO object there is a new table /BIC/A<aDSO_tech_name>8. E.g./BIC/AZMMA_ADSO8 - View for external Access for DataStore ZMMA_ADSO.

A full list of DDIC tables/views for my example aDSO is ZMMA_ADSO:

 

TABLES:

/BIC/AZMMA_ADSO                Inbound Table for DataStore ZMMA_ADSO

/BIC/A ZMMA_ADSO1              Inbound Table for DataStore ZMMA_ADSO

/BIC/A ZMMA_ADSO2              Active Data Table for DataStore ZMMA_ADSO

/BIC/AZMMA_ADSO3               Change Log for DataStore ZMMA_ADSO

VIEWS:

/BIC/AZMMA_ADSO6               View for Extraction from DataStore ZMMA_ADSO

/BIC/AZMMA_ADSO7               View for Reporting for DataStore ZMMA_ADSO

/BIC/AZMMA_ADSO8               View for external Access for DataStore ZMMA_ADSO

 

The views ending with 6 and 7 were generated by BW4/HANA 1.0 and view ending with 8 was generated in BW4/HANA 2.0. From my experience (based on BW4/HANA 2.0 SP05) the *8 view doesn’t get generated by aDSO activation right away. In my case it was generated at the time I loaded the data into the aDSO for 1^st time.

 

More information:

How to read/write/delete from/to aDSO objects

1682131 - SAP BW tables in SAP HANA Information Views and ABAP CDS Views not supported

2723506 - External SAP HANA SQL View with SAP BW/4HANA 2.0

Package based aDSO activation

In SAP BW, aDSO (advanced DataStore Object) or DSO (DataStore Object) is used as a persistence layer for storing and processing data. In case of e.g. standard DSO a data activation is the process of moving the data from inbound table of the DSO to active table. Data is aggregated accordingly within this process. See Flavors of aDSO object for more details.

In case there is a huge number (10k+) of data loads requests in specific aDSO objects and they all are being activated together there can be problems. Mostly with the respect to memory issues.

This is solved in the newer BW releases (7.50, BW/4HANA 1.0, BW/4HANA 2.0, BW/4HANA 2021) while introducing so called package based activation. This type of the data activation is available when the activation runs over Process chain – by process variant "Clean Up Old Requests in DataStore Objects (Advanced)". Here in this process s package size can be defined for specific aDSO object by a user. If it is not defined, a default package size of 10k is used.

The value itself is stored in table RSPCVARIANT (Generic Variant Storage):

The new way of the package based data activation only works when it is triggered from the in process chains via above mentioned process variant. In case the activation is performed manually, either via t-code RSMNG or via BW/4 Cockpit the activation work in the old way – no package size used.

Technically the activation is performed by a method _OPEN_PROCESS_ACTIVATE of class CL_RSDSO_MNG_ACTION / CL_RSDSO_DATA_TASK) triggered by IF_RSPM_RUNTIME~ACTIVATE_PROCESS (CL_RSPM_RUNTIME) ) and/or FM RSDSO_COMPRESS_REQUESTS.

 

More information:

3108217 - ADSO: packaged activation of requests

3038366 - Activation of requests in an aDSO with a huge number of request fails

Flavors of aDSO object

Watermarks of a BW data target object

Watermarks in BW can be considered as set of an internal counters (so called watermarks) of a technical information of the BW data target. Following are the BW data target objects that may have the watermarks: InfoCube, DataStore Object, Master Data Table and Text Table.

There is an ABAP program (RSPM_ADSO_WATERMARKS) in SAP BW systems that displays the aDSO watermarks like:

·        Number of all requests in aDSO

·        Number of nonactive requests in aDSO

·        Number of deleted requests in aDSO

·        TSN of lowest active request (AQ) in aDSO

·        TSN of high
est active request (AQ) in aDSO

·        AQ Status

·        TSN of lowest active request (AT) in aDSO

·        TSN of highest active request (AT) in aDSO

·        AT Status

·        Count

·        Count AT

·        Data in aDSO must be activated

·        Proc. Type

·        Process ID

·        Datamarted source TSN

·        Requests not datamarted

·        Delta information

 

The program gathers information from many tables like RSOADSO, RSPMREQUEST, RSMDATASTATE_DMO (DMO - DataMart Out), RSMDATASTATE_DMI (DMI - DataMart In) and others. For non BW4 optimized objects like classic infocube there is an function module RSSM_SHOW_WATERMARKS that gathers the watermarks for those objects. 

Selection screen of the report RSPM_ADSO_WATERMARKS:

Example output screen of the same report:

How to read/write/delete from/to aDSO objects

This is a sequel of my older blog that deals with classic DSO objects. This one deals with an aDSO objects. There is a set of SAP standard function modules for reading, writing and deleting data from/to the aDSO objects as well. They can easily be used in custom ABAP code in BW’s transformations.

There are function modules representing APIs for DSO objects under name space RSDRI_* (or RSDRD_*). In case of aDSO objects the API are there under name space RSDSO_*.

 

aDSO type                  Operation       Method / API

Standard                    READ                Open SQL SELECT; RSDRI_INFOPROV_READ

                                    WRITE            RSDSO_WRITE_API; RSDSO_WRITE_API_RFC

ACTIVE       RSDSO_ACTIVATE_REQ_API_RFC - Activates requests in aDSO. Multiple requests can be activated separately or whether or system can activate as many requests as possible at one shot.

                                    DETELE             N/A

Direct Update             READ                Open SQL SELECT

WRITE    RSDSO_DU_WRITE_API; RSDSO_DU_WRITE_API_RFC – Writes data from an itab into active data table of the aDSO. Each API call results in a new request. Database transaction is committed automatically.

DELETE            RSDSO_DU_DELETE_API_RFC - Deletes data from an aDSO. Whole content can be deleted or data can be deleted based on a selective deletion.

CLEANUP          RSDSO_DU_CLEANUP_API_RFC - Changes status of red requests in aDSO to green. Red requests are blocking further data loads thus have to be corrected. Only requests that were loaded via API are considered.

 

FMs ending with *RFC are supposed to be used for remote scenarios whereas other FMs are locally to be used where the aDSO and calling code resides within the same system.

 

Supporting FMs:

RSDSO_DEBUG_API – It enables a user to debug above listed RFC enabled APIs for aDSO.

 

More information:

DSO object APIs

Online docu for aDSO objects API – BW4/HANA

Online docu for aDSO objects API – BW 7.5

Error: Numeric overflow for parameter/column

I recently ran into an issue of reading aDSO object from custom code. I used a method READ of ABAP class CL_RSDRI_INFOPROV. Return code of the method call was 8 = inherited_error. When I debugged it; I came to place in FM TREX_DBS_AGGREGATE where following call is performed:

cl_hdb_sql_for_aggr_req_facade=>get_instance_for_1st_chunk

Exception that was triggered I found following error:

AttributeEngine: overflow in numeric calculation;AttributeEngine: overflow in numeric calculation;exception 70006944: AttributeE

overflow in numeric calculation; $message$=aggregation failed $BIC$<KF_name>$sum$ 1 fixed14.3(17) exception 70006944: AttributeE

overflow in numeric calculation; $message$=aggregation failed $BIC$ KF_name $sum$ 1 fixed14.3(17) ,Exception in executor ...

plan408469425@ndhcdb01-int:30085 while executing pop 6: calcEngine search on olapIndex failed.,QueryId: ...

00O2SPBEZ1RC04NPREWOW2QMR:_C10/[Request Info: Object Name = "<db_schema>"."0BW:BIA: <cube/aDSO_name>", FM Name = TREX_DBS_AGGREGATE]

Error 6.944 has occurred in the BWA/SAP HANA server

Error reading the data of InfoProvider <cube/aDSO_name>$X

Seems this is a generic DB error no 10811:

Numeric overflow for parameter/column (<id>) source type <source_type>, target type <target_type>, value '<value>'

Apparently, value of KF mentioned in the error exceeded its threshold. Solution was to delete the data that caused this overflow.

 

More information:

2399990 - How-To: Analyzing ABAP Short Dumps in SAP HANA Environments

2393013 - FAQ: SAP HANA Clients

2352450 - ADBC: Numeric overflow for data type BIGINT

Cube-like aDSO object

In my other post, I introduced different flavors of an aDSO objects. In this post, I want to go deeper with one of them – cube-like aDSO object.

Aim of the cube-like aDSO object is to replicate behavior of infocube object that we know from classic (NetWeaver based) releases of SAP BW. It is used to model data warehouse layer – data mart according LSA++ architecture. The object does not have change log (CL) table. There are only two tables underneath:

1 Inbound data (AQ or AX for a cold storage) - stores newly loaded data identified by data load requests, similar to F-table in classic cube.

2 Active data (AT) – stores compressed data by activation requests, similar to E-table in classic cube.

Data is moved from inbound table to active one upon its activation (also known as aggregation). Once all data is activated, there is no data in the inbound table.

The cube-like aDSO object setup is following. "Active data" and "All Characteristics are Key" check boxes are checked.

BW4/HANA

BW75

Reporting on the cube-like ADSOs is performed as a union of inbound and active tables. This behavior is referred as stable navigation. There is no need to activate the data as both tables are used for the querying the data.

There are few remarks with regard to a deletion of the cube-like aDSO object. Request based deletion works if the data is only available in the inbound/new (AQ, e.g. /BIC/A*1 Inbound Table) table. Once the data is activated it is not possible to delete the request in the aDSO. There is only possibility to complete content deletion or a selective deletion. If a possibility to delete the requests is needed the data must not be activated.

Cube-like aDSO is sometimes referred as DataMart DSO (or DataMart aDSO).

More information:

Onlinedocu - infocube

Onlinedocu – datawarehouse layer – delta calculation

Flavors of aDSO object

3102582 - Request Based Deletion does not work as expected

3116509 - Deletion of overlapping request for ADSO is not working in case if the request is activated in target of ADSO type cube

Flavors of aDSO object

aDSO objects came in BW with many flavors. Mostly the different flavors corresponds to different layers of Enterprise Data Warehouse Architecture (like LSA++ for SAP BW/4HANA or LSA++ for classic BW) categories:

1 Data acquisition layer - including corporate memory

2.1 Corporate memory - compression capabilities

2.2 Corporate memory - reporting capabilities

3.1 Data warehouse layer - delta calculation

3.2 Data warehouse layer - data mart

Or to different InfoProvider objects as we know them from classic BW:

4 Standard DataStore object

5 Write-optimized DataStore object

6 InfoCube

Or from planning point of view:

          7 Planning in InfoCube-like manner

          8 Planning on direct update

 

BW modeling tools offers a template to make a decision of what type of the aDSO objects would a developer pick via called Templates for Modeling the Data Warehousing Layers. The template appears in SAP HANA Studio (BW Modeling perspective). Developer can pick from the template and the new aDSO object’s settings are setup by the system.

Here is a brief description on how the particular predefined property settings for the aDSO object look like for different aDSO object type:

 

1 Data acquisition layer - including corporate memory: No properties are selected, object corresponds to PSA layer as known in the classic BW.

 

2 Corporate memory

2.1 compression capabilities: "Active data" check box is enabled. Data is loaded into the inbound table. Data can be compressed (activated). However, the request cannot be deleted anymore after the activation. No request based deletion of the request possible after the activation. While querying such the aDSO the active table is accessed.

2.2 reporting capabilities: "Active data" and "Keep Inbound data extract from Inbound Table" check boxes is enabled. Data is stored redundantly in the inbound and in active table as well. This enables activation of the data and deleting the activated data requests. Data can be reloaded from inbound table as well (detailed information is not lost). While querying such the aDSO the active table is accessed.

 

3 Data warehouse layer

3. 1 Delta calculation: "Active data" and "Write Change Log" check boxes is enabled. Data is loaded to inbound table. Another checkbox called "Unique Data Records" can be enabled too in case only unique data is loaded to the aDSO. While querying such the aDSO the active table is accessed thus data need to be activated prior the querying. Change log table is used to enable deletion of activated requests thus the aDSO can be reloaded again. Behavior of this type of the aDSO is similar to standard DSO (classic) like object. Mechanics here is called unstable navigation as while querying only active table is used.

 

3.2 Data mart: "Activate Data" and "All Characteristics are Key, Reporting on Union of Inbound and Active Table" check boxes is enabled. Behavior of this type of the aDSO is similar to standard InfoCube object (cube-like object). Its inbound table is similar to F (not compressed data) table of the cube and active table is similar to E (compressed data) table of the standard cube. This type of the aDSO object supports so called stable navigation as while querying both the tables are used (via union): inbound and active too.

 

4 Standard DataStore object: so called classic DSO-like aDSO object - See point 3.1.

 

5 Write-optimized DataStore object: No properties are selected.

 

6 InfoCube:  so called cube-like aDSO object - See point 3.2.

 

7 Planning in InfoCube-like manner: "Active Data", "All Characteristics are Key, Reporting on Union of Inbound and Active Table" and "Planning Mode" check boxes is enabled.

 

8 Planning on direct update: "Direct Update" and "Planning Mode" check boxes is enabled.

 

Notice that the templates for the Modeling the Data Warehousing Layers are only available for classic BW systems and not in BW4/HANA.

 

More information:

Templates for Modeling the Data Warehousing Layers