📝 Note: This is an Optional Exercise
Let us clarify what an Entity Relationship model is and why we are creating it, before we start the exercise.
An Entity Relationship Model provides a variety of benefits:
- Definition of entity-relationship models
- Design physical or remote database models
- Re-use existing entities (table, view) from Data Builder
- Add new entities on-the-fly
- In-editor real time data preview
- Model Import / Export
Basically, the Entity Relationship Model is not a view that you would consume in SAP Analytics Cloud, but instead it represents the relationship between the tables or views, and it helps you to define the relationship once, so that when you create a new view, that you do not have to define those relationships each time.
-
Log On to your SAP Data Warehouse Cloud tenant.
-
Select the menu option Data Builder on the left-hand side
-
Select the option New Entity - Relationship Model.
-
On the left-hand side you are presented with the local Tables and Views from your Repository and you also have the option to bring in Tables from any remote Sources that you have configured.
-
Ensure to select the option Repository, so that we see the local Tables.
-
Open the list of Tables.
-
Here you should see the tables that we created previously.
-
Now drag and drop the table Sales Transactions to the canvas.
-
When you select the Sales Transactions table on the canvas, you are presented with additional options, that allow you to add a new column, create a new View, create a new table, create a join, open the table in the editor, and to preview the data.
-
Now drag and drop the table Store to the canvas next to the table Sales Transactions.
-
Select the table Sales Transaction.
-
Click on the "->" symbol to Create a new Association.
-
Click on the arrow symbol and drag the icon over to the table Store. You are creating a new Association between the table Sales Transactions and the table Store.
-
In the Properties panel on the right-hand side click on the Expand icon.
-
Ensure that the join between the table Sales Transactions and Store is based on column Store ID (STORE_ID). The system suggests this based on matching columns, but you can also add / remove these joins manually
-
Click on the Expand icon in the top right corner again to reduce the size of the panel.
-
Now drag the table Product onto the canvas.
-
Select the table Sales Transactions on the canvas.
-
Select the arrow symbol and drag and drop the arrow to table Products to create a new Association between the table Sales Transactions and table Product.
-
Ensure that the join between the Sales Transactions table and table Product is based on the Product ID (Product ID) column.
-
Now drag the table Sales Manager onto the canvas.
-
Create a new Association between table Sales Transactions and table Sales Manager.
-
Ensure that the join between the Sales Transactions table and the Sales Manager table is based on the Sales Manager ID (Sales Manager ID) column.
-
On the left hand side in the Repository panel, open the list of Views
-
Now drag the View Time Dimension – Day to the canvas.
-
Select the table Sales Transactions on the canvas.
-
Select the arrow symbol and drag and drop the arrow to the view Time Dimension - Day to create a new Association between the table Sales Transactions and the view Time Dimension - Day.
-
In the panel on the right-hand side now, create a join between the Transaction Date column and the Date column.
-
You can use the option to arrange all tables in the toolbar.
-
Your Entity Relationship Model should look like the image shown below
-
Save the changes to your Entity Relationship model by using the Save option in the General menu.
-
Enter the following details:
- Business Name - My First Entity Model
- Technical Name - My_First_Entity_Model
- Click Save.
- Deploy the model.
You've now created the Entity Relationship Model and will make use of it in the next steps when we create the Views.
Continue to - Exercise 05: Importing Tables (part of Fast Track and a mandatory exercise)