March Madness on Demand

 

March Madness on Demand

One of the best things about my job is that I get the opportunity to jump in and lend a hand to other teams who are trying to accomplish big things and need some great database technology to back them up.  Back in December I was approached by Eric Schmidt of DPE's Media and Advertising team to build a massive monitoring and telemetry system for the March Madness On Demand (MMOD) project with CBSSports.com.   This is our latest effort to drive Silverlight adoption for major media events, PR is here.

Let me see, should I build a huge web farm to collect QoS data from millions of MMOD web viewers and roll it up into a massive real-time SQL Server 2008 data warehouse that provides Operational Business Intelligence to the virtual MMOD team, and Consumer Business Intelligence capabilities to MMOD viewers.  Um, YES!

This is a similar project to the 2008 Summer Olympics, but we are taking it to the next level.  The operations team will use the data warehouse for monitoring and telemetry as we did last time.  It's like having leads attached to a million heart patients so we can detect an abnormal heart rate and treat it before it gets out of hand. 

What's new this round is analyzing usage patterns and preferences in the data warehouse and feeding it back into the player to drive a higher quality user experience.  Here's how it works.

First, the monitoring data is collected at the Silverlight 2.0 video player client and queued on 20 front-end Windows Server 2008 web servers running an IIS 7.0 / ASP.NET / Windows Communication Foundation / SQL Server Service Broker application.

Next, the messages are routed to a target Service Broker queue where they are processed and consolidated in a log table on a central SQL Server 2008 OLTP database. 

Every five minutes, a SQL Server Integration Services (SSIS) package wakes up and extracts the new log table data from the OLTP database, transforms it, and loads it into a central SQL Server 2008 Data Warehouse on a separate system to be used for operational and consumer BI.  Here's a screen shot of the SSIS package executing:

Every ten minutes, a SQL Server Reporting Services (SSRS) report gets executed using a scheduled report subscription.  The data source for the report is a Transact-SQL stored procedure which gets the top 12 videos viewed in the past 60 minutes.

Here's the code for the GetTopVideos stored procedure.  Note that it automatically switches to the top 12 videos of all time if there wasn't much activity in the last 60 minutes.

CREATE PROCEDURE [VideoMonDW].[GetTopVideoList]
(
    @LiveVideoType nvarchar(450) = N'Live Game Video'
    ,@VideoStartEventCode tinyint = 18
)
AS
BEGIN
    DECLARE @StopDate AS datetimeoffset(7);
    SELECT
        @StopDate = MAX(ServerLogTime)
    FROM
        VideoMonDW.RawLog;
    DECLARE @StartDate datetimeoffset(7) = DATEADD(minute,-16,@StopDate);
    DECLARE @Top12List AS TABLE
    (
        VideoId bigint
        ,VideoViews bigint
    );

    INSERT @Top12List
    (
        VideoId
        ,VideoViews
    )
    SELECT TOP 12
        RL.VideoId
        ,COUNT(RL.LogId)
    FROM
        VideoMonDW.RawLog RL
    WHERE
        (RL.EventCode = @VideoStartEventCode)
        AND (RL.VideoTypeId IN (SELECT VideoTypeId FROM VideoMonDW.VideoTypes WHERE VideoType <> @LiveVideoType))
        AND (RL.Created BETWEEN @StartDate AND @StopDate)
    GROUP BY
        RL.VideoId
    ORDER BY
        2 DESC;
    IF @@ROWCOUNT <= 2
    BEGIN
        -- Not much activity in the last hour so switch to top 12 list of all time
        DELETE @Top12List;
        INSERT @Top12List
        (
            VideoId
            ,VideoViews
        )
        SELECT TOP 12
            RL.VideoId
            ,COUNT(RL.LogId)
        FROM
            VideoMonDW.RawLog RL
        WHERE
            (RL.EventCode = @VideoStartEventCode)
            AND (RL.VideoTypeId IN (SELECT VideoTypeId FROM VideoMonDW.VideoTypes WHERE VideoType <> @LiveVideoType))
        GROUP BY
            RL.VideoId
        ORDER BY
            2 DESC;
    END;

    SELECT
        VI.VideoTitle AS [title]
        ,VI.VideoKey AS [guid]
        ,TL.VideoViews AS [description]
        ,VI.Created AS [pubDate]
    FROM
        @Top12List TL
            INNER JOIN VideoMonDW.Videos VI ON TL.VideoId = VI.VideoId;
    RETURN 0;
END;

The SSRS subscription renders the report as XML transformed into RSS 2.0 using a server-side XSLT transform, and drops it into our web farm which uses DFSR to replicate the update to all our web servers.  The feed looks like this:

We wanted to avoid latency issues for users who are far away from our web farm hosted at ORCS in Charlotte, NC, so we push the file to a CDN every 10 minutes and the Silverlight player picks it up from there. 

And all this for what?  To provide real-time information on user preferences in our Silverlight 2.0 HQ player.  Here's a screen shot of the "What's Hot" page which lists the top 12 game videos viewers are watching in the last 60 minutes.  The data comes from our RSS feed:

I'm heading to MIX '09 next week to talk more about this. If you are there look me up!  Check out visitmix.com for the latest from the MIX community.

rdoherty's WebLog : March Madness on Demand