Getting your Excel data into Relatics

Exchanging data via Excel files is very popular given its flexibility, and during your project you will most likely obtain data from an external source (e.g. an external application or another party) in an Excel file. All the separate files make for an entangled web of data, where it’s hard to get a good overview of the project, this is where Relatics comes in. To gain full insight into the complexity of the project, the data from the Excel file(s) needs to be transformed and made available in the Relatics workspace. This provides a central location where project data is managed, where project members can view and edit the data extensively. This article provides tools for entering data from an Excel file into Relatics.

Analyze the Excel file

As a first step, you need to analyze the data inside the Excel file. Does the data fit the scope of the project? To what extent is the data already part of the Relatics workspace? Is the data in the Excel file up to date? The answers to these questions help you to validate if the data is suitable to be made part of the Relatics workspace.

After you have validated the data, it is time to check the underlying model of the data in the Excel file. You must find out what the main Systems Engineering (SE) model objects are (e.g. requirements, system objects, activities, verifications, interfaces, etc.). Further, you must understand how the SE model objects are interrelated. Once you have a clear picture of the underlying model, you must map it to the SE model of the Relatics workspace. If the models do not align and you want to make the data available in the Relatics workspace, you must first update the SE model of the Relatics workspace to be able to map the data.

Finally, you need to analyze how the data is structured in the Excel file. You need to optimize the structure of the Excel file if needed. Things you should at least check in the Excel file:

• Each sheet in the Excel file must present data on a primary SE model object. For example, in a sheet that primarily contains requirements, each row should present data for a single requirement.
• Each value in the Excel file which can be mapped to a property from Relatics, should be part of a separate cell in the Excel file. For example, for the property ‘Status’ of a requirement, each cell should only contain a single value.

Example of well-structured data in an Excel file that is optimized for copying and pasting into Relatics:

Align the data of the Excel file with the SE model from the Relatics workspace

After you have analyzed the data inside the Excel file, you need to make sure that the quality of the data matches the criteria to be pasted and stored in Relatics. For example, properties in Relatics are assigned a datatype. The datatype has implications for the allowed values and the number of characters that can be stored in Relatics. Further, data from the Excel file needs to be formatted in such a way that it can be pasted into the Relatics workspace.

Properties

Relations

Copy and paste the data from the Excel file into the Relatics workspace

After you have ensured that the quality of the data from the Excel file matches the criteria to be pasted and stored in Relatics, you can perform the action of copying and pasting the data from the Excel file to Relatics. It is advised to use the following steps:

Step 1. For each type element in Relatics, paste the list of elements from the Excel file into Relatics including all the properties.

• Use a my view or a use case. For each column in the Excel file that you have mapped to the SE model in Relatics, use the model selector to select the properties for the my view or the use case. The result is a table that shows all the properties for the corresponding type element for which you want to paste the data from the Excel file.
• Rearrange the order of the columns in the Excel file to match the order of the columns in the Relatics table.
• Copy the data from the Excel file and paste the data into the Relatics table.
• Repeat these steps until all the elements and properties are stored in Relatics.

Example of pasting elements into Relatics:

Step 2 For each type relation in Relatics, paste the list of relations from the Excel file into Relatics including all the relations.

The step of pasting relations into Relatics is similar to the step of pasting elements with properties into Relatics (see step 1). It is important to perform the step of pasting relations separately at the end, as only values for relations can be pasted into Relatics, of which the elements already exist in Relatics.

Example of pasting relations into Relatics:

Check the result

After you have pasted the data from the Excel file into the Relatics workspace, you must check the quality of the pasted data. Does the Relatics workspace contain all the data from the Excel file? It is wise to perform the following checks:

• For each type element and for each type relation in the Relatics workspace, look at the number of elements and compare it to the total number of unique values of the corresponding SE model object in the Excel file.
• Perform random testing of property values in the Relatics workspace against the Excel file to check whether the data is complete.