Defining business processes and capabilities by developing a business architecture helps articulate what data services, data sources, and data types are required to support those business processes and capabilities. Knowing which business functions utilize information; where data is created, consumed, and stored; and which technology components store and manipulate the information is crucial to achieving business objectives.
An enterprise has to define the data sources and data types required to support its business activities in a way that can be understood by all relevant stakeholders. All of the important information processing needs of the enterprise must be captured in the information architecture. A well-developed Information Architecture will enable the enterprise to identify any shortfalls in data services such as the wrong type of data being delivered or data not being in the right location.
It is important to note that Information Architecture is not necessarily about designing logical or physical storage systems though relationships with existing systems may be depicted. The goal of Information Architecture is to define the data entities relevant to the enterprise.
SAP Enterprise Architecture Designer provides your enterprise with the capabilities to model your information architecture from a database-independent Conceptual Data Model, a Physical Data Model, and modelling data movement between your data locations. SAP EAD is a useful tool for capturing, analyzing, and presenting your enterprise's information landscape, data requirements, and relationships using industry standard notations, models, principles, and techniques.
Business processes consume and produce data. As introduced in Chapter 1, Information Architecture comprises rules, standards, policies, and models that determine the nature of the data to be collected, the manner in which it is stored and leveraged by your enterprise. A data architecture describes the data structure utilized by your enterprise’s applications and business processes. This chapter explores how SAP Enterprise Architecture Designer can be utilized in developing your enterprise’s Information Architecture. This chapter will close by demonstrating how SAP accommodates other vendors’ databases through its reverse-engineering capabilities.
Conceptual Data Model
The Conceptual Data Model is a business-driven description of an enterprise’s data structures and assets. For example, in an ecommerce business, a sales order is executed by a customer; the customer has a name, address, and other data required to complete the sales order. The information that is required for the sales order process and all other business processes in the enterprise should be identified through a Conceptual Data Model. These items are later transformed into a format that a specific database can process.
The conceptual data model is used to design and analyze the information needs from a business perspective and help you identify the principal entities to be represented, their attributes, and the relationships between them. The visually driven Conceptual Data Modelling with the SAP EAD is database independent, supports many-to-many relationships, and supports inheritance.
Entities
Attributes (data items)
Relationships
Structured data types
Associations
Inheritances
To reiterate, the Conceptual Data Model is about identifying what information the enterprise’s day-to-day business processes need and how to represent that information in a manner that can be understood by stakeholders.
See 6.1 Conceptual Data – User Guide – SAP Enterprise Architecture Designer – Document Version: 1.0.4, Page 158.
https://help.sap.com/doc/1216ca7ff76848a4befadaf6145d04ec/1.0.04/en-US/ea_designer_en.pdf
Entities
A business has objects it would like to store information about such as a person/customer, sales orders, sales person, etc. Those objects are represented as entities. The information that is used to describe the entity (e.g., “First Name” or “Title” of the person entity) is represented by attributes or data items. The entity stores all of these attributes as a data set.
See 6.1.1 Entities – User Guide – SAP Enterprise Architecture Designer – Document Version: 1.0.4, Page 159.
https://help.sap.com/doc/1216ca7ff76848a4befadaf6145d04ec/1.0.04/en-US/ea_designer_en.pdf
Attributes (Data Items)
Data items or attributes represent the characteristics of an entity. What does the business need to know about the person? Their first name, telephone number, address, etc.
See 6.1.1.1 Attributes – User Guide – SAP Enterprise Architecture Designer – Document Version: 1.0.4, Page 160.
https://help.sap.com/doc/1216ca7ff76848a4befadaf6145d04ec/1.0.04/en-US/ea_designer_en.pdf
SAP EAD allows you to either create the attributes of an entity directly in the entity or to create separate data items that can be reused as attributes in different entities. You can define a data item such as “name” of a person with a variable data type and character limit of 20. You can then link any entity’s name attributes to this data item rather than creating a new name attribute for that entity.
Structured Data Types
Structured data types better define an attribute through a combination of data items. For example, money can be better described by two data items, its value and its currency. A structured data type called “amount” that consists of value and currency can be created for this purpose.
Identifiers (Index)
An entity will also consist of an index that represents a unique identifier of each data set of attributes. This essentially identifies each occurrence of the entity. For example, for the person entity, the person ID can be an index that uniquely identifies each person and their associated attributes (dataset) such as first name, address, telephone number, etc.
Relationships
One-to-many relationship
One-to-many dependent relationship
Each order may contain one or more order lines. Each order is contained by exactly one order. The triangle signifies the dependency.
The fundamental difference between the one-to-many relationship and the one-to-many dependent relationship is the fundamental question, could or must?
Many-to-many relationship (M-to-N relations)
Each shipment may have one or more products. Each product may be in one or more shipments.
See 6.1.2 Relationships – User Guide – SAP Enterprise Architecture Designer – Document Version: 1.0.4, Page 163.
https://help.sap.com/doc/1216ca7ff76848a4befadaf6145d04ec/1.0.04/en-US/ea_designer_en.pdf
Inheritances
Inheritance specifies a parent-child-like relationship between entities. The parent holds attributes that are common to multiple entities, whereas the child only holds attributes specific to it.
See 6.1.3 Inheritances – User Guide – SAP Enterprise Architecture Designer – Document Version: 1.0.4, Page 166.
https://help.sap.com/doc/1216ca7ff76848a4befadaf6145d04ec/1.0.04/en-US/ea_designer_en.pdf
Once the Conceptual Data Model (CDM) has been developed, SAP EAD can generate a Physical Data Model based on the CDM. If there are any changes to that CDM, you can regenerate to that same Physical Data Model to reflect those changes. Changes will go through unless they conflict with changes made on the PDM’s side. There is an opportunity to review changes before regenerating the PDM.
The inheritances generated are controlled by inheritance options which determine which tables are generated among the parents and children of your inheritances.
See 6.1.7 Generating a CDM to a PDM – User Guide – SAP Enterprise Architecture Designer – Document Version: 1.0.4, Page 171, available at
https://help.sap.com/doc/1216ca7ff76848a4befadaf6145d04ec/1.0.04/en-US/ea_designer_en.pdf
Domains
SAP EAD also includes the concept of domains. Domains are standard data types for a data model that ensure consistency on a data model once it is transformed into a physical data model or during an extract, transform, and load process. This is particularly handy when using the same data model on different databases that may interpret it differently. A name attribute of 60 characters may be transformed into a name attribute of 20 characters in another database.
The Conceptual Data Model is an efficient way to structure your thoughts and describe the important information in your business and is useful for communicating ideas to various stakeholders.
Generating a Physical Data Model
A Physical Data Model (PDM) is a database-specific model that represents the actual relationships and structure of data within the database. A physical data model can be used to generate Data Definition Language statements (DDL) which can then be deployed to a database.
- 1.
Generate from existing Conceptual Data Model.
- 2.
Reverse-engineer an existing DBMS into a Physical Data Model.
- 3.
Create a new Physical Data Model.
Generate a Physical Data Model from an Existing Conceptual Data Model
All of the objects defined in the CDM are transformed into objects applicable to the PDM.
View with column and view index
User, schema, group, role
Domain, abstract data type, sequences
The PDM is similar to the CDM, but there are some differences. The PDM includes arrows that now represent what were relationships in the CDM but are now references in the PDM.
Generating a Physical Data Model from an Existing Database Schema
SAP HANA 2.0 Deployment Infrastructure (HDI)
SAP HANA 2.0 Database
SAP SQL Anywhere 17
Oracle 12c
Microsoft SQL Server 2016
IBM DB2 v11 for z/OS
Teradata 15
The reverse-engineering database selection page in SAP EAD has a left panel that shows available database schemas to choose from. Selecting a schema reveals the objects available for selection when generating a PDM. Objects can be selected from multiple schemas.
SAP EAD shows the selected objects and automatically includes default items in your model.
There are instances when there may be a need to generate or regenerate into an existing PDM. In this situation, a “merge screen” will appear for comparing the new PDM’s selected objects and the existing PDM’s objects. It lists all differences and includes radio buttons and checkboxes to select the changes to take place and to which objects. SAP EAD utilizes graphic colors to represent areas where changes will take place.
Furthermore, SAP EAD includes the capabilities to make manual changes to the model before generating it. SAP EAD also automatically migrates the primary key in the process.
While all of this is taking place, on the bottom right of the screen, SAP EAD compiles the Data Definition Language (DDL) statements for the generation of this model.
These DDL statements can be saved as files, on Git or directly to the database. Select the “Save” tab on the top-right corner and select “Generate Database.” If you choose to generate the PDM directly to the database, you will get the option to select which objects you would like to generate.
There is an option of previewing the DDL statements before saving them.
Once the model has been generated, SAP EAD provides functionality to verify that the model is working. Additionally, SAP EAD also runs its own rule-based checks to identify any issues with the model and flags them graphically.
It is important to note that the SAP HANA function of virtual or proxy tables can be depicted in SAP EAD. Virtual tables are tables in another database that can be accessed remotely.
If you choose to generate the PDM directly to the database, you will get the option to select which objects you would like to generate.
Data Movement Model
Most enterprises have information stored in different databases and applications. A Data Movement Model (DMM) provides insight into the movement of this data between different points and how it is transformed along the way. A DMM depicts the flow and transformation of data from source models to target models.
For more information, see 6.4 Data Movement – User Guide – SAP Enterprise Architecture Designer – Document Version: 1.0.4, Page 297, available at
https://help.sap.com/doc/1216ca7ff76848a4befadaf6145d04ec/1.0.04/en-US/ea_designer_en.pdf
As a guide for an ETL tool implementation
To generate FlowGraph in SAP HANA’s own ETL tool, Smart Data Integration (SDI)
A new Data Movement Model can be created, or an existing FlowGraph can be edited through reverse-engineering a FlowGraph file.
Creating a New DMM Diagram
Generating Conceptual Objects into a Physical Data Model
Conceptual Objects | Standard DBMS | SAP HANA 2.0 HDI |
|---|---|---|
Entities | Tables | Entities |
Attributes | Columns | Elements |
Identifiers | Keys | Elements with Key property set to true |
Relationships | References | Associations |
Inheritances | Options control generation of parent and child tables | Options control generation of parent and child entities |
Domains | Domains | Simple types |
Structured types | Abstract data types | Structured types |
Data items | [Not generated] | [Not generated] |
DMM Constructing Elements
Constructing Element | Use |
|---|---|
Data input | Represents a source of data in a data transformation diagram and is linked to a database, an XML document, a web service, or a flat file |
Data output | Represents a target destination to load data in a data transformation diagram and is linked to a database, an XML document, or a flat file |
Data join | Performs a join on two or more input flows and combines them |
Data query | Executes an SQL query against a database for each row of the input flow to transform it and create a new data flow. Data from the input flow can be used as a parameter |
Data filter | Filters incoming rows using SQL criteria |
Data union | Combines two or more identical input flows into a single output flow |
Data flow | Conveys data between steps in a data transformation diagram |
Data aggregation | Aggregates incoming data using functions such as Avg, Min, Max |
Data projection | Responsible for data transformations such as reordering or deleting columns |
Data sort | Sorts incoming rows by one or more data structure columns |
Data split | Duplicates a simple input data flow into two or more identical output data flows |
Transform | Performs various simple transformations such as joins, sorts, filters, and aggregations |
- 1.
Select the data input constructing element to specify the data source. Thereafter, assign an existing entity from an SAP EAD model to the database input element as the data source.
- 2.
It is possible to manually map the source elements (attributes) with the target elements by adding the data projection element and defining the mapping.
- 3.
Any required filtering can be added to the incoming data with the filter element by defining filtering criteria in SQL.
- 4.
Lastly, add a data output element and assign an existing SAP EAD model object.
Using SAP EAD, we can now generate the FlowGraph file that can be utilized by developers in Web IDE for SAP HANA. This is a simplified example that clearly shows how DMM can be developed from a given SAP EAD model and used to generate a FlowGraph useful for implementing an extract, transform, and load (ETL) tool.
For more information, see 6.4 Data Movement – User Guide – SAP Enterprise Architecture Designer – Document Version: 1.0.4, Page 298, available at
https://help.sap.com/doc/1216ca7ff76848a4befadaf6145d04ec/1.0.04/en-US/ea_designer_en.pdf
Reverse-Engineering a FlowGraph File
As mentioned, a FlowGraph file can be reengineered from SAP Web IDE for SAP HANA. Simply select the option “reverse engineer flowgraph file” in the menu and navigate to the FlowGraph file.
For further information, see 6.4.4 Reverse-Engineering – User Guide – SAP Enterprise Architecture Designer – Document Version: 1.0.4, Page 297, available at
https://help.sap.com/doc/1216ca7ff76848a4befadaf6145d04ec/1.0.04/en-US/ea_designer_en.pdf
Non-SAP Databases and Data Models
IBM DB2 Version 11 for z/OS
Microsoft SQL Server 2016
Oracle 12c
SAP HANA 2.0 Database
SAP HANA 2.0 HDI
SAP SQL Anywhere 17
Teradata Version 15
Reverse-Engineering an Existing Schema
- 1.
Click Create New Diagram to start creating a new model in SAP EAD.
- 2.
In the menu, select “Reverse Engineer Database.”
- 3.
You can either connect to the database by specifying connection details or upload a DDL statement file.
- 4.
The SAP EAD Reverse Engineering selection page allows you to select the elements you would like to reverse-engineer.
The physical data model is then generated including the selected objects. This data model can now be regenerated for any other database.
This process transforms any physical characteristics that don’t match between the PDM and the target database. For example, the PDM may have a data type called “VarChar2,” whereas the target database calls the same data type “VarChar.” This process is similar to the process shown on how to generate objects from a PDM in a database using the same merge view that compares the differences between the PDM and the target database model.
Summary
This chapter sought to share some of the Information Architecture capabilities of SAP EAD. Every business has information it requires to collect, consume, and produce in order to effectively execute its business processes. SAP EAD helps to model this information in a manner that can be communicated to multiple stakeholders.
Information Architecture starts with modelling the Conceptual Data Model which captures all of the information requirements and types independent of any database. SAP EAD provides the functionality to efficiently model and verify the Conceptual Data Model and use it to generate a Physical Data Model. Any changes to the original Conceptual Data Model can be regenerated into the Physical Data Model and update it.
The Physical Data Model is database specific and hence more detailed than the Conceptual Data Model. The Physical Data Model can also be reverse-engineered from an existing database and regenerated for another database using SAP EAD. SAP EAD also allows you to create a completely new Physical Data Model.
For the purposes of ETL (extract, transform, and load), a Data Movement Model can be utilized to depict the flow of data from and to different data stores and applications. The Data Movement Model can also be used to generate a FlowGraph that can be used with SAP HANA’s ETL tool “SDI” to transform data from a remote source into SAP HANA.
Lastly, SAP accommodates other vendors’ databases through the reverse-engineering functionality. You can reverse-engineer the database in SAP EAD producing a physical data model that can then be generated into another database model if need be.