Solved

Working with time in workspace

  • 28 January 2021
  • 4 replies
  • 74 views

Hello all.

I am trying to find a way how to work with time in workspace. I have timestamps in my source data, which I would like to use in the analytics e.g. to measure how long an event lasted, how long events of some type lasted on average, show number of events on timeline etc. I have not found a way how to do that. Could anybody advice?

Thank you.

Ell

icon

Best answer by Martin Burian 29 January 2021, 07:33

Hello Elly.

It is very good question. If you have timestamps in your source data, it is possible to use it in analytics, but usually it means some preparations in ETL.

By timestamp I understand Date and Time or Epoch time. You cannot directly use it for slicing by hour > minute > second, you have to prepare the time dimension:

Time dimension

It will allow you to slice by the time, but think about if you need second granularity. If not you can just extract hour and add it directly into the fact dataset:

See the “Call Start Hour”

How to calculate the duration? You have 2 options, calculate it in your ETL and have duration as fact or you can add start time epoch and end time epoch as facts and then use just subtract start from end and you will get duration in seconds. Then you can calculate averages etc.

Couple of examples of reports:

Simple count by hour with filter to date and call type (incoming/outgoing)
Call duration from start and end epoch

Result of the calculation is seconds, so how come there are minutes and seconds. It is because of the time metric format template which is possible to choose in format. It looks as follows:

[>=86400]{{{86400||0d}}} {{{3600|24|00}}}h;
[>=3600]{{{3600|24|00}}}h {{{60|60|00}}}m;
[>=60]{{{60|60|00}}}m {{{|60.|00}}}s;
[>0]{{{|60.|00.0}}}s;
[=0]{{{|60.|0}}}

Clever isn't it?

Do not forget that there is the MAQL which allows you to create metrics exactly according your needs.

Last but not least there is one more case and it is that you can have start and end in different records in dataset. Imagine for example some logs. You have start and end of the event in different records. Model could looks as follows:

How to calculate duration and average now? MAQL!


To sum it up, there is no direct support of time dimension in GoodData as there is for dates, but flexibility GoodData provides allow you to create your own time dimension. You can use epochs as facts and calculate duration of events. Duration is also possible to calculate in ETL, if you do not have it in your data.

Hope it helped.

Martin

View original

4 replies

Userlevel 1

Custom time dimensions can be set up in your project as described here on our help portal: 

https://help.gooddata.com/doc/en/building-on-gooddata-platform/data-modeling-in-gooddata/data-modeling-using-the-cloudconnect-tool/learning-data-modeling-in-cloudconnect/working-with-ldm-objects-in-cloudconnect/using-a-custom-time-dimension-in-a-logical-data-model

 

 

Thank you. Now I can slice by the time attributes, but how to do calculations. How to take “Ended HH:MM:SS” and subtract “Started HH:MM:SS” to calculate duration?

Userlevel 1

Working with times in this sense can be a bit tricky. However, you can start by finding MIN/MAX of your time attributes, or even creating Start/End times to make use of the date arithmetic within MAQL. 

Userlevel 1

Hello Elly.

It is very good question. If you have timestamps in your source data, it is possible to use it in analytics, but usually it means some preparations in ETL.

By timestamp I understand Date and Time or Epoch time. You cannot directly use it for slicing by hour > minute > second, you have to prepare the time dimension:

Time dimension

It will allow you to slice by the time, but think about if you need second granularity. If not you can just extract hour and add it directly into the fact dataset:

See the “Call Start Hour”

How to calculate the duration? You have 2 options, calculate it in your ETL and have duration as fact or you can add start time epoch and end time epoch as facts and then use just subtract start from end and you will get duration in seconds. Then you can calculate averages etc.

Couple of examples of reports:

Simple count by hour with filter to date and call type (incoming/outgoing)
Call duration from start and end epoch

Result of the calculation is seconds, so how come there are minutes and seconds. It is because of the time metric format template which is possible to choose in format. It looks as follows:

[>=86400]{{{86400||0d}}} {{{3600|24|00}}}h;
[>=3600]{{{3600|24|00}}}h {{{60|60|00}}}m;
[>=60]{{{60|60|00}}}m {{{|60.|00}}}s;
[>0]{{{|60.|00.0}}}s;
[=0]{{{|60.|0}}}

Clever isn't it?

Do not forget that there is the MAQL which allows you to create metrics exactly according your needs.

Last but not least there is one more case and it is that you can have start and end in different records in dataset. Imagine for example some logs. You have start and end of the event in different records. Model could looks as follows:

How to calculate duration and average now? MAQL!


To sum it up, there is no direct support of time dimension in GoodData as there is for dates, but flexibility GoodData provides allow you to create your own time dimension. You can use epochs as facts and calculate duration of events. Duration is also possible to calculate in ETL, if you do not have it in your data.

Hope it helped.

Martin

Reply