Connecting to databases or applications using Datastores

Objectives

After completing this lesson, you will be able to:

  • Connect to databases or applications using Datastores

Datastores

Datastores contain connection information to databases and applications that contain your data.

SAP Data Services uses connection information in datastores to access metadata from the specified database or application.

It means it accesses information about the data, like name and structure of your tables, and data types of the columns, for example.

When you import from the datastore connection, Data Services actually imports the metadata from the database or application.

Data Services accesses the data when you include source metadata, such as metadata for a table, in jobs. If the database or application supports it, Data Services uses the connection to upload processed data.

Note

In this course, we will focus on database datastores. Other supported datastore types include: Application (for example, SAP NetWeaver BW); Web Service (REST or SOAP); and Adapter (including OData, JMS, JDBC)

Datastore Creation

Basically, you need three steps to use a datastore:

  • Connect to the data source.

  • Import metadata from the data source.

  • Read from or write to the data source by using the datastore metadata in a data flow.

Let's Try It

Let me guide you through the creation of your first datastore:

Datastore Metadata

In order to use tables (or other objects) in data flows, you need to import metadata after creating the datastores. This imported metadata includes names of tables and columns, data types and keys.

Imported Objects Use

On a basic level, use imported objects in SAP Data Services as follows:

  • As a source object: Data Services accesses the data through the connection information in the datastore and loads the data into the data flow.
  • As a target object: Data Services outputs processed data from the data flow into the target object and, if configured to do so, uploads the data to the database or application using the datastore connection information.

The easiest way to import metadata is by browsing. You find the table (or other object) that you want to import on the External Metadata tab of the Datastore Explorer, then right click and choose Import.

In situations where this method is not possible or convenient, you can alternatively import by name.

Let's Try It

Let's see how you can import metadata from your Alpha datastore:

Datastore Maintenance

If there is some changes in table (external) metadata, for example column names, re-importing the metadata is a best practice. It is very similar to the initial import by browsing, and will update the repository metadata to match the current external metadata.

Let me show you how it can be done:

Reconcile vs Reimport

  • Reconcile compares external metadata to repository metadata – showing which objects have changed (either at data source or by editing in the repository). It does NOT actually update the repository metadata.
  • Reimport will overwrite the repository metadata with current values of the external metadata. Any changes made to the metadata in Data Services would be lost.

For changes in the database itself (for example, database name or server location), you would use the Edit Datastore screen. There are a few values that cannot be edited this way (the actual datastore name, database type, and version) and might require actually recreating the datastore.

Datastore Configurations

It is possible to store multiple sets of configuration information inside of a single datastore.

Implementing multiple datastore configurations adds complexity, but can increase flexibility in moving objects between different environments or database instances.

The details about datastore configurations and system configurations are well documented here: Multiple datastore configurations | SAP Help Portal

Log in to track your progress & complete quizzes