Quote of the Day
The easiest way to solve a problem is to deny it exists.
— Isaac Asimov. Hmmm … Describes a lot of the goings-on in Washington DC.
Introduction
Figure 2: Requirements Flowdown Structure.
I spend much of my time writing system requirements for the company's hardware, software, and firmware staff. My system requirements are derived from user requirements that I receive from our Marketing group.
I recently needed to generate a table that showed how every user requirement generated by our Marketing Department was mapped to one or more system requirements. This table is known as a flowdown table. For all sorts of reasons, none of them good, our requirements database could not generate the report. I decided that Excel would be the appropriate tool to generate the table we required. I should note that a Python version was also developed and will likely be used in the future.
In general, every user requirement will map to multiple system requirements that are contained in multiple system documents. The mapping between user and system requirements makes it easy to see which system documents are involved in meeting each user requirement.
The report format I generated was easy to implement using Excel and its spillable range feature. For those who want to follow along, my Excel workbook is here.
Background
Definitions
- User Requirements
- Stakeholder requirements identified by Product Marketing.
- Stakeholder
- A group with a vested interest in the requirements (user and system). This includes external and internal customers (Engineering, Production, Test, etc).
- Requirement Traceabilty
- The ability to provide objective evidence that every user requirement corresponds to a lower-level requirement (system, subsystem, or test). This ensures that the product meets the stakeholder's needs.
- Coverage
- The percentage of user requirements that have corresponding lower-level requirements. Normally, a program wants 100% coverage. In fact, programs always have time limits and some user requirements may need to be deferred to follow-on development efforts.
Requirements Structure
My task is to prove that every user requirement maps to a system requirement in one or more system documents. Figure 2 illustrates our document tree. The only unusual aspect of the document tree is that we have one system requirement document for each version of the product – there always are multiple versions. This results in systems requirements documents that have substantial duplication. We have plans to reduce this duplication, but we are not ready to implement that approach yet.
Figure 2: Document Tree.
Analysis
Requirement Link Table
Figure 3 illustrates how the user requirements map to the system requirements. One user requirement can appear in multiple system requirement documents.
Figure 3: Very Small Example of A Requirement Link Table.
Requirement Table
Our requirement database can generate a table version (Figure 4) of the graph shown in Figure 3. We will convert this table into our desired format, which is shown in Figure 5.
Figure 4: Table View of Requirement Links.
Output
Figure 5 shows the output that I need to generate. In reality, there are hundreds of user requirements, thousands of system requirements in roughly 9 system documents. I think of this table as a pivot table for text data.
Figure 5: Output Generated.
Output
I solved this problem using the following Excel's spillable ranges to generate:
- list of unique user requirements (table row indices)
- list of unique system documents (table column indices)
- for each cell (which corresponds to a combination of user requirement and system document), a list of system requirements that are used to meet that user requirement in each system document.
Figure 6 shows the formulas used. Please click on the image to enlarge it. Of course, it may be easier to view in the workbook.
Figure 6: Output Implementation.
Conclusion
The traceability table has been generated using Python and Excel. My management has been more interested in using Excel because more staff is familiar with that tool. I have used the Excel tool version for two projects and it has worked well. We will now be working on formalizing a process for using the tool.