How to Read Data Stored in SAP Datasphere from SAP BW Bridge

How to Read Data Stored in SAP Datasphere from SAP BW Bridge
Estimated read time 23 min read

SAP BW bridge is primarily intended for ODP-based source systems while non-SAP sources are connected directly to SAP Datasphere. However, data flows in SAP BW or SAP BW/4HANA often uses a mix of SAP and non-SAP sources, for example, flat files, or source systems that are not supported in SAP BW bridge, for example, SAP HANA source systems.

That can be a challenge in conversion projects to SAP BW bridge. You can leverage Shell Conversion to migrate the data flows with supported SAP sources to SAP BW bridge and recreate the data flows based on unsupported sources in SAP Datasphere. But what do you do in SAP BW bridge if a transformation from a supported SAP source into an ADSO does a lookup on data that is loaded via flat file? So far, this was not easily possible in SAP BW bridge.

With CDS external entities, it is now possible in SAP BW bridge to access data stored in SAP Datasphere. A CDS external entity is a CDS artifact that represents an external database object. In the context of this blog, the external entity can be any object in SAP Datasphere that can be exposed for consumption.

CDS external entities provide a modern way to handle secondary database connections in ABAP Cloud. This allows retrieving data from other databases using SQL, for example, from the SAP HANA Cloud database of SAP Datasphere. The remote connection is managed using the data federation approach of the SAP HANA Smart Data Access technology (SDA).

Depending on the use case, you may want to load data from SAP Datasphere into SAP BW bridge periodically. This can be done by creating a custom process type and scheduling a process chain with this variant.

The Solution at a Glance

In SAP Datasphere:

Create a View and expose it for consumption.Create a Database User in your space.

In SAP BW bridge:

Create a Logical External Schema which points to your space in SAP Datasphere.Create an Outbound Service for your logical external schema.Create a customer-managed Communication Scenario which uses the outbound service.Create a Communication System that points to the SAP HANA Cloud database of SAP Datasphere.Create a Communication Arrangement that uses the communication scenario and the communication system.Create a CDS View with template External Entity which points to the view exposed by SAP Datasphere.Implement a Custom Process Type to schedule a data load from SAP Datasphere into SAP BW bridge. (optional)

The Solution Step by Step

In this demo scenario, we create a local table in SAP Datasphere. A view on top of this local table is the external entity that is read from SAP BW bridge. 

Prerequisites:

Install the most recent version of BW Modeling Tools.In SAP Datasphere, a space is needed, and a scoped role must be set up accordingly.In SAP BW bridge, a software component and a related ABAP development package must be created.

 

1. In SAP Datasphere | Create a View and Expose it for Consumption

In the Data Builder, create and deploy a new Local Table (Master_Data_Table). In this demo scenario, the table is created in a space named SPACE_IN_DATASPHERE.

Upload data into the local table, e.g., by manual data upload.

Create a new SQL View on that local table (View_on_Master_Data_Table), expose it for consumption, and deploy it.

 

2. In SAP Datasphere | Create a Database User

Go to Space Management, and create a Database User with Database User Name Suffix SPACE_DB_USER with Read Access (SQL). The full name of the user is then SPACE_IN_DATASPHERE#SPACE_DB_USER. Deploy the space. Then, click on the Info icon and note down Host Name, Port, and Password. This information is later needed when creating a communication system in BW bridge.

 

3. In SAP BW Bridge | Create a Logical External Schema

In BW Modeling Tools, in the ABAP Cloud Project, create a new Logical External Schema. In this demo scenario, the logical external schema is named Z_SPACE_IN_DATASPHERE.

Go to menu File > New > Other…, select ABAP > Logical External Schema. Select your ABAP development package and ensure that the name is in the customer namespace.In the next screen, as Default Remote Schema, set the name of the space in SAP Datasphere: SPACE_IN_DATASPHERESave and activate the new logical external schema.

 

4. In SAP BW Bridge | Create an Outbound Service

In BW Modeling Tools, in the ABAP Cloud Project, create a new Outbound Service which is later used in a communication scenario. In this demo scenario, the outbound service is named Z_OUTBOUND_SRV_TO_DATASPHERE_SCHEMA_DESD.

Go to menu File > New > Other…, select ABAP > Cloud Communication Management > Outbound Service. Select your ABAP development package. Ensure that the name is in the customer namespace and ends with suffix DESD.As Service Type, select Outbound SQL Access.In the next screen, set the logical external schema you have created in step 3.Save the new outbound service.

 

5. In SAP BW Bridge | Create a Communication Scenario

In BW Modeling Tools, in the ABAP Cloud Project, create a new Communication Scenario which is later used in a communication arrangement. In this demo scenario, the communication scenario is named Z_COM_SCEN_DATASPHERE_SCHEMA.

Go to menu File > New > Other…, select ABAP > Cloud Communication Management > Communication Scenario. Select your ABAP development package and ensure that the name is in the customer namespace.In the Overview tab, open the dropdown menu for Allowed Instances and select Once Instance per client.

Switch to the Outbound tab. In the section Outbound Settings, in Supported Authentication Methods, ensure that Basic is set as the only option. In the section Outbound Services, click Add. In the popup dialog, select the outbound service you have created in step 4.Save the new communication scenario.

Click the Unlock button in the upper menu bar. Then, click the Publish Locally button in the upper right corner. This unlocks the communication scenario and makes it available for usage in a communication arrangement.In the Overview tab, check that the status of the communication scenario has changed to Published. If publishing fails, close the communication scenario, open it, and try to publish it again.

 

6. In SAP BW Bridge | Create a Communication System

In the SAP BW Bridge Cockpit, create a new Communication System which points to the SAP Datasphere tenant. In this demo scenario, the communication system is named MY_DATASPHERE.

In Communication Management, open the app Communication Systems, and click New.In section Technical Data, set Host Name and Port as previously copied in SAP Datasphere when creating the database user.Set Remote SQL Access to ON.As Adapter Name, choose ‘HANA (ODBC)’.As Adapter Configuration, use the template below and replace <Host Name> and <Port> by the host name and port previously copied in SAP Datasphere when creating the database user:
Driver=libodbcHDB.so;ServerNode=<Host Name>:<Port>;encrypt=FALSE;In section Users for Outbound Communication, add a new User for Outbound Communication:As Authentication Method, select ‘User Name and Password’.As User Name/Client ID, set the database user that was created in the space in step 2 (SPACE_IN_DATASPHERE#SPACE_DB_USER) and set the password previously copied in SAP Datasphere when creating the database user.Save the new communication system.

 

7. In SAP BW Bridge | Create a Communication Arrangement

In the SAP BW Bridge Cockpit, create a new Communication Arrangement which uses the communication scenario and the communication system created in the steps before. In this demo scenario, the communication arrangement is named ‘Access to Datasphere Schema’.

In Communication Management, open the app Communication Arrangements and click New.In the New Communication Arrangement dialog, select the communication scenario that you created in step 5 and set a meaningful name for the communication arrangement.In Common Data, select the communication system MY_DATASPHERE that you have created in step 6.In Outbound Services, for the Remote Database Schema Name, click Use Default to set the schema name you have maintained in the logical external schema in step 3.Save the communication arrangement.

 

8. In SAP BW Bridge | Create a CDS External Entity

Back in BW Modeling Tools, in the ABAP Cloud Project, create a new CDS View which points to the exposed view in SAP Datasphere. It is later used in your ABAP code, e.g., in a transformation. In this demo scenario, the CDS view is named Z_View_on_Master_Data_Table.

Go to menu File > New > Other…, select ABAP > Core Data Services > Data Definition. Select your ABAP development package and ensure that the name is in the customer namespace. In the templates dialog, choose the template External Entity (creation) > defineExternalEntity.As external entity name, set the name of the exposed view in SAP Datasphere and maintain the (key) fields, their data types, and the external (key) element names used in SAP Datasphere.Save and activate the CDS view.

 

@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: ‘View on Master Data Table’
define external entity Z_View_on_Master_Data_Table external name View_on_Master_Data_Table
{
key Key_Column : abap.char(10) external name Key_Column;
Attribute_A : abap.char(5) external name Attribute_A;
Attribute_B : abap.char(5) external name Attribute_B;
Attribute_C : abap.char(5) external name Attribute_C;

} with federated data
provided at runtime

 

You can now use the new CDS view in ABAP to read data stored in SAP Datasphere.

At this point, you may wonder how the new CDS view is linked to the logical external schema. This information is provided at runtime, in the SQL statement. Let’s look at an example and write a simple test class.

Go to menu File > New > Other…, select ABAP > Source Code Library > ABAP Class. Select your ABAP development package and ensure that the class name is in the customer namespace. In the Interfaces section, add the interface IF_OO_ADT_CLASSRUN.Implement method if_oo_adt_classrun~main as shown below.

 

CLASS z_read_from_datasphere DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .

PUBLIC SECTION.
INTERFACES if_oo_adt_classrun .
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.

CLASS z_read_from_datasphere IMPLEMENTATION.

METHOD if_oo_adt_classrun~main.

* read from Datasphere
SELECT * FROM Z_View_on_Master_Data_Table PROVIDED BY z_space_in_datasphere INTO TABLE (lt_data).

* output table to ABAP Console
out->write( data = lt_data name = ‘Data read from Datasphere’ ).

ENDMETHOD.
ENDCLASS.

 

Press F9 to run the method.

 

9. In SAP BW Bridge | Implement a Custom Process Type (Optional)

To periodically load the data stored in SAP Datasphere into an ADSO or an InfoObject in SAP BW bridge, you must implement a custom process type. Custom process types allow you to create your own process types, similar to the process type ABAP you may know from SAP BW or SAP BW/4HANA, but even more powerful thanks to status and event handling and the option to create user interfaces. In the context of this blog, a simple process type without user interface is sufficient.

Before creating the custom process type in the SAP BW Bridge Cockpit, you must create the ABAP implementation of the process type. This is an ABAP class that implements the interface IF_RSPC_SIMPLE_PROCESS_TYPE and inherits from the superclass CL_RSPC_SIMPLE_PROCESS_TYPE. In the implementing class, you read the data from the CDS external entity and write it to a DataStore or to an InfoObject using the BW Data API.

 

CLASS zcl_load_from_datasphere DEFINITION
PUBLIC
INHERITING FROM cl_rspc_simple_process_type
FINAL
CREATE PUBLIC .

PUBLIC SECTION.
INTERFACES if_rspc_simple_process_type .
CLASS-METHODS class_constructor.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.

CLASS zcl_load_from_datasphere IMPLEMENTATION.

METHOD class_constructor.
c_type = ‘ZLOAD_MD’.
c_class = ‘ZCL_LOAD_FROM_DATASPHERE’.
ENDMETHOD.

METHOD if_rspc_simple_process_type~execute.
TRY.
SELECT * FROM Z_View_on_Master_Data_Table PROVIDED BY z_space_in_datasphere INTO TABLE @DATA(lt_data).

DATA(l_r_writer) = cl_rs_bw_data_api_factory=>get_datastore_data_handler( ‘MY_ADSO’ ).

l_r_writer->write_data(
EXPORTING
i_allow_new_sids = abap_true
i_activate_data = abap_true
it_data = lt_data
i_process_type = ‘DATA_LOAD’
IMPORTING
e_lines_inserted = DATA(l_lines_inserted)
et_msg = DATA(lt_msg)
e_upd_req_tsn = DATA(l_upd_req_tsn)
et_act_req_tsn = DATA(l_act_req_tsn)
).
CATCH cx_root INTO DATA(l_r_e).
e_failed = abap_true.
ENDTRY.
ENDMETHOD.
ENDCLASS.

 

Once the class is activated, you can then create a new Custom Process Type in the SAP BW Bridge Cockpit.

In Datasphere Configuration, open the app Custom Process Types and click Create.In the Create Process Type dialog, enter a name and ensure that it is in the customer namespace.Select a category, maintain short text and description, and set the name of the implementing class.Save the custom process type.

 You can then create and schedule a process chain with the new custom process type.

Conclusion

In this blog we have shown how CDS external entities can be used to cover common business requirements in SAP BW bridge.

Many thanks to my colleague @JANNINGD for his valuable contribution to this blog post.

For any questions or feedback just leave a comment.

 

​ SAP BW bridge is primarily intended for ODP-based source systems while non-SAP sources are connected directly to SAP Datasphere. However, data flows in SAP BW or SAP BW/4HANA often uses a mix of SAP and non-SAP sources, for example, flat files, or source systems that are not supported in SAP BW bridge, for example, SAP HANA source systems.That can be a challenge in conversion projects to SAP BW bridge. You can leverage Shell Conversion to migrate the data flows with supported SAP sources to SAP BW bridge and recreate the data flows based on unsupported sources in SAP Datasphere. But what do you do in SAP BW bridge if a transformation from a supported SAP source into an ADSO does a lookup on data that is loaded via flat file? So far, this was not easily possible in SAP BW bridge.With CDS external entities, it is now possible in SAP BW bridge to access data stored in SAP Datasphere. A CDS external entity is a CDS artifact that represents an external database object. In the context of this blog, the external entity can be any object in SAP Datasphere that can be exposed for consumption.CDS external entities provide a modern way to handle secondary database connections in ABAP Cloud. This allows retrieving data from other databases using SQL, for example, from the SAP HANA Cloud database of SAP Datasphere. The remote connection is managed using the data federation approach of the SAP HANA Smart Data Access technology (SDA).Depending on the use case, you may want to load data from SAP Datasphere into SAP BW bridge periodically. This can be done by creating a custom process type and scheduling a process chain with this variant.The Solution at a GlanceIn SAP Datasphere:Create a View and expose it for consumption.Create a Database User in your space.In SAP BW bridge:Create a Logical External Schema which points to your space in SAP Datasphere.Create an Outbound Service for your logical external schema.Create a customer-managed Communication Scenario which uses the outbound service.Create a Communication System that points to the SAP HANA Cloud database of SAP Datasphere.Create a Communication Arrangement that uses the communication scenario and the communication system.Create a CDS View with template External Entity which points to the view exposed by SAP Datasphere.Implement a Custom Process Type to schedule a data load from SAP Datasphere into SAP BW bridge. (optional)The Solution Step by StepIn this demo scenario, we create a local table in SAP Datasphere. A view on top of this local table is the external entity that is read from SAP BW bridge. Prerequisites:Install the most recent version of BW Modeling Tools.In SAP Datasphere, a space is needed, and a scoped role must be set up accordingly.In SAP BW bridge, a software component and a related ABAP development package must be created. 1. In SAP Datasphere | Create a View and Expose it for ConsumptionIn the Data Builder, create and deploy a new Local Table (Master_Data_Table). In this demo scenario, the table is created in a space named SPACE_IN_DATASPHERE.Upload data into the local table, e.g., by manual data upload.Create a new SQL View on that local table (View_on_Master_Data_Table), expose it for consumption, and deploy it. 2. In SAP Datasphere | Create a Database UserGo to Space Management, and create a Database User with Database User Name Suffix SPACE_DB_USER with Read Access (SQL). The full name of the user is then SPACE_IN_DATASPHERE#SPACE_DB_USER. Deploy the space. Then, click on the Info icon and note down Host Name, Port, and Password. This information is later needed when creating a communication system in BW bridge. 3. In SAP BW Bridge | Create a Logical External SchemaIn BW Modeling Tools, in the ABAP Cloud Project, create a new Logical External Schema. In this demo scenario, the logical external schema is named Z_SPACE_IN_DATASPHERE.Go to menu File > New > Other…, select ABAP > Logical External Schema. Select your ABAP development package and ensure that the name is in the customer namespace.In the next screen, as Default Remote Schema, set the name of the space in SAP Datasphere: SPACE_IN_DATASPHERESave and activate the new logical external schema. 4. In SAP BW Bridge | Create an Outbound ServiceIn BW Modeling Tools, in the ABAP Cloud Project, create a new Outbound Service which is later used in a communication scenario. In this demo scenario, the outbound service is named Z_OUTBOUND_SRV_TO_DATASPHERE_SCHEMA_DESD.Go to menu File > New > Other…, select ABAP > Cloud Communication Management > Outbound Service. Select your ABAP development package. Ensure that the name is in the customer namespace and ends with suffix DESD.As Service Type, select Outbound SQL Access.In the next screen, set the logical external schema you have created in step 3.Save the new outbound service. 5. In SAP BW Bridge | Create a Communication ScenarioIn BW Modeling Tools, in the ABAP Cloud Project, create a new Communication Scenario which is later used in a communication arrangement. In this demo scenario, the communication scenario is named Z_COM_SCEN_DATASPHERE_SCHEMA.Go to menu File > New > Other…, select ABAP > Cloud Communication Management > Communication Scenario. Select your ABAP development package and ensure that the name is in the customer namespace.In the Overview tab, open the dropdown menu for Allowed Instances and select Once Instance per client.Switch to the Outbound tab. In the section Outbound Settings, in Supported Authentication Methods, ensure that Basic is set as the only option. In the section Outbound Services, click Add. In the popup dialog, select the outbound service you have created in step 4.Save the new communication scenario.Click the Unlock button in the upper menu bar. Then, click the Publish Locally button in the upper right corner. This unlocks the communication scenario and makes it available for usage in a communication arrangement.In the Overview tab, check that the status of the communication scenario has changed to Published. If publishing fails, close the communication scenario, open it, and try to publish it again. 6. In SAP BW Bridge | Create a Communication SystemIn the SAP BW Bridge Cockpit, create a new Communication System which points to the SAP Datasphere tenant. In this demo scenario, the communication system is named MY_DATASPHERE.In Communication Management, open the app Communication Systems, and click New.In section Technical Data, set Host Name and Port as previously copied in SAP Datasphere when creating the database user.Set Remote SQL Access to ON.As Adapter Name, choose ‘HANA (ODBC)’.As Adapter Configuration, use the template below and replace <Host Name> and <Port> by the host name and port previously copied in SAP Datasphere when creating the database user:Driver=libodbcHDB.so;ServerNode=<Host Name>:<Port>;encrypt=FALSE;In section Users for Outbound Communication, add a new User for Outbound Communication:As Authentication Method, select ‘User Name and Password’.As User Name/Client ID, set the database user that was created in the space in step 2 (SPACE_IN_DATASPHERE#SPACE_DB_USER) and set the password previously copied in SAP Datasphere when creating the database user.Save the new communication system. 7. In SAP BW Bridge | Create a Communication ArrangementIn the SAP BW Bridge Cockpit, create a new Communication Arrangement which uses the communication scenario and the communication system created in the steps before. In this demo scenario, the communication arrangement is named ‘Access to Datasphere Schema’.In Communication Management, open the app Communication Arrangements and click New.In the New Communication Arrangement dialog, select the communication scenario that you created in step 5 and set a meaningful name for the communication arrangement.In Common Data, select the communication system MY_DATASPHERE that you have created in step 6.In Outbound Services, for the Remote Database Schema Name, click Use Default to set the schema name you have maintained in the logical external schema in step 3.Save the communication arrangement. 8. In SAP BW Bridge | Create a CDS External EntityBack in BW Modeling Tools, in the ABAP Cloud Project, create a new CDS View which points to the exposed view in SAP Datasphere. It is later used in your ABAP code, e.g., in a transformation. In this demo scenario, the CDS view is named Z_View_on_Master_Data_Table.Go to menu File > New > Other…, select ABAP > Core Data Services > Data Definition. Select your ABAP development package and ensure that the name is in the customer namespace. In the templates dialog, choose the template External Entity (creation) > defineExternalEntity.As external entity name, set the name of the exposed view in SAP Datasphere and maintain the (key) fields, their data types, and the external (key) element names used in SAP Datasphere.Save and activate the CDS view. @AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: ‘View on Master Data Table’
define external entity Z_View_on_Master_Data_Table external name View_on_Master_Data_Table
{
key Key_Column : abap.char(10) external name Key_Column;
Attribute_A : abap.char(5) external name Attribute_A;
Attribute_B : abap.char(5) external name Attribute_B;
Attribute_C : abap.char(5) external name Attribute_C;

} with federated data
provided at runtime You can now use the new CDS view in ABAP to read data stored in SAP Datasphere.At this point, you may wonder how the new CDS view is linked to the logical external schema. This information is provided at runtime, in the SQL statement. Let’s look at an example and write a simple test class.Go to menu File > New > Other…, select ABAP > Source Code Library > ABAP Class. Select your ABAP development package and ensure that the class name is in the customer namespace. In the Interfaces section, add the interface IF_OO_ADT_CLASSRUN.Implement method if_oo_adt_classrun~main as shown below. CLASS z_read_from_datasphere DEFINITION
PUBLIC
FINAL
CREATE PUBLIC .

PUBLIC SECTION.
INTERFACES if_oo_adt_classrun .
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.

CLASS z_read_from_datasphere IMPLEMENTATION.

METHOD if_oo_adt_classrun~main.

* read from Datasphere
SELECT * FROM Z_View_on_Master_Data_Table PROVIDED BY z_space_in_datasphere INTO TABLE (lt_data).

* output table to ABAP Console
out->write( data = lt_data name = ‘Data read from Datasphere’ ).

ENDMETHOD.
ENDCLASS. Press F9 to run the method. 9. In SAP BW Bridge | Implement a Custom Process Type (Optional)To periodically load the data stored in SAP Datasphere into an ADSO or an InfoObject in SAP BW bridge, you must implement a custom process type. Custom process types allow you to create your own process types, similar to the process type ABAP you may know from SAP BW or SAP BW/4HANA, but even more powerful thanks to status and event handling and the option to create user interfaces. In the context of this blog, a simple process type without user interface is sufficient.Before creating the custom process type in the SAP BW Bridge Cockpit, you must create the ABAP implementation of the process type. This is an ABAP class that implements the interface IF_RSPC_SIMPLE_PROCESS_TYPE and inherits from the superclass CL_RSPC_SIMPLE_PROCESS_TYPE. In the implementing class, you read the data from the CDS external entity and write it to a DataStore or to an InfoObject using the BW Data API. CLASS zcl_load_from_datasphere DEFINITION
PUBLIC
INHERITING FROM cl_rspc_simple_process_type
FINAL
CREATE PUBLIC .

PUBLIC SECTION.
INTERFACES if_rspc_simple_process_type .
CLASS-METHODS class_constructor.
PROTECTED SECTION.
PRIVATE SECTION.
ENDCLASS.

CLASS zcl_load_from_datasphere IMPLEMENTATION.

METHOD class_constructor.
c_type = ‘ZLOAD_MD’.
c_class = ‘ZCL_LOAD_FROM_DATASPHERE’.
ENDMETHOD.

METHOD if_rspc_simple_process_type~execute.
TRY.
SELECT * FROM Z_View_on_Master_Data_Table PROVIDED BY z_space_in_datasphere INTO TABLE @DATA(lt_data).

DATA(l_r_writer) = cl_rs_bw_data_api_factory=>get_datastore_data_handler( ‘MY_ADSO’ ).

l_r_writer->write_data(
EXPORTING
i_allow_new_sids = abap_true
i_activate_data = abap_true
it_data = lt_data
i_process_type = ‘DATA_LOAD’
IMPORTING
e_lines_inserted = DATA(l_lines_inserted)
et_msg = DATA(lt_msg)
e_upd_req_tsn = DATA(l_upd_req_tsn)
et_act_req_tsn = DATA(l_act_req_tsn)
).
CATCH cx_root INTO DATA(l_r_e).
e_failed = abap_true.
ENDTRY.
ENDMETHOD.
ENDCLASS. Once the class is activated, you can then create a new Custom Process Type in the SAP BW Bridge Cockpit.In Datasphere Configuration, open the app Custom Process Types and click Create.In the Create Process Type dialog, enter a name and ensure that it is in the customer namespace.Select a category, maintain short text and description, and set the name of the implementing class.Save the custom process type. You can then create and schedule a process chain with the new custom process type.ConclusionIn this blog we have shown how CDS external entities can be used to cover common business requirements in SAP BW bridge.Many thanks to my colleague @JANNINGD for his valuable contribution to this blog post.For any questions or feedback just leave a comment.   Read More Technology Blogs by SAP articles 

#SAP

#SAPTechnologyblog

Avatar for

You May Also Like

More From Author