TV and Radio Case Study

BIAnalytix™ provides corporate wide analytic needs for a large Television and Radio station group.

This Television group has a respected history of analyzing their commercial revenue using in-house developed management reports that were essential for critical business decision making. By 2007, an imminent upgrade of the operational traffic system for over 30 television stations, and additional sophistication of the requested analysis, dictated a fourth redevelopment of the Oracle data mart used for most of this analysis.

The in-house project team reviewed the functionality of the Decentrix BIAnalytix™, a Media data warehouse and Business Intelligence system, (MBI™) and decided to install this open data warehouse solution for a number of reasons:

• BIAnalytix™ was based on the latest BI technology from Microsoft-SQL Server 2005, Integration Services 2005, Analysis Services 2005, and Microsoft Office SharePoint Services 2007.
• The BIAnalytix™ components were open, allowing unrestricted in-house development, and the installation included training their internal team to leverage this open design.
• The functionality of the BIAnalytix™ Media Base module already included most of the new requirements and had a number of advanced analysis options beyond the scope of the initial project.
• A number of required custom enhancements were able to be specified and included in the installation project plan.
• The in-house project team respected the Decentrix consulting team's lengthy media technology background, extensive industry experience and their past media project successes.
• The installation of the proven BIAnalytix™ system was determined to be a much faster, lower cost and lower risk project than a new design and development project.

Project Tasks:

Considerable care was taken, with input from Decentrix, to secure an experienced DBA in the Microsoft technology suite to compliment the in-house team's business development experience. Development began on a new extraction from the Oracle traffic system source for the data loading to the BIAnalytix™ Staging schema. In parallel, another extraction was developed to directly load the BIAnalytix™ data warehouse with all the history from the existing Oracle data mart. During this phase of the project, Decentrix also completed the custom enhancements to the BIAnalytix™ data warehouse.

Next, the enhanced BIAnalytix™ system was installed to allow extensive testing of the traffic extraction, the history extraction, and the custom enhancements. The system was then thoroughly tested, including advanced analysis with MOSS 2007 using Excel Services. Production Servers were commissioned from the IT database services team in parallel with this development testing and the tested system was deployed to these production servers by the in-house team who took over all operational and enhancement responsibility. The source of the SSIS projects, the SSAS cube projects, and the schema were under full Source Control using the Team Edition of Visual Studio Development Studio with Decentrix consultants integrating enhancements with the DBA enhancements.

Project Considerations:

Reconciliation of the new data warehouse was performed automatically by comparison of reconciliation cube reports to the existing data mart reports and all discrepancies tracked down and corrected for both history data loaded as well as the new nightly extract from traffic. With up to two (2) million extracted transactions every nightly load, performance was always a requirement because all transactions had to be processed and then all cubes had to be incrementally processed before the use by business users each morning.

With the successful completion of these production goals for television data, it was decided to extend the BIAnalytix™ data warehouse to load the commercial data from over 100 radio stations. As radio stations were converted to use the in-house Traffic system, the extraction program was used to load this data nightly to extend the powerful analysis implemented for television to be available for Radio Management across the group. Additional business data sources are planned for inclusion in the integrated data warehouse. Web revenue from station web sites, Nielsen audience data, Program Rights contract expenses are examples of sources where additional modules of BIAnalytix™ can be installed to extend the data warehouse for advanced integrated analysis.

Keys to Success:

The factors leading to the success of this sophisticated Data Warehouse and Business Intelligence installation can be summarized as:

• The choice of a proven package using advanced BI technology lowered the risk, lowered the cost, and shortened the project delivery timescale.
• The client reporting team was both experienced and talented and was complimented by the acquisition of an additional DBA staff member with both talent and experience with the Microsoft technology.
• The Media experience of the Decentrix consultant installation team ensured that the package and custom enhancements directly addressed the media business requirements.
• The open design of the package allowing enhancements by the in-house team ensured that the data warehouse and business intelligence system was very responsive to the requests and requirements of the business users and management.
• Extensive project experience of both the in-house and Decentrix team supported by advanced large project development practices using Team Studio ensured the project was closely managed to business delivery goals.