Define Remote Tables

Objective

After completing this lesson, you will be able to define remote tables to access the Business Partner data sources.

Contents

  • Verify SQL Server Source Connection
  • Federated (Virtual) Access to Product Data Source
  • Federated (Virtual) Access to Business Partner Data Source
  • Persistent Access to Business Partner Data

In this section, we import remote tables from two connected source systems to make the data available in your space. First, we import Microsoft SQL database tables as a source in the view modeling tool. Afterwards, SAP BW/4HANA aDSOs (Advanced DataStore Objects) are imported directly from the Data Builder start page. In general, both import procedures are valid for Microsoft SQL, SAP BW as well as other source object types.

The access method is a pure federated (virtual) one! The tables including their data are still in the Microsoft SQL Database and in the SAP BW system and not in SAP Datasphere.

Many connections (including most connections to SAP systems) support importing remote tables to federate or replicate. Some connections support real-time replication and for others, you can keep your data fresh by scheduling regular updates.



Verify SQL Server Source Connection

Before importing the remote tables, we test if both source system connections are ready. The first source, a Microsoft SQL database is bound to our SAP Datasphere by using the connection MSSQL. The SAP BW source system is bound by using the connection BW4_DSP.

  1. Select Connections (step ‘A’) in the side navigation area and your space if necessary.

  2. Select the connection MSSQL (step ‘B’) and choose Validate (step ‘C’).

  3. A message appears informing you about the availability of supported features and provides details in case of errors in the connection.

  4. Select the connection BW4_DSP and repeat the steps 2-4.


Federated (Virtual) Access to Product Data Source

In this second step, we use the Microsoft SQL database as the data foundation. We will import two tables from the product area. For this task, you use the Data Builder to leverage the Graphical View Editor for the remote table imports.

  1. Select Data Builder in the side navigation area, choose your space if necessary.

  2. Select New Graphical View tile to open the view editor.

  3. Choose the Sources tab, expand Connections and press the Import … option of the MSSQL connection.

  1. In the Import Objects from Connection window, expand dbo schema -> Tables.

  2. Choose Products and ProductTexts.

  1. Select the Next button.

  2. Mark both table entries and press the Import and Deploy button.

  3. Both objects are imported as remote tables into SAP Datasphere now.

    • On the canvas the Products and ProductTexts entities are placed for modeling. Typically, we would add further objects from other sources and perform transformations to create a view. Since we only wanted to import the objects as remote tables, we don’t continue with the modelig part.

    • Alternatively to this integrated import step, part of the modeling, there is also a direct import option available for SQL objects. This direct import procedure is described for the Business Partner objects later.

  1. Close your graphical view builder and confirm to Discard this view in the pop-up window.

  2. The MSSQL tables Products and ProductTexts are listed as remote tables in the object list overview.

  3. Select Products to open the table editor.

  1. In the table editor, the semantic usage of the table and the columns can be adapted depending on the table type (local or remote). You can create associations, hierarchies, filters or switch from federated access to persistency, loading the data into SAP Datasphere.

  2. Close the table editor again.

What was done ?

In this step, we replicated the metadata of two Microsoft SQL tables into SAP Datasphere, as remote tables.

The access method is a pure federated (virtual) one. The tables including their data are still in the Microsoft SQL database physically and not in SAP Datasphere.

Federated (Virtual) Access to Business Partner Data Source

In the third step of the data harmonization, we use an SAP BW/4HANA system as the data foundation. This source is bound to our SAP Datasphere by using the connection BW4_DSP. We import two BW aDSOs as remote tables in our SAP Datasphere. Here, the data refers to business partners.

  1. For the Data Flow creation, the required remote tables are imported first.

  2. In the Data Builder, select Import -> Import Remote Tables.

  1. In the Import Remote Tables wizard, select the connection BW4_DSP.

  1. Press the Next Step button.

  2. Expand Extractors -> BW -> D… and select the tables DWC_BP and DWC_ADR.

  1. Press the Next Step button.

  2. Adapt the Business and Technical Name for both objects:

    • Addresses for the object refering to ‘BW4_DSP/BW.DWC_ADR$F’
    • BusinessPartners for the object refering to ‘BW4_DSP/BW.DWC_BP$F’

  3. Select the Import and Deploy button, to confirm and import both remote objects.

  1. After successful import execution, press the Close button.

  2. Both remote tables are listed in the object list overview.

In this step we replicated the metadata of two SAP BW/4HANA aDSOs about business partners into our SAP Datasphere.

The access method is a pure federated (virtual) one! The tables including their data are still in the SAP BW system physically and not in SAP Datasphere.

Persistent Access to Business Partner Data

  1. Click BusinessPartners in the object list to open the table editor.

For data persistency, below options are available.

  • Data Replication (snapshot load)
  • Real-Time Data Replication
  • Replication Scheduling

  1. Select the Remote tab or scroll down to the Remote section.

  2. Choose Start Data Replication as replication option.

  1. Select the ‘Refresh Replication’ icon to monitor the progress. A message about successful replication appears. The table Data Access property is changing from *Remote to Replicated (Snapshot).

  2. Open the Go to Remote Table Monitor link.

  3. The replication action details are displayed in the Data Integration Monitor.

  4. Close the Replication Task view of Business Partners.

In the Data Integration Monitor let us start the replication for the second SAP BW remote table too.

  1. Go into the Details '>’ of the remote table Addresses.

  2. Select Data Replication and choose the option Start Data Replication.

  3. A message about initializing the replication appears. You may refresh the action details to see the progress.

  1. Close the Replication Task view of Addresses.

  2. The data of both SAP BW objects is persisted into SAP Datasphere now.

  3. Select Data Builder in the side navigation area, leaving the Data Integration Monitor.

  4. Your Data Builder object list shows your previous created objects:

    • MSSQL Remote table: Products
    • MSSQL Remote table: ProductTexts
    • SAP BW Remote table: Business Partners
    • SAP BW Remote table: Addresses

What was done ?

In this step, we replicated the transactional data of two SAP BW/4HANA aDSOs (Advanced DataStore Objects) into our SAP Datasphere. The access method changed from federated (virtual) to persistent. Both tables including their data are physically stored in SAP Datasphere.

Congratulations

You have taken an insight into the system source connections and their validations. Based on the source connections, you have imported remote tables from Microsoft SQL database and SAP BW/4HANA aDSOs to make the data available in your space.

First the access method is a pure federated (virtual) one! The tables including their data are still in the source systems.

Alternatively, the source data can be replicated into SAP Datasphere, which you have done for the SAP BW/4HANA objects. Optionally, the replication execution can be scheduled, depending how often the (master) data in the source system changes.

Learning

SAP FacebookSAP YoutubeSAP LinkedIn