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:
I have also referenced Mosha's blog articles on: "counting-in-flight-events" and "Dynamic named sets" respectively on:
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:
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
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"
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
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
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.
asked Jan 30 '12 at 02:31 AM in Default