Accessing Data Directly from Remote Sources

Across the lessons of this unit, a data flow has been described. The purpose of the data flow is to load transaction data from SAP S/4HANA into SAP BW/4HANA. The loaded data is permanently stored in DataStore Objects (advanced) (for transaction data) and characteristic InfoObjects (for master data). This data is available to BW Queries.

As well as querying data that has been loaded to SAP BW/4HANA, it is also possible to build BW Queries on data that is stored remotely in the original source systems. In this case we do not need to load the data. We can build BW Queries directly on the remote data. This is sometimes referred to as a virtual data scenario.

These two virtual data scenarios are explained in this lesson:

• Combine the loaded and stored actual transactional sales data with plan sales data that is stored in an SAP HANA database table. The data in the remote table is provided to SAP BW/4HANA using an Open ODS View.
• Combine the loaded and stored actual transactional sales data with CO2 footprint data provided by an SAP HANA calculation view.

The actual product sales quantities have been loaded from SAP S/4HANA to SAP BW/4HANA and is stored in a Standard DataStore Object. The plan sales quantity is stored in an SAP HANA database table. The plan data in the SAP HANA table looks like this:

Instead of setting up a data flow to physically load the plan data to SAP BW/4HANA, a different approach is used:

Firstly, an Open ODS View is defined. This object is created in SAP BW/4HANA and reads the plan quantity in a remote SAP HANA table.

In the next step, a CompositeProvider is defined to combine the actual sales data, which is stored in the Standard DataStore Object, and the plan data which is read at run-time from the remote database table using an Open ODS View. We will define a union of the providers so that for each product we will have the actual and plan quantity.

We then create a BW Query on top of the CompositeProvider and finally display the results in Analysis for Microsoft Office. The report should look like this:

So let's look at the data flow one more time to notice the objects for this scenario.

The first step is to create an Open ODS View in SAP BW/4HANA on top of the SAP HANA table.

An Open ODS View is an SAP BW/4HANA object that exposes remote source data to SAP BW/4HANA without the need to create InfoObjects and without loading and storing data.

Open ODS Views enable you to define data models based on database tables and database views that are located in remote databases. These data models allow flexible integration without the need to create InfoObjects and without the need to load the data into SAP BW/4HANA. This flexible type of data integration makes it possible to consume external data in SAP BW/4HANA without staging, and afterward, using a CompositeProvider, to combine these external data with other SAP BW/4HANA models.

In our scenario the Open ODS View is created based on the plan data table in SAP HANA .

A CompositeProvider represents the virtual layer of SAP BW/4HANA.

Our CompositeProvider has two providers: the Standard DataStore Object that contains the actual data and the Open ODS View that exposes the plan data.

In our scenario we create a CompositeProvider to generate a union of the actual and plan sales data.

A BW Query is defined on top of the CompositeProvider.

In our scenario the BW Query selects Sold Quantity from the Standard DataStore Object and Planned Quantity from the Open ODS View. Two additional formulas are also defined in the BW Query: Diff. Sold - Planned and % Variance Sold/Planned.

In another scenario we combine data relating to the CO2 footprint of each product with the actual sales data to generate the total CO2 footprint for each sale. The CO2 footprint data is provided by an SAP HANA calculation view and the actual sales data again comes from the Standard DataStore Object.

The CO2 footprint data provided by the SAP HANA calculation view looks like this:

To set up this scenario, another CompositeProvider is created to combine the Standard DataStore Object which stores the actual sales data and the SAP HANA calculation view. The results of a BW Query, defined on top of this CompositeProvider, are then displayed in Analysis for Microsoft Office. The final result should look like this:

So let's look at the data flow to notice the objects for this scenario.

We define a CompositeProvider to combine the Standard DataStore Object with the SAP HANA calculation view.

In our CompositeProvider we define a Left Outer Join between the Standard DataStore Object and the SAP HANA calculation view.

A BW Query is defined on the CompositeProvider.

In the BW Query Gross Amount and Quantity are selected from the Standard DataStore Object and the key figure CO2 Footprint (Each) is selected from the SAP HANA calculation view. Also a formula is defined, CO2 Footprint (Total), which calculates the total CO2 Footprint when the BW Query is executed.