Entity Relationship Model in SAP Datasphere: Beginner Overview
The Entity Relationship Model( ERM) serves as a framework for designing databases and data models in Datasphere. It allows you to visually represent the structure of your data and the relationships between the different entities.
Let’s walk through the process of creating an Entity Relationship Model( ERM) in SAP Datasphere using an illustration of an online retail store. We will define the entities, attributes, and relationships, and then create an ERM diagram step by step.
Example- Online Retail Entity Relationship Model
- Customer: CustomerID (primary key), FirstName, LastName, Email
- Product: ProductID (primary key), ProductName, Price
- Order: OrderID(primary key), OrderDate, TotalAmount
Entities and attributes: First of all find the necessary entities which are all involved in your domain. These entities represent real-world objects, concepts, or events. For Example, In a retail scenario, entities could include customers, products, orders, and suppliers. Each entity has attributes that describe its characteristics, such as a customer name, order date, product price etc..
Customer-Order: One-to-Many (a single customer can place multiple orders)
Order- Product: Many-to-Many (an order can contain multiple products, and a product can be part of multiple orders)
Relationships help to find out how different entities are related to each other. In SAP Datasphere, you can establish different types of relationships such as One-to-one, One-to-Many, and Many-to-many. For Instance, we can give a one-to-many relationship between customers and orders because a customer can place multiple orders.
Step 1: Create or Import Necessary Tables:
Log in to SAP Datasphere.
Create a new space for your project if you haven’t created it.
Inside the space, go to the” Data Builder” section.
Likewise, create appropriate tables similar to the customer table mentioned above( order, product).
Either you can create a local table and import the data via CSV files or instead of local tables, we can also use remote tables from data sources if necessary. In this example, let me use the local table example.
Step 2: Identify relationships:
Customers place orders, and orders contain products. To show this, you will need to create links between the tables. In the order table, create a column called CustomerID. This column will link back to the customer table. In the order table, create another column called OrderID. This column will link to the OrderProduct table. The OrderProduct table will have two columns: OrderID and ProductID. These columns will link the orders and products.
For the order-product relationship, produce an intermediate table (e.g., OrderProduct) to represent the many-to-many relationship. This table will have columns” OrderID”( foreign key field representing the order table) and” ProductID”( foreign key field representing the Product table).
Step 3: Creation of the ER Diagram:
After creating all the necessary tables, the ER model can be created.
In the Data Builder, click New Entity Relationship Model.
Drag and drop the customer table, product table, and order table into the canvas.
Arrange the tables on the canvas.
Click the Create Association button and drag it to the order table, and the association will be created with CustomerID.
Likewise, join all the tables with respect to the relationship as mentioned above.
Use connectors to show the relationship between the tables. Connect the customer table to the order table and the order table to the product table.
Step 4: Consuming the ER model in a view:
After establishing the relationships, the Entity-relationship model is created.
As we already maintained the relationship between the tables, we can use those tables in a graphical view.
Now in the data builder, click on “graphical view.”
Drag the customer table into the canvas.
And click on the + icon (Related Entities), and it will pop up the related entities that we created in the ER model so that we can use it accordingly
Define the cardinality by specifying the number of related records. For the Customer-Order relationship, set it as “1 to Many”. For the Order-Product relationship, set it as “Many to Many”.
Similarly, add all the related entities that are all required and complete the graphical view. Make sure the relationships and the cardinalities match your model.
Step 5: Save and Share:
Now the created Entity-Relationship Model( ERM) visually represents the Entities, attributes and Relationships, within your data model. It provides an intuitive overview of the structure and relations of your data. As your business conditions evolve you can easily make variations and expansions to your ERM Diagram in SAP Datasphere to accommodate changes and optimizations.