SAP Data Warehouse Cloud – Building Entity Relationship Model

SAP Data Warehouse Cloud is an enterprise-class Data Warehousing solution on Cloud by SAP. Powered by SAP HANA Cloud services, it integrates heterogeneous data from multiple sources and enables you to do advanced analytics using SAP Analytics Cloud.

An Entity-Relationship Model is the visual representation of data that helps business users in understanding the relationship between Tables and Views as well as the dependencies between them.

This blog is a continuation of the blog that details on building Entity-Relationship Model using Data Builder capabilities in SAP Datawarehouse Cloud Trial Account. For more details click here.

The scope of this blog covers various menus available in the window used for creating the Entity-Relationship Model in SAP Data Warehouse Cloud in the Trial Account.

Menus Explained

If you select an Entity in the Entity-Relationship Model, a list of the context menu is displayed and each one of them is explained below.

SAP Data Warehouse Cloud – Building Entity Relationship Model – Part 2

Add a column

This will help you to add a new column into the table Products provided no data is present in the table. If data is already present in the Table, you may need to truncate the data before adding a new column.

As you can see, ‘Column1’ is added with the default DataType String(100). The DataType can be changed by clicking on the ‘Edit’ icon in the Properties screen. You can also rename the Business Name and set the column as Key based on the requirement. The new column is not only added in the table Products in the Entity-Relationship Model but also in the local table created in the Space ‘HANA’. Another salient feature to be noted is that this option ‘Add a new column’ is available only if the entity is a Table. For views, this option is not applicable.

Create View from Selection

Using this option you can create a Calculation View using the selected entity as Source.

Enter the Business Name and Technical Name of the view and click OK.

Now you could see that the Calculation View is opened in an editor. You can edit the Calculation view based on the requirement and click on the ‘Back’ icon.

Create Association

In the Entity-Relationship Model, now you can use the Calculation View ‘Products View’ created in the previous step. In this case, let us replace the Table Products with the calculation view Products View and try to establish the same relationships which the entity Products had with other entities.

Select the entity PurchaseOrders, click ‘Create Association’ and drag onto the entity ‘Products View’.

Now you could see that the two entities are automatically joined based on the common field names.

Similarly, create an association between ‘Products View’ and ‘Suppliers’.

Create Table

Select the entity PurchaseOrders and click the option ‘Create Table’.

Using this option, you can create a table on-the-fly in the same space in which we are working on. Set Business and Technical Names for the table and use Edit option to add necessary columns.

In this case, a table ‘ProductCategory’ is created with necessary columns with an association created between ‘PurchaseOrders’ and ‘ProductCategory’.

Add Related Entities

Consider a scenario where you have inadvertently deleted the table Address from the canvas. Having said that, Select the entity Customers and click ‘Add related entities’.

A window will open which will suggest you that Address is a related entity to Customers. It also displays the related entity PurchaseOrders that is already added into the diagram. Select the checkbox against Address and click Select. In this case, SAP Datawarehouse Cloud automatically identifies the related entities based on the relationships already defined in the JSON file.

Data Preview

This option helps you to preview the data in an entity. Select the Entity ‘Customers’ and Click ‘Data Preview’. Since we didn’t load any data with the JSON file, no data is visible here. Click ‘Save’ and ‘Deploy’.

Significance of building Entity Relationship Models

To understand the impact of defining relationships between entities in Entity Relationship Model, navigate to Data Builder and try creating a new Graphical View. Click and Drag the table ‘PurchaseOrders’ onto the canvas and select the node corresponding to the table. In the context menu, select ‘Join Suggestion’.

The objects Customers, ‘ProductCategory’, ‘Products_View’ appear in the suggestions list since a relationship had already been created between these objects and PurchaseOrders in the Entity Relationship Model. Now click ‘Customers’.

The tables PurchaseOrders and Customers will be automatically joined based on the column Customer ID since these two objects are related by Customer ID in the Entity Relationship Model. Hence, the activity of Data Modeling will become simpler if the relationship between the entities are already designed in the ER Model.

An interesting point to be noted here is that the Entity Relationship Models cannot be consumed in Stories whereas it is solely built to understand how different entities from multiple sources are related to one another to provide meaningful business insights.

Entity Relationship Modeling is expected to play a major part in the Design Phase of SDLC in SAP DataWarehouse Cloud Projects. For more details on the product please visit this link.