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 UA’s ERP system (SAP R/3). • What type of data integration is this?

3. Tasks • Model the data in UA’s SAP system • Model the booking data in Orbitz. • Construct the mapping from the Orbitz ERD to UA’s 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 = CZero or one • m = 1 exactly one • m = C Zero or one • m = N Many • m = CN Zero, one, or many

8. UA’s 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 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.

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.(TC:SD11) • 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 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.

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 TC:SE11