SSAS Point-in-Time Count

In an SSAS project i am currently working on, i am trying to perform a point-in-time count. my ultimate goal is to create a line-chart in SSRS to show the basic trend in the numbers of customers viewing TV for each hour of any given day, based on data available over 24 hours for the day. The idea is to track Receiver-Box activty.

Briefly, i have a table of customer Receiver-Box TV-Viewing Activity/Events with a begin-time and end-time for a pair of begin & end dates, and i am trying to determine how many viewers/customers are watching TV per hour over a 24 hour time-span. The trouble i am having is that the count appears to be a bit like a running-value count, and i havent figured out how to get the viewer-count per hour broken-down over a typical 24-hour day. In any given hour a viewer may start and stop their viewing several times. which would generate multiple ReceiverBox transactions/sessions for that user in the space of an hour , so the Total TV-viewers count for any given hour-interval is the delta of the active users still watching minus those who stopped watching at the end of the hour. There might be hours without any recorded tv-viewing activity as well, but that is only of importance for the end-chart phase.

I referenced this olap-article on: [Handling Begin & End Times in SSAS 2008], written by Microsoft's "Richard Tkachuk" at this address:
[<http://www.sqlserverdatamining.com/OLAPPapers/Handling%20Begin%20and%20End%20Times%20in%20SQL%20Server%20Analysis%20Services%202008.htm>],

as it goes to the heart of what i am trying to accomplish in ssas 2008.

I have also referenced Mosha's blog articles on: "counting-in-flight-events" and "Dynamic named sets" respectively on:
[<http://sqlblog.com/blogs/mosha/archive/2007/06/01/counting-in-flight-events-in-mdx.aspx>]
[<http://sqlblog.com/blogs/mosha/archive/2007/08/25/mdx-in-katmai-dynamic-named-sets.aspx>]

However,i didnt quite understand how to apply the material to handle my situation . In short , i am currently stuck on how the "Interval ID" key is assigned to each record with a begin & end time (vis-a-vis the implementation of Richards approach), and specifically, on which sql statement is required to generate the new Receiver-Box Events fact table.

I have endeavored to set-up the set of relevant tables suggested in the article; in a way i believe they appear to work similarly to the concept of interval lookup-tables much like R-Tree's in MySQL ie: [www.sqlite.org/rtree.html]

Here is what i have accomplished so far:
Minutes_FACT

-id

-Time_Id
-minute_Id
-interval_5
-interval_10
-interval_30
an example of the data in the Minutes_FACT table:

id  Time_Id  minute_Id  interval_5  interval_10  interval_30

1   0    00:00   00:00   00:00   00:00
...
30   29    00:29   00:25   00:20   00:00
...
59   58    00:58   00:55   00:50   00:30
60   59    00:59   00:55   00:50   00:30
61   100  01:00   01:00   01:00   01:00
...
105  144  01:44   01:40   01:40   01:30
...
345   544  05:44   05:40   05:40   05:30
...
1440  2359 23:59   23:55   23:50   23:30

ReceiverBoxEvents_FACT (original)

- Box-ID

- User-ID
- Start-Date
- Start-Time
- End-Date
- End-Time
- Box-Function-TypeID
an example of the data in the ReceiverBoxEvents_FACT table:

Box-ID User-ID Start-Date Start-Time End-Date End-Time Box-Function-TypeID

34 1234 24-7-2011 14:00 24-7-2011 15:05 (2) ie: "Pay-Per-View"
34 1234 24-7-2011 17:07 24-7-2011 20:10 (3) ie: "Digital-Radio"
34 1234 24-7-2011 20:00 24-7-2011 21:05 (4) ie: "Recorded-Video"
37 8327 24-7-2011 07:00 24-7-2011 18:06 (4) ie: "Recorded-Video"
56 1256 24-7-2011 06:30 24-7-2011 08:34 (2) ie: "Pay-Per-View"
58 3333 24-7-2011 00:00 24-7-2011 00:05 (2) ie: "Pay-Per-View"

Minute-Interval_DIM

- Row_ID

- Interval_ID
- Minute_ID
- Interval_Name
- MinuteInterval_Name

an example of the data in the Minute-Interval_DIM table: (Note that the Interval_ID is generated from the calculation of the duration between the end-minute and start-minute, + 60 so the interval-id is always a number between 0-59 )

Row_ID Interval_ID Minute_ID Interval_Name MinuteInterval_Name

  1            0          0             00-00         0000
  2            1          1             00-01         0001
  3            2          2             00-02         0002 
  ..
  59           58         58            00-58         0058
  60           59         59            00-59         0059
  61           59         100           01-00         0100
  62           0          101           01-01         0101
  63           01         102           01-02         0102
  ..
  120          58         159           01-59         0159
  ..
  3600         0          5959          59-59         5959             

Intervals_DIM

- Interval_ID

- Start_Minute
- End_Minute

an example of the data in the Interval_DIM table: (Note that the start and end Minute is simply the 2 digit minute value stripped of the Start-Time and End-Time taken from the rows from the ReceiverBoxEvents table )

Interval_ID Start_Minute End_Minute

     ?         00 ie:(14:00)      05 ie:(15:05)
     ?         07 ie:(17:07)      10 ie:(20:10)
     ?         00 ie:(20:00)      05 ie:(21:05)
     ?         00 ie:(07:00)      06 ie:(18:06)
     ?         30 ie:(06:30)      34 ie:(08:34)
     ?         00 ie:(00:00)      05 ie:(00:05)

I would like to check if my understanding of the Interval_ID column, in my implementation of the Minute-Interval_DIM Table is correct. and that my implementation of the Interval_DIM table column data is accurate.

At present, i am also unsure of the sql method to be used to correctly assign an Interval_ID to each begin and end-time record within the ReceiverBoxEvents table

Is there an easier way to accomplish this task other than using the many-to-many pattern ie: is it possible using an MDX-calculation say or by associating a column: (Count + 1), and(Count -1) with the Begin and End Times respectively in the ReceiverBox-Events table?

I'm a bit lost, Please Help.