Data modeling to define productivity metrics

2951778

I have a requirement to calculate productivity in our issue tracking software(Jira). Idea is I want to capture the data as an issue progresses through diff development stages (like audit trail of the issue for specific field in it).

Now I want to build a data model that enables me to capture metrics like avg. amount of time it took for issues to move between 2 stages (for eg, In Progress > UAT). Avg. time for each developer etc.

Above audit trail view would give me data in this format

<span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">Audit ID </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">|</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> Issue ID</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">|</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> Developer </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">|</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> Issue-stage</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">|</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> Data_Update_dt <br/>A001 </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">|</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">101</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">|</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> D01 </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">|</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">In</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> Progress</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">|</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">31</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">-</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">May-17 </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">00</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">:</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">25</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">:</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">00</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> <br/>A002 </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">|</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">101</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">|</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> D01 </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">|</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> UAT </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">|</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">31</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">-</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">May-17 </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">06</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">:</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">25</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">:</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">00</span>

I am trying to understand the design to calculate difference between A002 and A001, time reqd to move from In progress to UAT. What is the best way to do it.

Please advice.

Sherry George

I think what you need is an accumulating snapshot fact table. You can read a little bit about it here.

http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techn…

2951778

This is definitely something I am looking for. Thanks for sharing!

I guess the model can be designed in lines of SCD Type 2 dim, for each change in status, new record will be inserted into the table. Old record will be end dated which gives us the time for how long the ticket stays in a particular stage(start time - end time) of development. This ll be my fact to calculate metrics around duration/productivity.

Thomas Dodds

in addition to @Sherry George's link ... here's an example of what Kimball describes ...

Scroll down to Accumulating Snapshot ...

Facts about Facts: Organizing Fact Tables in Data Warehouse Systems

Key to remember:  ONE ROW represents ONE INSTANCE of the process.