data modeling for integrating sap with other applications
DESCRIPTION
Data Modeling for Integrating SAP with other Applications. Problem. United Airline needs to integrate its ERP system (that handles scheduling, booking) with Orbitz, which provides Web-based airline reservation service. - PowerPoint PPT PresentationTRANSCRIPT
Data Modeling for Integrating SAP with other Applications
School of Information Science and Technology
Prof. John Yen [mailto:[email protected]]
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 UA’s ERP system (SAP R/3).
What type of data integration is this?
School of Information Science and Technology
Prof. John Yen [mailto:[email protected]]
Tasks
1.Model the data in UA’s SAP system2.Model the booking data in Orbitz.3.Construct the mapping from the
Orbitz ERD to UA’s ERD.
4.Choose a message-oriented middleware (MOM) for transmitting “midnight updates”.
School of Information Science and Technology
Prof. John Yen [mailto:[email protected]]
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
School of Information Science and Technology
Prof. John Yen [mailto:[email protected]]
SAP Data Modeler
A development tool from the ABAP/4Based 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)
School of Information Science and Technology
Prof. John Yen [mailto:[email protected]]
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.
School of Information Science and Technology
Prof. John Yen [mailto:[email protected]]
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
School of Information Science and Technology
Prof. John Yen [mailto:[email protected]]
UA’s Data Model (Simplified)
The mode describes how passengers can reserve seats or spaceReservations 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.
School of Information Science and Technology
Prof. John Yen [mailto:[email protected]]
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
School of Information Science and Technology
Prof. John Yen [mailto:[email protected]]
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)
School of Information Science and Technology
Prof. John Yen [mailto:[email protected]]
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
School of Information Science and Technology
Prof. John Yen [mailto:[email protected]]
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
School of Information Science and Technology
Prof. John Yen [mailto:[email protected]]
Foreign Keys - Example
Table SBOOK in the flight model contains the customer’s 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.
School of Information Science and Technology
Prof. John Yen [mailto:[email protected]]
SAP Lab
Login to SAP
Follow instructions in the following slides
School of Information Science and Technology
Prof. John Yen [mailto:[email protected]]
Procedure for Displaying the Flight Model
Tools->ABAP Workbench-> Development->Data Modeler.(TC:SD11)Enter BC_TRAVEL in field Modeling object and choose Data Model. Data Model (lower level) To display, double click the fieldEntity Types: to display definition, double click the field
School of Information Science and Technology
Prof. John Yen [mailto:[email protected]]
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.
School of Information Science and Technology
Prof. John Yen [mailto:[email protected]]
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
School of Information Science and Technology
Prof. John Yen [mailto:[email protected]]
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
School of Information Science and Technology
Prof. John Yen [mailto:[email protected]]
Table Data Browser
Continued from the slide “Table Structure view” or TC:SE16 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.
School of Information Science and Technology
Prof. John Yen [mailto:[email protected]]
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.
School of Information Science and Technology
Prof. John Yen [mailto:[email protected]]
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 TC:SE11