How to Model Advanced DataStore Objects (ADSOs) in SAP BW/4HANA

SAP BW/4HANA is a modern data warehouse solution that simplifies and optimizes the way you manage and analyze your data. One of the key features of SAP BW/4HANA is the Advanced DataStore Object (ADSO), which is the primary persistent object used in data modeling.

In this blog post, you will learn what ADSOs are, what types of ADSOs are available, how to create and activate them, and how to use them in mixed modeling scenarios or expose them to third-party consumers.

What are ADSOs?

ADSOs are flexible data containers that store data in different tables and views, depending on the type and settings of the ADSO. ADSOs can be used for various purposes, such as staging, reporting, extraction, or external access.

ADSOs consist of three main components:

• Data Fields: These are the fields that define the structure and semantics of the ADSO. Data fields can be key fields or data fields, and they can have different attributes, such as data type, length, aggregation type, etc.
• Partitions: These are optional components that allow you to split the data of an ADSO into smaller units based on one or more partitioning criteria. Partitions can improve the performance and manageability of large ADSOs.
• Indexes: These are optional components that allow you to create secondary indexes on the tables of an ADSO to speed up data access and query execution.

Depending on the type and settings of an ADSO, different tables and views are generated when you activate an ADSO. The most common ones are:

• Inbound Table: This is the table that receives the data from the source system or transformation. It has the same structure as the data fields of the ADSO.
• Active Data Table: This is the table that stores the activated data of the ADSO. It has the same structure as the data fields of the ADSO, plus some additional fields for technical purposes, such as request ID, record mode, etc.
• Change Log Table: This is the table that stores the delta (new, deleted, and changed records) of the ADSO. It has a similar structure as the active data table, plus some additional fields for delta management, such as change mode, image flag, etc.
• View for Extraction: This is the view that provides the data for delta extraction from the ADSO. It is based on the change log table and has a similar structure as the active data table.
• View for Reporting: This is the view that provides the data for reporting and analysis from the ADSO. It is based on the active data table and has a similar structure as the data fields of the ADSO.
• View for External Access: This is a new feature introduced in SAP BW/4HANA 2.0. It is a view that provides a standardized and simplified access to the data of an ADSO for external consumers, such as SAP HANA SQL clients or third-party tools. It is based on the view for reporting and has some additional features, such as cold store flag, internal and external formats for amount key figures, reference points for non-cumulative key figures, etc.

What types of ADSOs are available?

There are four main types of ADSOs in SAP BW/4HANA:

• Standard DataStore Object: This is the most common type of ADSO. It can be used for reporting and analysis, as well as for delta extraction. It has an inbound table, an active data table, and a change log table. You can choose whether to write to or without change log depending on your requirements. You can also enable snapshot support or unique data records options for specific scenarios.
• Staging DataStore Object: This is a type of ADSO that is mainly used for staging or temporary storage of data. It has an inbound table and an active data table, but no change log table. You can choose whether to compress data or enable reporting on this type of ADSO.
• Direct Update DataStore Object: This is a type of ADSO that allows direct update of data from external applications or programs using a write interface. It has only an active data table and no inbound or change log tables. You cannot use transformations or DTPs to load data into this type of ADSO.
• Planning DataStore Object: This is a type of ADSO that allows planning and write-back of data using SAP Business Planning and Consolidation (BPC) or SAP Analytics Cloud (SAC). It has only an active data table and no inbound or change log tables. You can use transformations or DTPs to load data into this type of ADSO.

The following table summarizes the main characteristics and differences of the four types of ADSOs:

Type	Inbound Table	Active Data Table	Change Log Table	Extraction	Reporting	Planning	Direct Update
Standard	Yes	Yes	Optional	Yes	Yes	No	No
Staging	Yes	Yes	No	No	Optional	No	No
Direct Update	No	Yes	No	No	Yes	No	Yes
Planning	No	Yes	No	No	Yes	Yes	No

How to create and activate ADSOs?

You can create and activate ADSOs using the SAP BW/4HANA Modeling Tools in Eclipse. The steps are as follows:

1. In the Project Explorer, right-click on the InfoArea where you want to create the ADSO and choose New > DataStore Object (advanced).
2. In the wizard, enter a technical name, a description, and a type for the ADSO. You can also choose a template from the predefined ones or create your own template. Click Next.
3. In the next screen, you can define the data fields of the ADSO. You can add, delete, or modify the fields as per your requirements. You can also specify the partitioning criteria and the secondary indexes for the ADSO. Click Finish.
4. The ADSO is created in the inactive version and is displayed in the editor. You can make further changes to the ADSO properties, such as modeling settings, write interface settings, etc.
5. To activate the ADSO, right-click on the ADSO and choose Activate. The system will generate the tables and views for the ADSO and activate it.

How to use ADSOs in mixed modeling scenarios or expose them to third-party consumers?

As mentioned earlier, one of the new features of SAP BW/4HANA 2.0 is the view for external access for ADSOs. This view is generated by default for all new ADSOs created in SAP BW/4HANA 2.0. For pre-existing ADSOs, you need to reactivate them using the program RSDG_ADSO_ACTIVATE or manually.

The view for external access has some advantages over using the tables or views directly for mixed modeling scenarios or exposing data to third-party consumers. Some of these advantages are:

• It provides a standardized and simplified access to the data of an ADSO without exposing the internal details or complexity of the tables or views.
• It supports different data temperature scenarios by using a new controller technical flag called COLD_STORE_FLAG. This flag indicates whether a record is stored in hot, warm, or cold storage tiers based on your data tiering strategy.
• It supports different formats for amount key figures by using internal and external formats. The internal format is used for calculations and aggregations, while the external format is used for display and conversion purposes. The external format is derived from the currency/unit fields of the ADSO.
• It supports non-cumulative key figures by exposing reference points with an additional column with naming convention as _NCUM_REFP. The reference points indicate the validity period of a non-cumulative value.

To use the view for external access, you need to know its name and structure. The name of the view is /BIC/A8. The structure of the view is similar to the data fields of the ADSO, plus some additional fields as mentioned above.

You can access the view for external access using SAP HANA SQL clients or third-party tools that support SAP HANA SQL dialect. For example, you can use SAP HANA Studio or SAP HANA Database Explorer to query the view using SQL statements.

Here is an example of a SQL statement that selects some fields from an ADSO with technical name SALESADSO:

This statement will return the following result:

CALYEAR	COUNTRY	PRODUCT	SALES_QTY	SALES_AMT (Internal Format)	SALES_AMT_EXT (External Format)	CURRENCY	COLD_STORE_FLAG
2020	US	P1	100	100000000	1000	USD	H
2020	US	P2	200	200000000	2000	USD	H
2020	IN	P1	50	50000000	500	INR	W
2020	IN	P2	100	100000000	1000	INR	W

As you can see, the view for external access shows the sales amount in both internal and external formats, and also indicates the data temperature using the cold store flag.

Conclusion

In this blog post, you have learned how to model ADSOs in SAP BW/4HANA, what types of ADSOs are available, how to create and activate them, and how to use them in mixed modeling scenarios or expose them to third-party consumers. ADSOs are powerful and flexible data containers that can help you simplify and optimize your data warehouse solution.

Disclaimer: This content is generated by AI.