Data Modeling for Integrating SAP with other Applications

1
Data Modeling for Integrating SAP with other
Applications
2
Problem

• United Airline needs to integrate its ERP system
  (that handles scheduling, booking) with Orbitz,
  which provides Web-based airline reservation
  service.
• Based on sales forecasts, UA blocks certain
  number of seats/flights for Orbitz each day. At
  midnight each day, Orbitz sends transactions of
  the day to UAs ERP system (SAP R/3).
• What type of data integration is this?

3
Tasks

• Model the data in UAs SAP system
• Model the booking data in Orbitz.
• Construct the mapping from the Orbitz ERD to UAs
  ERD.
• Choose a message-oriented middleware (MOM) for
  transmitting midnight updates.

4
Learning Objectives

• Be able to use SAP development tools for (task 1)
• Displaying tables and their relationship
• Constructing ERD
• Preview
• MOM (task 4) will be covered next week

5
SAP Data Modeler

• A development tool from the ABAP/4
• Based on the SAP SERM method (Structured Entity
  Relationship Model)
• Basic objects are entity types and the
  relationships
• Modeling involves use of a graphical editor (not
  available for browser client)

6
Data Modeler Elements

• An entity (table record) is a physical or
  abstract object.
• Each entity type (table or View) describes a set
  of entities with attribute.
• An attribute (field) consists of a name and a
  definition of the possible values for the
  attribute (for example, the attribute color with
  the values white, black, yellow, and so on).
• One or more attributes are labeled as key
  attributes. The values of the key attributes
  uniquely identify an entity within its entity
  type.

7
Relationships

• Relationships between two tables the source
  table and the target table.
• Unlike ERD, the relationship itself can not be a
  table.
• Hence, no many-to-many relationship.
• The relationship is oriented from the source
  table to the target table.
• The cardinality (n m) describes the possible
  values for the left and right sides of the
  cardinality as follows
• n 1 exactly one
• n C Zero or one
• m 1 exactly one
• m C Zero or one
• m N Many
• m CN Zero, one, or many

8
UAs Data Model (Simplified)

• The mode describes how passengers can reserve
  seats or space
• Reservations are made either through a sales
  office of the airline, or through a travel
  agency.
  
• The data model contains the entity sets
  aircraft, airline connection and flight booking.
  

9
Display the data model as a Graphic

• Not available for web browser client
• Navigation through the diagram
• Edit on the graphics such as adding a new node

10
Assigned Tables in the ABAP Dictionary

• T000 Client table
• SCURX Currencies (key currency key)
• SBUSPART Business partner (key client, partner
  number)
• STRAVELAG Travel agencies (key client, travel
  agency number)
• SCUSTOM Customers (key client, customer number)
  
• SCARR Carriers (key client, carrier ID)
• SCOUNTER Sales counters (key client, carrier
  ID, sales counter number)
• SPFLI Flight schedule (key client, carrier ID,
  connection number)
• SFLIGHT Flights (key client, carrier ID,
  connection number, date of flight)
• SBOOK Flight bookings (key client, carrier ID,
  connection number, date of flight, booking
  number, customer number)

11
Relationships between the Tables

• Table SBUSPART contains all the business partners
  of a carrier, which is identified by a number in
  this table. The data of the contact person for
  the business partner is also stored. A business
  partner can be a travel agency or a customer
  (e.g. company that frequently books flights
  directly with the carrier).
• The data for travel agencies are stored in table
  STRAVELAG.
• The data for customers are in table SCUSTOM.
• There is an entry with the same key in either
  table STRAVELAG or table SCUSTOM for each entry
  in table SBUSPART.
• Table SCARR contains the IDs and names of the
  carriers. Each carrier has a number of
  connections. These flight connections are stored
  in table SPFLI.
• Table SFLIGHT contains the concrete flight data
  for each connection.
• Bookings can be made for each flight in table
  SFLIGHT.
• The bookings made for each flight are entered in
  table SBOOK. The customer number or agency number
  for which the booking was made is stored in
  table.
• The carriers have sales counters in the airports.
  These sales counters are entered in table
  SCOUNTER.
• If the customer books his flight directly at a
  counter, the counter number is also entered in
  the booking data in table SBOOK

12
Foreign Keys 

• A foreign key of table T1 is a primary key of
  another table (e.g. T2).
• Table T1 is called the foreign key table
  (dependent table) and table T2 the check table
  (referenced table).
• One field of the foreign key table therefore
  corresponds to each key field of the check table.
  This field is called the foreign key field.
• Check Field and Value Check

13
Foreign Keys - Example

• Table SBOOK in the flight model contains the
  customers flight bookings for a carrier.
• If the booking was made at a counter, its number
  is stored together with the booking in field
  COUNTER in table SBOOK.
• You must make sure that only correct counter
  numbers can be entered. All the counters are
  entered in table SCOUNTER.
• The necessary value check can be defined by
  creating a foreign key for check field COUNTNUM.

14
SAP Lab

• Login to SAP
• Follow instructions in the following slides

15
Procedure for Displaying the Flight Model

• Tools-ABAP Workbench- Development-Data
  Modeler.(TCSD11)
• Enter BC_TRAVEL in field Modeling object and
  choose Data Model.
• Data Model (lower level) To display, double click
  the field
• Entity Types to display definition, double click
  the field

16
Choosing Tables to View

• In the entity display screen, click the button
  Dictionary
• Then you can see the table name, then click
  Table/view
• You can display table relations by click incoming
  relation button.

17
Display Relation

• Incoming relation means This entity type as
  target
• Source entity and Cardinality
• You can also display out-going relations viewed
  as source entity
• Click here to switch the reviews

18
Table Structure View

• To display a table
• A table definition in the ABAP Dictionary
  contains the following components
• Table fields define the field names and data
  types of the fields contained in the table
• Foreign keys define the relationships between the
  table and other tables.
• Click here to display table content

19
Table Data Browser

• Continued from the slide Table Structure view
  or TCSE16 Then table name SBOOK
• Then click the execute button Breen Check with
  small Clock
• Navigation buttons allow you to select columns,
  sort data, etc.
• Select a data entry (row) then click detail
  button.

20
Homework 4 Assignment (15 HW points)Due Nov 5th

• Task Create ERD (not SAP SERM) for SAP Customer
  delivery model (Data modeler name SDSLS30303)
• Include all tables and key fields
• Include two non-key fields of your choice (the
  most important two from your viewpoint)
• Read the next page before you start
• Tips Display data model-
• find out relations between entity type-
• look at table structures (fields, keys, foreign
  keys)-(Optional but helpful, display table
  content)
• -Then create the diagrams.

21
Tips for HW4 How to Display Tables from a View

• Very often SAP use views instead of tables for
  data model (A view is derived from one or more
  tables, but not stored physically)
• You need tables to find out key fields (for you
  homework)
• Three ways to display table
• Double click the table name when displaying the
  entity type, or
• If you are displaying view, first click tab
  table/join condition second double click the
  table name
• If you know the table name, just use TCSE11