Equipment Utilization by Hour

[#1]

Hi,

I am looking to determine the utilization of equipment by hour. I have created hour buckets and can use IntervalMatch to get the sum of the SecondsUsed based on start time, but what do I do if the SecondsUsed runs over into the next bucket? Are buckets the wrong approach for this? My main goal is to determine the busiest periods based on utilization. A bonus would be to determine how often all 4 machines are in use...

Take, for example, the first row from my data below:

StartTimestamp: 2015-11-01 00:59:51.000

SecondsUsed: 24

ItemID: 2

Therefore the usage for that one record should be split between two buckets:

Bucket: 1

StartTime: 2015-11-01 00:00:00

EndTime: 2015-11-01 00:59:59

Usage: 9 seconds

Bucket: 2

StartTime: 2015-11-01 01:00:00

EndTime: 2015-11-01 01:59:59

Usage: 15 seconds

Here is an example of the raw utilization data that I have:

Fact:

LOAD * INLINE [

    StartTimestamp, SecondsUsed, ItemID

    2015-11-01 00:59:51.000, 24, 2

    2015-11-01 00:47:40.000, 24, 3

    2015-11-01 00:49:49.000, 30, 1

    2015-11-01 12:50:19.000, 31, 0

    2015-11-02 00:51:12.000, 30, 3

    2015-11-02 12:51:53.000, 30, 1

    2015-11-02 16:06:32.000, 28, 2

];

Please let me know if any more details would be helpful.

Thanks in advance for any and all help.

[sunny_talwar]

Not sure how the data is getting divided into two buckets. How did you derive 9 seconds and 15 seconds?

[#3]

Sorry Sunny,

I did make a mistake in my buckets which may have led to confusion. I have fixed the time frame for bucket 2 above.

Here's why the seconds need to be split into 2 buckets:

Item 2 was put put in use on 2015-11-01 at 00:59:51 and used for 24 seconds, therefore ending in the next hour at 01:00:15.

My goal is to determine how much of the possible time per hour Item 1 was used. I can't put the entire 24 seconds in Bucket 1, as the item was only in use for 9 seconds in that hour. I can't put the entire 24 seconds into Bucket 2 as it was only in use for 15 seconds of that hour. So I need to put the first 9 seconds in Bucket 1 and the remainder into Bucket 2.

Thanks for your help!

[Anonymous]

IntervalMatch() and time buckets would be the way I would go.

I have done something similar for comparing staff rostered hours vs. staff actual hours.

Each staff member has various dimensions such as Department and Location so I can select and slice 'n dice with them.  I can show count of staff rostered [or actually on site]  per time bucket - in my case 15 minute buckets and display on a Line Chart.

Sounds like you need your [Machine ID] as a dimension.

[#5]

Thanks Bill. The [ItemID] field in my data is the machine's id.

I agree that buckets and an IntervalMatch would work if everything fit nicely into one bucket or the other, but my question is what do I do with those records that span multiple buckets? It seems like a simple problem, but it's got me stumped.

Thanks again for the help.

[sunny_talwar]

May be this:

Fact:

LOAD StartTimestamp,

  SecondsUsed,

  ItemID,

  Interval(If(StartEndHour - StartTimestamp >= SecondsUsed/(24*60*60), SecondsUsed/(24*60*60), StartEndHour - StartTimestamp), 's') as Bucket1,

  Interval(If(EndTimestamp - StartEndHour <= 0, 0, EndTimestamp - StartEndHour), 's') as Bucket2;

LOAD *,

  TimeStamp(StartTimestamp + SecondsUsed/(24*60*60)) as EndTimestamp,

  TimeStamp(Ceil(StartTimestamp, (1/(24*60)))) as StartEndHour;

LOAD * INLINE [

    StartTimestamp, SecondsUsed, ItemID

    2015-11-01 00:59:51.000, 24, 2

    2015-11-01 00:47:40.000, 24, 3

    2015-11-01 00:49:49.000, 30, 1

    2015-11-01 12:50:19.000, 31, 0

    2015-11-02 00:51:12.000, 30, 3

    2015-11-02 12:51:53.000, 30, 1

    2015-11-02 16:06:32.000, 28, 2

];

[ElizaF]

Hi,

One solution is to create a Master Time Calendar and with IntervalMatch function to count the number of seconds of the equipment's utilization on hour.

More information about Master Time Calendar you can find below blog:

The Master Time Table

You can find in the attached file my approach for this.

Hope this helps.

P.S. Reload the file. I removed all the values because of the file's size.

[#8]

Thanks Sunny!

This was very promising, until I found out that some tasks span more than 2 buckets. This was not reflected in the test data i was given and presented here, so not a reflection on your solution at all. I think I would need to build a large number of Buckets with conditional statements like this:

Interval(If(EndTimestamp - StartEndHour <= 0, 0, if(EndTimestamp - StartEndHour > 3600, 3600, EndTimestamp - StartEndHour), 's') as Bucket2,

Interval(If(EndTimestamp - StartEndHour <= 0, 0, if(EndTimestamp - StartEndHour > 7200, 3600, EndTimestamp - StartEndHour-3600), 's') as Bucket3,

and so on...

I appreciate the help, but I think I need to find a way to handle N buckets.

[sunny_talwar]

That's understandable and I agree that you will have to decide how you want the buckets to be created. We can guide you only to the extent of what is provided to us

But I hope you have a way to move forward from here

[#10 · Accepted solution]

Jonathan, please check the below script. I am simply creating new row if End Time stamp is in different hour.

// More Buckests purpose I took Secs as Mins

TempFactData:

LOAD

  *,

  Timestamp(StartTimestamp + (MinsUsed/(24*60))) AS EndTimeStamp

;

LOAD * INLINE [

    StartTimestamp, MinsUsed, ItemID

    2015-11-01 00:59:51, 24, 2

    2015-11-01 00:47:40, 24, 3

    2015-11-01 00:49:49, 30, 1

    2015-11-01 12:50:19, 181, 0

    2015-11-02 00:51:12, 30, 3

    2015-11-02 12:51:53, 30, 1

    2015-11-02 16:06:32, 28, 2

];

FactData:

LOAD

  ItemID,

// /*

  StartTimestamp,

  EndTimeStamp,

  MinsUsed,

  CounterFlag,

// */

  DateHour,

  IF(CounterFlag=1, ceil(((DateHour+(1/24))-StartTimestamp)*24*60*60),

  IF(Hour(DateHour)=Hour(EndTimeStamp) AND Floor(EndTimeStamp)=Floor(DateHour) , ceil((EndTimeStamp-DateHour)*24*60*60) ,3600)

  )  AS SecsUsedPerHr

;

LOAD

  ItemID,

  StartTimestamp,

  EndTimeStamp,

  MinsUsed,

  IterNo() AS CounterFlag,

  Floor(StartTimestamp) + ((Hour(StartTimestamp)+IterNo()-1)/24) AS DateHour

Resident TempFactData

While IterNo() <= ((Floor(EndTimeStamp)-Floor(StartTimestamp))*24) + Hour(EndTimeStamp) - Hour(StartTimestamp) + 1

;

DROP Table TempFactData;