Handling Begin and End Times in SQL Server Analysis Services 2008

A common Analysis Services scenario is to handle events with a begin time and end time and then want to know what is happening at any point in time. For example, when people watch TV there is a time they turn it on and off. What broadcasters want to know is how many viewers are watching at any given time – often down to the minute level. There are many other examples:  issue tracking (opened date, closed date, assigned to, etc), flight arrival and departure. The list goes on, but we’ll stick to TV viewing.

There is a common way to handle this – chop up the events into slices at the lowest level of detail. But this can dramatically increase the amount of data. For example, someone watching TV for a couple hours generates over 100 transactions this way. Multiply this by millions of viewers over several months and concerns about handling large data volumes arise. This document discusses a slightly altered approach to handle the data scale issues. (Special thanks for Thomas Kejser contributing.)