Greetings,
I am instrumenting a server farm. I would like to plot the instantaneous server load (in jobs), broken out by server (color-coded) and stacked. Please refer to the attached workbook for the full, unambiguous picture. I will follow with a rough description to frame the issue.
In this workbook, I am assuming a farm with four servers, and jobs begin and end on five-second boundaries. In the real system, there may be hundreds of servers. Jobs are recorded as beginning and ending on one-second boundaries, but may be logged at a higher time resolution in the future.
Here is a shot of the best depiction I have so far:
My native data sources are Jobs and ServerHistory. These are generated by the system and are readily available.
Jobs contains Job ID (key), Start Time, End Time, and Server ID.
ServerHistory contains Server History ID (key), Timestamp, Server ID, and Number of Jobs. Note that ServerHistory could be derived from Jobs. Solutions that use only the Jobs table would be interesting, and probably useful for a larger class of problems than ServerHistory-based solutions.
I have derived two more types of table, ServerPivot and ServerTransaction; both derived from ServerHistory. There are two variants of each table. The second variant of each collapses simultaneous records where possible for shorter tables.
ServerPivot contains Server History ID (key), Timestamp, Number of Jobs Server 1, Server 2, Server 3, Server 4. It is expensive to generate and its schema depends on the number of servers in the farm (which could change).
ServerTransaction provides the delta number of jobs on the implicated server.
Here is a brief example:
ServerHistory:
SH_ID, Timestamp, ServerID, NumJobs
1, Time1, 1, 2
2, Time2, 4, 3
3, Time3, 1, 0
4, Time4, 4, 0
ServerPivot:
SH_ID, Timestamp, Jobs1, Jobs2, Jobs3, Jobs4
1, Time1, 2, 0, 0, 0
2, Time2, 2, 0, 0, 3
3, Time3, 0, 0, 0, 3
4, Time4, 0, 0, 0, 0
ServerTransaction:
SH_ID, Timestamp, ServerID, NumJobsDelta
1, Time1, 1, 2
2, Time2, 4, 3
3, Time3, 1, -2
4, Time4, 4, -3
I have nine worksheets in the attached workbook. The first three are to show the underlying data, and the next six are various attempts to generate the desired chart (instantaneous historical server load).
While this problem resembles many of those posted regarding start and stop times, I believe this is a little different. Instead of plotting arrivals and departures for one entity, I have many. I am also attempting to use continuous time, and my time intervals are relatively short with a combination of collisions and near-collisions.
Please have a look at the workbook and let me know if you have any insights regarding making a better multi-resource utilization worksheet.
Thanks,
-tom
I hope I understand your request correctly.
Here is my approach.
Duplicate Jobs data source to capture start and end separately.
Create calculate field for both data source
under jobs(start)
[datetimne]
[T Start]
Run_sum(count)
running_sum(countd([J Id]))
under jobs(end)
[datetimne]
[T End]
Run_sum(count)
running_sum(countd([J Id]))
Make relationships only with serverHist and jobs (start)/jobs(end) with using "datetime" and "S Id"
(same thing for jobs (start))
Under serverHist data source,
create calculated field.
[area]
zn([jobs (start)].[Run_sum(count)])-zn([jobs (end)].[Run_sum(count)])
I'm afraid though that handling "Zero" between each Brock may be not appropriate??
Thanks,
Shin
9.3 attached.
