none
Ranking response times for Fire Department

    Question

  • Hello,

    I'm in the middle of powerpivot pro's online course, but I'm looking for a little feedback to see if I'm on the right track with a project. I work for a fire department and we collect the response times of our units. As you can guess, we have goals we measure against and I'm hoping powerpivot will be a good choice to work with. Simply put, for each incident (fire, medical) I create a response time using a calculated column in powerpivot window subtracting the arrival time from the dispatch time (format HH:MM:SS). I am populating the workbook with a database connection to our SQL database. I group the incidents using the unique incident number in a pivot table, so it list every unit that was on the run. My idea would be to create a measure listing each unit's response time, looking for the minimum time and compare the minimum against our goal. The result returns either true or false. 

    I"m working on the solution, but I'm just wondering if my idea sound plausible using powerpivot?

    I'm not looking for anyone to do my work, but looking for any suggestions to push me in the right direction.

    Thank you for looking , Brent

    Friday, July 25, 2014 2:03 AM

Answers

  • I agree that with so little data and such a simple requirement, Power Pivot may not be necessary.  However, since you brought it up though, I just wanted to express a couple thoughts about the approach to use in a scenario where Power Pivot is used.  If the objective is to calculate a time duration between a start time and end time, you should calculate a numeric value rather than a time.  If you try to express this as a date/time type value, it would have to be relative to a specific date which gets messy.  It's just better to calculate the number of minutes or seconds that have elapsed between two date/time values.  I'd also recommend using a measure instead of a calculated column to save storage and it would work better in aggregate if you were to roll these values up across groups.  The calculated duration could be relative to a target value so the resulting differential values might be -3 for three minutes under the target or 5 for five minutes over.  You could also use a KPI to express good/acceptable/bad values in a scorecard fashion.  You mentioned that you wanted to rank response times.  If you do need to list response times in ranked order, you can create another measure that uses the duration calculated measure or expression within the RANKX function.


    Paul Turley, MVP, www.SQLServerBIBlog.com *Please vote if helpful*


    Friday, July 25, 2014 3:56 AM
    Moderator

All replies

  • PowerPivot will work for your requirement. Though it might be a little overkill but your approach is more elegant compared to just creating a field the incidents response time and then creating a field that calculates the "True" or "False" field.

    I recommend that you use a stored procedure (assuming you are using SQL Server as data source). That seems to perform much faster than directly connecting to a table.

    Friday, July 25, 2014 2:18 AM
  • I agree that with so little data and such a simple requirement, Power Pivot may not be necessary.  However, since you brought it up though, I just wanted to express a couple thoughts about the approach to use in a scenario where Power Pivot is used.  If the objective is to calculate a time duration between a start time and end time, you should calculate a numeric value rather than a time.  If you try to express this as a date/time type value, it would have to be relative to a specific date which gets messy.  It's just better to calculate the number of minutes or seconds that have elapsed between two date/time values.  I'd also recommend using a measure instead of a calculated column to save storage and it would work better in aggregate if you were to roll these values up across groups.  The calculated duration could be relative to a target value so the resulting differential values might be -3 for three minutes under the target or 5 for five minutes over.  You could also use a KPI to express good/acceptable/bad values in a scorecard fashion.  You mentioned that you wanted to rank response times.  If you do need to list response times in ranked order, you can create another measure that uses the duration calculated measure or expression within the RANKX function.


    Paul Turley, MVP, www.SQLServerBIBlog.com *Please vote if helpful*


    Friday, July 25, 2014 3:56 AM
    Moderator
  • I will check into creating a stored procedure. Thank you, Brent
    Friday, July 25, 2014 4:11 AM
  • Sir,

    I was using the rank simply to find the minimum time, but I guess that would be unimportant for this task if I incorporate the minimum function. Additionally, we count the number of units on the scene, so I though it might be  helpful for that also. I will take your advice on creating the measure instead of the column. I just read a posting concerning your time comment and it makes sense. I did try the RANKX function, but it was trying to rank the entire column, just not the group. I'll give it another run.  I have some work to do figuring it all out, but it is nice to know it is possible. 

    We have over 50,000 incidents per year with multiple units, so I'll be working with several hundred thousand rows in all. I'm going to head over to your blog and check it out. Thank for taking the time to answer my question. 

    I'm still learning SQL server, so I thought this solution might be a bit easier for me. 

    Thanks, 

    Brent


    Friday, July 25, 2014 4:21 AM
  • It sounds like you may be heading in the right direction using Power Pivot then.  It can take some work to get RANKX to behave the way you want so while you're checking out blogs, take a look at this one:
    http://www.powerpivotpro.com/tag/rankx/

    Paul Turley, MVP, www.SQLServerBIBlog.com *Please vote if helpful*

    Friday, July 25, 2014 4:51 AM
    Moderator
  • Hello,

    I have worked on a few measures, although I keep getting an error when I attempt to create my response time as a measure instead of a calculated column. I'm not ready to give up trying to figure it out and post a question quite yet. I read the RANKX post with powerpivot pro and several of your presentations! Once I make a good try at it and really get lost, I'll post a few more questions. 

    Thanks!

    Saturday, July 26, 2014 3:44 AM
  • If you'd like to share a simplified copy of your model, I can take a look at it.

    Paul Turley, MVP, www.SQLServerBIBlog.com *Please vote if helpful*

    Saturday, July 26, 2014 3:47 PM
    Moderator
  • I'm not sure if  this is the best way, but I've attached my file. I'm still working through quite a bit of the concepts, but it is slowly making sense as I watch more of Rob's video's and reading the chapter's in his book. 

    Thanks

    https://www.dropbox.com/s/vbs873rbbxhgwql/Workbook%20with%20calculated%20columns%20-%20Response%20Times%20using%20Measures.xlsx

    Tuesday, July 29, 2014 1:44 AM
  • Bent, I have a copy of your workbook file.  I've looked over your measure calculations and the pivot table you created.  The more I peel back the layers, the more questions I have but I think I have the essentials.  I think there are some opportunities to streamline the modelling which could make this easier.

    Some observations:

    I see that the Calendar Table is not related to any other tables.  To be able to use time intelligence functions or data part slicers, I think you should be using date lookup tables rather than storing the date parts in the other tables.  Since your incidentdate, arrivaldate and dispatchdate columns actually store date and time values, these don't match the DateKey in the date table.  In my brief prototyping, I changed the name of the incidentdate column to [Incident Date Time] and then created a calculated column named IncidentDate as "=DATE(YEAR([Incident Date Time]), MONTH([Incident Date Time]), DAY([Incident Date Time]))".  I made two more copies of the date lookup table and named these tables 'Incident Date', 'Arrival Date' and 'Dispatch Date' with the DateKey in each table related to the three respective calculated date columns.  I also deleted all the date part columns in the apparatus and incident tables in lieu of using the related date lookup tables.

    The apparatus table appears to be the fact table which I interpret as the "apparatus response".  Ideally, I think that there should be an apparatus or equipment lookup table related by the apperatuskey or apperatusid.  However, this is probably immaterial to get the calculations working.

    I'll need to understand where you are seeing errors.  I didn't encounter an error in any of the measures but I'm not sure about the accuracy calculated results.   I think we'll just need to look at a specific reporting scenario.

    I had to convert this model to Excel 2013.  If you have a copy, I can send this back to you.  Otherwise, we'll just need to compare notes.


    Paul Turley, MVP, www.SQLServerBIBlog.com *Please vote if helpful*

    Thursday, July 31, 2014 5:35 AM
    Moderator
  • Hello Paul, 

         The calendar table was something I just put in because of a lesson I had with Rob's course, so I was in the processing of adding it to the project as a means of reinforcing the lesson. I will take another look at our SQL database, but in the database, the incident and apparatus tables are primarily linked by the incident key. I will double check to see if the apparatuses in the table have another relationship with a look up table and I can change the data model. Initially, I was looking for the simplest model to start with and then build from there. 

          As far as the error, the min function is working correctly to find the minimum time of the incident, but I think my confusion comes with the grouping of the rows. As you can probably tell, there is one incident key for each fire call and many apparatus are assigned to the call with the incident key as the primary relationship. If I add all the incidents to the pivot table, it correctly identifies each incident as being met or not met. But, if I remove the incident keys from the pivot and just keep the months, the pivot table correctly filters down to the month and find the minimum of the entire month and I loose the details of each incident's value of met or not met. Thank to Rob, I understand what is happening, but not how to fix it (I'm finishing up the calendar table now and the X factors are next, which will probably help me with this issue).

    The reporting for now, is pretty simplistic. It is being able to identify each incident, if it met the goal and then able to report out of the incidents how many we made and how many we missed. If we had 1,000 calls and we made 500 of them, we would be at 50%. The goal is a simple true or false based on the minimum time of the first arriving unit ( I used 200 seconds for this project). 

    I do have Excel 2013, so I can open the file. I created the original at home with 2010. 

    I hope that make sense.  Thank you for the help. I have the day off and I'm working through Rob's video course most of the day today.  Thanks, Brent

    Thursday, July 31, 2014 1:51 PM
  • Hi Paul,

    This is probably not the best solution, but after today's videos I thought of a way (I think) to capture what I am after. I think I already see pit falls in the way I'm attempting, but everyone has to start somewhere.

    Basically, I used a a calculated column in my Incident Table to filter the Met Objective Measure to return the Boolean value for just that row. I used =Calculate(apparatus[met objective]).

    Then I created a new pivot table (Cal Col Pivot) and created =count rows(incident) to give me a count of the unique incident numbers. I can see that it won't be the permanent solution, but it does reinforce the lessons of the day. I'll move in the X factors (as he calls them) over the weekend, so I guessing there will be other, better  ways. 

    Thanks

    https://www.dropbox.com/s/jl1cx2fv76u23qm/Workbook%20with%20calculated%20columns%20-%20Response%20Times%20using%20Measures%20ver%202.xlsx 

    Thursday, July 31, 2014 6:22 PM