Programming

Modeling Data in Excel for Use in SAP Build

Data modeling with SAP can be quite complex depending on your requirements, and Microsoft Excel can be used to design a data model and create data for a prototype in SAP Build.

 

Unless an API from the SAP API Business Hub is needed, this is the recommended approach to modeling and managing data.

 

SAP Build provides multiple sample Excel files with data models of varying degrees of complexity. These are available in the SAP Build documentation under the “Use Sample Files to Create a Data Model” section. It’s recommended to use these examples to understand more. We recommend that you delete any previously created objects and relations before completing the tasks in the rest of this blog.

 

Defining the Data Model in Excel

For a simple data model that contains a single data object with no relations, you can create a simple table in Excel with property names as a header or followed by data, as shown in the first figure. When importing this type of simple data model from Excel, SAP Build creates a single data object and uses the Excel worksheet name as the data object name. This approach doesn’t work to define multiple data objects.

 

Defining Data Model in Excel

 

Most data models contain multiple data objects and relations between the objects. The recommended approach to create this type of data model using Excel is summarized as follows:

  • Create data objects in Excel using defined table names.
  • Import the data model into SAP Build.
  • Define relations between data objects in the Data Editor.
  • Export the data model to Excel.
  • Define data relationships in the created relations tables.
  • Import the data model into SAP Build.

Let’s use this approach to create a data model which includes Employee and Department data objects and a relation between the objects.

 

Create Data Objects in Excel Using Defined Table Names

The first step is to create the basic tables with properties and data for each object as shown in the next figure. The table headers will become the property names in the data object. Although not required, it’s recommended to label the tables for reference (e.g., Employee). No specific layout is required because we’ll use the Excel table function to define discrete data object areas.

 

Create Data Objects

 

Next, each data section must be defined as a table in Excel. As shown below, first highlight the required data area (including headers), and then select TABLE under the INSERT ribbon in Excel. A small dialog appears confirming the selection. Ensure that My table has headers is checked. Click OK.

 

Defining Tables in Excel

 

After inserting the table, Excel formats the data area as a table, which provides a visual indication of the defined table. As shown in the following figure, to define the table name, first select any cell within the defined table area (the table should still be selected if you just inserted it and haven’t clicked elsewhere).

 

Under the Table Tools > Design ribbon, enter the Table Name to match the label of the data area you defined earlier (e.g., “Employee”). When finished, repeat these steps for the Department object data area.

 

Assigning Tables Based on Name
 

Import the Data Model into SAP Build

You’re now ready to import the Excel file into SAP Build. Drag and drop your Excel file from your file system into SAP Build either in the DATA pane or in the Data Editor.

 

Import Excel Data to SAP Build

Optionally, you can import the file in the Data Editor by selecting Import (from Excel) from the + menu in the toolbar.

Import Excel Data into SAP Build

 

SAP Build first validates the integrity of the data model. If any errors occur, a message is displayed at the top of the window. If the model is valid, the Data Editor is displayed with the created data model, as shown in the figure below.

 

Validating Data

 

SAP Build will attempt to automatically assign the correct field type for each property (e.g., String, Numeric, etc.). Selecting Edit Samples for any data object will display the data entered in the Excel table.

 

Define Relations between Data Objects in the Data Editor

Let’s now create a relation between the Employee and Department data objects. Select and hold the left mouse button on the circle icon at the bottom left of the Employee object, drag the arrow onto the target object, and release. The next figure displays the result (with the Department object moved for visual clarity).

 

Defining Relations
 

Export the Data Model to Excel

You can now export the model back to Excel where the relation table is automatically created. You can replace the previously created Excel file or create a new one. After exporting and opening the file, the file is updated as shown in the figure below (column widths have been adjusted for readability). Ensure that the toggle button relations with tables is selected to retain your defined relations. A few points to note:

  • Each table defined in Excel has a corresponding label (e.g., ObjectName = employee). This may overwrite your previously defined label depending on where you placed it.
  • A relation table is created for the Employee to Department data object relationship.
  • The relation table data isn’t complete.
Exporting Data Model to Excel
 

Define Data Relationships in the Created Relations Tables

In the previous step, the relation table was created but is incomplete. No data is defined for From.Employee. After a relation is created in the Data Editor, you can use EDIT SAMPLES on the SAMPLES pane to define relation data. The object data you need to edit depends on the type of relation created.

 

For our scenario, we’ll input the data in Excel. We’ll assign Employee with ID = 1 (Dwayne) to Department with ID = 1 (Sales). Because this is a relation table, only certain data can be entered. As such, data validation rules on the cells are enforced. To enter the data, click a cell in the table, and select the desired value from the dropdown, as shown in the next figure. Repeat to set Employee ID = 2 to Department ID = 2 and Employee ID = 3 to Department ID = 3. Save the file.

 

Relation Assignment
 

Import the Data Model into SAP Build

Reimport the Excel file to update the SAP Build data model.

 

It’s important to note that if you try to drag and drop the Excel file using the canvas or Import menu, SAP Build will present an error that the Employee and Department data objects already exist. To import the updated Excel file and have SAP Build update the existing model (rather than trying to add to it), you must use the IMPORT DATA function on the SAMPLES pane. Select the SAMPLES pane, and expand the IMPORT DATA section. You can now drag and drop your Excel file into this section or select IMPORT FILE.

 

As a final validation, select the Department data object in the visual designer, and select EDIT SAMPLES from the SAMPLES pane. As shown in the final figure, the relation data entered in Excel is visible and editable in the Data Editor.

 

Validating Data
 

Conclusion

It’s important to note that after importing an Excel file for data modeling with SAP Build, the data can be modified using EDIT SAMPLES from the SAMPLES pane. However, if the Excel file is reimported, any changes made using this method will be overwritten.

 

With this data in SAP Build, you’re now ready to build some application prototypes using best programming design principles.

 

Editor’s note: This post has been adapted from a section of the book SAP Build: Prototyping and Design by Dwayne DeSylvia and Jonathan Yagos.

Recommendation

SAP Build: Prototyping and Design
SAP Build: Prototyping and Design

Before the application comes the prototype. With this guide to SAP Build, learn how to develop detailed, interactive prototypes of SAP Fiori and SAPUI5 applications. Enhance mockups by conducting user research, and collaborating on design projects. Request and implement feedback from end users and prepare your prototype to be turned into a fully-functional application. If you Build it, they will come.

Learn More
SAP PRESS
by SAP PRESS

SAP PRESS is the world's leading SAP publisher, with books on ABAP, SAP S/4HANA, SAP C/4HANA, intelligent technologies, SAP Cloud Platform, and more!

Comments