Events in Progress Date & Time

[m1nkeh]

Almost all the content online, as well as built in functions of PowerPivot/Dax/PowerBI etc. cater for date calculations that stop at day level.. I.e. YTD, QTD, MTD

I have a requirement to model event activity down to the minute, and it is absolutely driving me around the bend - most notably where the activity "crosses" midnight..

I need to answer the following:

• Number of starting events @ time x - DONE
• Number of ending events @ time x - DONE
• Number of events in progress @ time x - ONLY AT DATE

Ultimately the time will probably be reported hourly, or perhaps in 15-min segments - but the grain of the data is by second.

These events are very small, and fast... some might only last for a few seconds, and others a maximum of 3 or 4 minutes.

I have attached a workbook, with a simplified version of my model.. it contains a separate Date, and time dimension as well as a 'Fact' with a series of events - I have this model doing what it needs to do with regards to the Date upon which something happened... however, going below that level i am a bit stumped.

My thinking right now is that somehow i need to crossjoin the date and time dimension when analysing below the date level.. i need to give time some context as something i was getting yesterday was where 1am (for example) would be replicated upon all dates going into the future which obviously isn't right..

Initially, I'm just looking to get this working, however i know Marco / Alberto wrote a great paper on optimising Events in progress and my dataset is pretty large (many tens of thousands of events per hour) this is a concern!

Attached workbook: https://onedrive.live.com/redir?resid=6239C024A73A77F7!20866&authkey=!ACj9oxB3IxATkoo

Thanks!

 

[ImkeF]

What kind of interactivity shall this report have?
Do you want to filter on certain attributes (that you haven't shown so far ) or is it "just" about creating a chart like this that will only be refreshed regularly?

 

[Matt Allington]

There is no 1 correct way to do this. The easiest (I think) would be to reshape your data table so it contains a single date:time column and a "duration" column. But this would compress very poorly and the calendar table would be very large (assuming you need seconds).

i haven't had a good look at your data (not easy on an iPad) but I thought you may find this post interesting.

Peak Detection: A Surprising Usage of PowerPivot - PowerPivotPro

not it the same, but an interesting time based example.

 

[m1nkeh]

What kind of interactivity shall this report have?
Do you want to filter on certain attributes (that you haven't shown so far ) or is it "just" about creating a chart like this that will only be refreshed regularly?

hey, thanks for the reply, yes there is other dimensionality not shown in this example... things like Event name, and customer for example. Ultimately these also need to be able to filter the results of duration. For example "show me the average duration of events for customers that were born in London", nothing too extravagant

There is no 1 correct way to do this. The easiest (I think) would be to reshape your data table so it contains a single date:time column and a "duration" column. But this would compress very poorly and the calendar table would be very large (assuming you need seconds).

i haven't had a good look at your data (not easy on an iPad) but I thought you may find this post interesting.

Peak Detection: A Surprising Usage of PowerPivot - PowerPivotPro

not it the same, but an interesting time based example.

hi matt, thanks for the reply - i am currently contemplating creating a Year >>> Minute dimension table, but yes, this would compress horribly but I'm not very certain what the correct conceptual approach here should be. As i stated, i think in my mind i need to try to "attach" the time that things are happening to the date upon which they happen maybe with CROSSJOIN() but then i am basically producing the huge table that we're talking about at run-time, which sounds even worse.

Can get away with no having seconds, but there are some events that literally last 1 or 2 minutes, and this would hide them considerably. Seconds is the ideal.

I would massively appreciate it if you could take a look at the data and come back with any suggestions, at this point I'm just after another brain to potentially suggest "have you thought about this"

 

[m1nkeh]

something i just thought about though was that there are other aspects to my model that actually only should consider "date", and i actually want them to only deal with DimDate...

now i'm think i need a DimDate, and a DimDateTime, hmm..

as ever, i think the detail here is in the DAX code i am failing to visualise in my head :|

 

[ImkeF]

Supporting Matt's suggestion.
Had a look at your data model and think that you can use Power Query to reduce your time-granularity down to hour, as you seem to be reporting on hour only - or is this not correct?

 

[Matt Allington]

These problems do my head in. And you can spend a lot of time trying to solve them - but I guess you know that already! Here are some thoughts (not, necessarily solutions).
1. I am thinking maybe you should not join the data table to the date table. When you do this, the date table will make it hard for the instance where events cross over days. I wrote an article about a similar (not the same) problem here Fill Table with Last Survey Result - Excelerator BI
2. I am pretty sure I would transform the data on load. I would capture start date, start time, duration, and I would capture a flag that indicates if the event extends into the next day. Easier to do this during ETL than in DAX. Put the effort into making the data work for you rather than you having to work for the data.
3. I think you need to make a call on how granular your reporting needs to be (the report, not the data). You will need to write your DAX to iterate over this time horizon and "count" the open events. If you choose 15 mins, then it implies that there could be events that start and finish between the time blocks. You need to be clear how you will handle this. I would then consider loading a time table at this level of granularity (not second). Probably don't join this table either (same as 1). You would then need to iterate over this table and work out how to filter the data table so it goes back in time to find events that started in the past that are still running for every point in time.

The problem with this problem is that it is the "thinking and design" that IS the work. And that will take some R&D. It could take 30 mins, it could take 8 hours - I don't know. If you can't solve it, you could consider getting some professional help to resolve the problem. I guess it depends how you go and how quickly you need the problem solved.

 

[Matt Allington]

Thinking over a coffee. I would do this.
1. In your data table, have date column, HH:MM:SS Column, Duration Column in HH:MM:SS
2. All of the above columns would convert to the Excel date formatting concept and hence they can be added and subtracted.
3. Set up a date calendar and a time calendar like you have. Set the time calendar to the smallest granularity you need for reporting. Don't connect to the data table
4. Write your events in progress measure to be something like this.

=calculate(countrows(datatable),
      filter(datatable,
               max(calendar[Date]) + max(time[time]) <= datatable[date] + datatable[starttime] + datatable[duration]  &&
               min(calendar[Date]) + min(time[time]) >= datatable[date] + datatable[starttime] + datatable[duration] 
      )
)

This would need some testing, debugging etc and I am not sure what performance would be like, but that is how I would approach it (to start with anyway).

 

[m1nkeh]

seem to be getting no notifications about replies to this thread - apologies!

just looked on the off chance there were more suggestions, so will digest and come back with some comments - i currently have something that works, although i feel it's a bit clunky / hacky

 

[m1nkeh]

Thinking over a coffee. I would do this.
1. In your data table, have date column, HH:MM:SS Column, Duration Column in HH:MM:SS
2. All of the above columns would convert to the Excel date formatting concept and hence they can be added and subtracted.
3. Set up a date calendar and a time calendar like you have. Set the time calendar to the smallest granularity you need for reporting. Don't connect to the data table
4. Write your events in progress measure to be something like this.

=calculate(countrows(datatable),
      filter(datatable,
               max(calendar[Date]) + max(time[time]) <= datatable[date] + datatable[starttime] + datatable[duration]  &&
               min(calendar[Date]) + min(time[time]) >= datatable[date] + datatable[starttime] + datatable[duration] 
      )
)

This would need some testing, debugging etc and I am not sure what performance would be like, but that is how I would approach it (to start with anyway).

@Matt - I have implemented this now, and when scaling up to a large-ish dataset, the server ran out of RAM :/

*sigh*

thinking that i need to filter NOT on the DataTable to be honest, as it is very very very large...