Is Time Duration a Measure or Hierarchy - If measure how do I get make it a Measure

#1

I have in my data a time duration. The original format is not human readable.


https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/dbo-sysjobhistory-transact-sql?view=sql-server-2017


Specially column: run_duration int Elapsed time in the execution of the job or step in HHMMSS format. (there are other tables with this same time duration)


If I leave it as and Integer it will be a Measure, but not readable.


I have this in a view and a function that coverts it to 00:00:00 (HH:MM:SS) but by doing that it is no longer a Measure.



How do I get it to be a measure and in a normal time readable format?


I am charting and reporting on how our SQL Agent jobs and SSIS packages are performing/running.

One of the things that we are looking at is how long did this job as a whole take and how long did each step (yes I have the set duration too) take.



Is this possible currently or should this be a feature request, to treat time durations (that look like possible just time of day as a measure not a Hierarchy).

Duration to time - format axis of graph
#2

I believe a similar case was discussed under this post: https://dundas.influitive.com/discussions/topics/67396?page=1


The solution (detailed in the post) is to keep it as a measure and use formula measures at the metric set level (so post the aggregations) to format it to be readable.

#3

That solution has the amount of time in a decimal format.

My amount of time is an Integer in HHMMSS format. NOT the amount of seconds it took so you can do the x / 60 get mins the agian to do hours.


here is a sample:

say the process took 1 hour 23 minutes and 5 seconds

the stored interger is 12305


another

say it took 2 minutes and 1 seocond

the stored interger is 201


I am not kidding look at the actuall[msdb].[dbo].[sysjobhistory] table column run_duration


Your quess is as good or better than mine as to why MicroSoft stores time spans this way.


I was just hoping for a function in the datacube to convert string time span into some sort of measure that had the same format. I realized now that a much larger task than i had given thought at the time of asking (will need Microsoft to add a data type and that not going to happen).


So it is possible with that post (but it is a lot of converting back a forth because mine is stored in the weirest possible format ever(thanks MicroSoft)) and thanks for showing me that. Hopefully I will actually not need it as a measure I was just wonder if it would be easy to make it one.

#4

In that case you can use the same concept with other formulas to get the hours, minutes and seconds values. I successfully tried the following formulas to get the numbers out of the whole number.

For example, the formula to get the number of digits you have in your number would be:

(int)Math.Floor(Math.Log10($YourHHMMSSMeasure$) + 1);


Then the formula to get the first 2 digits would be:

(int)Math.Truncate(($YourHHMMSSMeasure$ / Math.Pow(10, $NumOfDigitsFormula$ - 2)));


if you wrap that in an if and else statement you should be able to get the values you need for each time section (hour/minute/second) and then show it in a readable way on your dashboard.


Image title