HowToCreateKPI

How to Create a KPI for the BSC

Plandora allow creating key indicators for management purposes. Each counter is performed daily by Plandora and the results can be accessed by the projects stakeholders though a BSC table. This feature was implemented since version 0.8.9. To create a new KPI, enter into Plandora using the user 'root' (only root is allowed to create KPIs), then, click the link 'KPIs Form (Key Performance Indicator)'.

1. First of all, try to plan about what kind of information or measure do you want to collect and each projects those metrics will be applied.

2. Choose a name for the KPI. This name will be used as a label into the BSC table for each project

3. Select the type of KPI (daily, weekly and monthly). This information is used by Plandora Timer to decide whether the metric should be performed or not.

4. Select the perspective which best fit your KPI. The perspective list follows the BSC standards.

5. Select the project related to the KPI.

6. Select the execution hour that the KPI should be performed by the Plandora Timer.

7. Write the SQL query to get the information of KPI. In the end of this article there are queries "ready for use" that can be copied for tests or even to be applied for your project.

8. Select the result type. You have to choose the type of value that your query is returning. For example: a numeric value, a date value, etc. This field is used by Plandora to show appropriately the values on the GUI.

9. ?#PROJECT_ID# - Return the current id of project related with the KPI. It can be useful if you are duplicating the same query for many projects.

10. ?#INITIAL_RANGE# - Return the initial date and time based on the type of query. For example, if the type if 'Diary', this key word will return the current date and 00h:00m:00s. You suppose to use this key word if you implemented a KPI that collect data using a period of time that changes according to the Timer execution.

11. ?#FINAL_RANGE# - Like the previous key word, return the final date and time based on the type of query. For example, if the type if 'Diary', this key word will return the current date and 23h:59m:59s.

12. ?#USER_ID# - Return the current user id currently signed into the tool.

13. The picture bellow there is a example. The red circle shows some key words that can be optionally used to improve the query.

• After that, it is import to make sure that the Plandora Timer is turned-on. Open the web.xml file in {YOUR_TOMCAT_HOME}\webapps\pandora\WEB-INF\ and check if the property TIMER contain the value ON. If you change the value, don't forget to restart Tomcat.

• Open again the Plandora and connect using a Leader username and password. Make sure the "My Project" list contain projects with the BSC icon (like the picture bellow).

• Click into the icon related to the project that you created the KPI previously. The new metric should be displayed into the list of the appropriate perspective. However, the new counter will be displayed only after the Plandora Timer has had populated values in it.

---

The list bellow contain a list of queries that can be used for your project or to test the Plandora BSC feature.

Feel free to change or to suggest new metrics.

Actual Partial Cost of Project

    select sum((rt.actual_time/60) * r.cost_per_hour) as value
    from resource_task rt, resource r
    where rt.resource_id = r.id
      and rt.project_id = r.project_id
      and rt.project_id=?#PROJECT_ID#
      and rt.actual_time is NOT NULL

Estimated Partial Cost of Project

    select sum((rt.estimated_time/60) * r.cost_per_hour) as value
    from resource_task rt, resource r
    where rt.resource_id = r.id
    and rt.project_id = r.project_id
    and rt.project_id=?#PROJECT_ID#

Relationship between estimated time and actual time of all tasks

    select (sum(sub.yield) / count(sub.yield)) as value
    from (
    select (estimated_time/actual_time) as yield, year(actual_date) as year,
           month(actual_date) as month, day(actual_date) as day
    from resource_task
    where project_id=?#PROJECT_ID#
      and actual_date is NOT NULL
      and actual_time is NOT NULL
      and actual_time > 0
    group by year, month, day
    ) as sub

Number of accumulated extra hours of resources

    select count(sub.total) as value from (
    select sum(rt.actual_time) as total, year(rt.actual_date) as year,
    month(rt.actual_date) as month, day(rt.actual_date) as day,
    rt.resource_id , r.capacity_per_day
    from resource_task rt, resource r
    where rt.project_id=?#PROJECT_ID#
    and rt.actual_date is NOT NULL
    and rt.resource_id = r.id
    and rt.project_id = r.project_id
    and rt.actual_time is NOT NULL
    group by year, month, day, rt.resource_id
    ) as sub
    where sub.total > sub.capacity_per_day

Daily Number of Open Requests (except Reject and Cancel)

    select count(r.id) as value
    from requeriment r, requeriment_status rs, planning p
    where r.requeriment_status_id = rs.id
      and r.id = p.id
      and r.project_id=?#PROJECT_ID#
      and (rs.state_machine_order <> 200 and rs.state_machine_order <> 202)
      and p.creation_date >= ?#INITIAL_RANGE#
      and p.creation_date < ?#FINAL_RANGE#