Creating Datastore

 See more in sap bods step by step

This Post describes following:

All the preparatory work needed to define data movement specifications for a flat-file data source to a target data warehouse. In this post you will:
• Define a datastore from Data Services to your target data warehouse
• Import metadata from target tables into the local repository so that you can use the Designer to work with these tables
• Define file formats and a connection to flat-file source data

You can refer to the post Setting up the system in BODS for more details.

Defining a Datastore

Datastores:
• Provide a logical channel (connection) to a database
• Must be specified for each source and target database
• Are used to import metadata for source and target databases into the repository.
• Are used by Data Services to read data from source tables and load data to target tables

Define a Datastore for Source Database-

1. From the Datastores tab of the object library, right-click in the blank area and click New.
   The "Create New Datastore" window opens. An example for the Oracle environment appears as follows:

2. In the Datastore name box, type ODS_DS.
This datastore name labels the connection to the database you will use as a source. The datastore name will appear in the local repository.

When you create your own projects/applications, remember to give your objects meaningful names.
3. In the Datastore type box, click Database.
4. In the Database type box, click the option that corresponds to the database software being used to store the source data.
The remainder of the boxes on the "Create New Datastore" window depend on the Database type you selected.

5. Click OK.
Data Services saves a datastore for your source in the repository.

Define a datastore for the target database

Define a datastore for the target database using the same procedure as for the source (ODS) database, use Target_DS for the datastore name.

Importing Metadata

You can  import the metadata using browsing, by name or by searching. Below steps illustrate on how to do it using 'Browsing'.

Import Metadata for ODS Source Tables

In the Datastores tab, right-click the ODS_DS datastore and click Open.
The names of all the tables in the database defined by the datastore named ODS_DS display in a window in the workspace.
2. Move the cursor over the right edge of the Metadata column heading until it changes to a resize cursor.
3. Double-click the column separator to automatically resize the column.
4. Import the tables by right-clicking each table name and clicking Import. Alternatively, because the tables are grouped together, click the first name, Shift-click the last, and import them together. (Use Ctrl-click for nonconsecutive entries.)

Data Services imports the metadata for each table into the local repository.

Note: The prefix 'ods' can denote that the source is oracle database. If the source is Microsoft SQL Server, you can use the prefix 'dbo'

5. In the object library on the Datastores tab, under ODS_DS expand the Tables node and verify the tables have been imported into the repository.

Import Metadata for Target Tables

1. Open the Target_DS datastore.
2. Import the tables by right-clicking each table name and clicking Import. Alternatively, use Ctrl-click and import them together

Data Services imports the metadata for each table into the local repository.

3. In the object library on the Datastores tab, under Target_DS expand the Tables node and verify the tables have been imported into the repository.

Defining a File Format

If the source or target RDBMS includes data stored in flat files, you must define file formats in Data Services. File formats are a set of properties that describe the structure of a flat file.
Data Services includes a file format editor. Use it to define flat file formats. The editor supports delimited and fixed-width formats.

You can specify file formats for one file or a group of files. You can define flat files from scratch or by importing and modifying an existing flat file. Either way, Data Services saves a connection to the file or file group.
Note: Data Services also includes a file format (Transport_Format) that you can use to read flat files in SAP applications.

Steps to define a File format

In the object library, click the Formats tab, right-click in a blank area of the object library, and click New > File Format.
The file format editor opens.
2. Under General, leave Type as Delimited. Change the Name to Format_SalesOrg.
3. Under Data File(s), beside File name(s), click the Files folder icon and navigate in your Data Services, Click Open.

4. Under Default Format, change Date to ddmmyyyy.

5. The first row contains the names of the column headings. Under Input/Output, change Skip row header to Yes.

Note:
A prompt asks you to verify that you want to overwrite the current schema with the schema (column headings) from the file you selected. Click Yes.

6. Click Save & Close.

 

Refer to the ebook for more details: New Ebook – SAP BODS Step by Step