Displaying items by tag: data transformations

Monday, 06 June 2016 01:59

Excel Requirement bridge

Enterprise Architect provides many features for requirements management. It allows team to create and update requirements, trace their solution, maintain whole requirement life cycle.

We need sometimes work with requirements “outside” the Enterprise Architect. Especially when we discuss them with customers. We can of course generate documents from RTF templates as basis for meeting, which is suitable for most cases. At the level of project management, when it comes to project scope and overall state of requirements, is better to have requirement list in form of an Excel spreadsheet. Project manager can clear report requirement status to the customer and quickly change the list according to the results of the meeting. Next step is to save changed list back to Enterprise Architect.

We tried package CSV Import/Export utility but it was not suitable for us. CSV format is prone to data errors and we needed to split some data cells into more requirement properties.

 

So we decided to write our own bridge for requirement data interchange between Excel spreadsheet and Enterprise architect repository.

Solution

Our Excel requirements template has several columns. Mapping to requirement attributes is as follows:

Column

Requirement attribute

ID

Requirement code – first part of requirement name before “-“

Hierarchy

Package path to requirement in repository

Source

Tagged value “REQ: Source”

Description

Notes

Discovered

Tagged value “REQ: Date discovered”

Type

Type

Responsibility

Author

Priority

Priority

Status

Status

The bridge is implemented as Excel addin. It is written in Visual Basic and stored in Requirements.xlam file. When you add it to Excel, new tab CCA appears in ribbon. It has two commands “to Excel” for importing requirements from Enterprise architect repository to Excel spreadsheet and “to EA” for saving requirements from Excel back to EAP.

 

Importing requirements to Excel

Bridge is written for requirements template MyProjectREQ.xlsm. Copy it into new file and open this file. Press “to Excel”.

File open dialog appears, choose your project EAP file. Program then show “Enter prefix” dialog to filter imported attributes. Enter the prefix (for example “REQ”) or leave the value empty for all requirements. Program uses standard Enterprise Architect search “Requirements”.

Sheet is filled with requirements. Notice the cell D3 – it contains EAP filename. When you run command “to Excel” again, you will be not asked for EAP file name, the file in cell will be used. The same applies for saving cells back to Enterprise Architect.

Column “J” is hidden – it contains requirement GUIDs for subsequent updates.

 

Saving requirements to Enterprise Architect

After you made changes in requirement list in your spreadsheet, you can save them directly to project EAP file. Press “to EA”.

Program shows dialog with EAP package structure. Choose your requirements root package. New requirements (requirements without GIUD) will be inserted into this package.

 

Saving starts immediately after your select the package.

 

Data transformations

In our Excel template we use ID and Description for requirement code and description. In Enterprise architect we use Requirement name and notes. Enterprise architect has an alias attribute, which can store requirement code, but it is not useful, because we have to see this code in Project browser. So we have to transform

ID

Description

REQ113

Must be recoverable quickly.

In the event of software or hardware failure the system must be able to be recovered to full operating mode within the tolerances listed below. In the event of parts of the system failing the system must be able to be run in a safe mode.

to

Name

Notes

REQ113 - Must be recoverable quickly.

Must be recoverable quickly.

In the event of software or hardware failure the system must be able to be recovered to full operating mode within the tolerances listed below. In the event of parts of the system failing the system must be able to be run in a safe mode.

 

This is done using simple VBA functions. Code is delimited by “-“ character. Requirement name is derived from first sentence in description.

 

Conclusion

This article is mentioned as other example of extending Enterprise architect. It is a real life example, we use it in our company for many projects.

If it is suitable for you, use it! Any comments are welcome!

Petr Přibyl

CCA Group a.s.

This email address is being protected from spambots. You need JavaScript enabled to view it.

Published in Community Resources