Chunk data according to timestamp for correct aggregation

**Problem**

Hi all,
I have following data

Start End State
7:30 8:30 80
8:30 8:40 90
8:40 9:05 100
9:05 10:10 110

I am doing aggregations over hours so I would need to split the data like this
Start End State
7:30 **8:00** 80
**8:00** 8:30 80
8:30 8:40 90
8:40 **9:00** 100
**9:00** 9:05 100
9:05 **10:00** 110
**10:00** 10:10 110

I can transform the data first with some ETL tool or with SQL server directly, but I would have to chunk data to the smallest granularity, which can change over time, so I would have to transform data again first. A then the data replicates pretty quickly.

I would like to this on the fly when loading data or handling them in cube..
Any ideas in proper way of doing this or am I getting it wrong?

:slightly_smiling_face:

Thanks in advance