Microsoft MBI Case Study

Decentrix chose Microsoft BI technology to implement their BIAnalytix™ product line - an advanced Media Industry Business Intelligence solution.

The overall design of an open but powerful business intelligence solution for the Media Industry required exceptional tools unique attributes for the key components:

1. A scalable database technology for the large volume of data warehouse transactions.
2. A powerful but easy to use ETL data transformation tool to transform the staging data for the data warehouse.
3. A highly functional but flexible OLAP technology to allow very fast enquiry and drilling.
4. An easy to use interactive analysis tool and report design tool to distribute the analysis through an intranet portal.

To evaluate the best available technology it was first necessary to define the required functionality for media analysis as well as the operational goals for the BIAnalytix solution.

Based on the extensive experience of the Decentrix team in the Media Industry, we focused on the challenging elements required for a powerful media business intelligence solution that addressed the complexity of the industry workflow. In summary:

• Media business pacing analysis required that the granularity of the information held in the data warehouse was at a daily level so that date comparisons jumped back to the actual "state of that day". This implied large transaction volumes, rich ETL, and OLAP measure support for pacing.
• A number of many-to-many" data relationships occur in media data (Selling Periods, Calculated Demographs, etc) requiring advanced ETL transformations and support of many to many analysis in the OLAP tool.
• A large number of shared dimension attributes (> 30) over the transactional data required efficient and flexible OLAP structures to maintain fast enquiry.
• A high volume of transactions for daily processing requiring efficient ETL over a large transaction volume.
• A large multi-year historical transaction set for the data warehouse requiring efficient management of large transaction tables (> 1 billion).
• Security was required at the data level for flexible but trusted protection of subsets of the data was necessary to ensure that users only accessed authenticated data slices.

Meeting the demanding expectations of Media Groups:

Other operational goals were set for the product to meet the demanding expectations of media groups:

• The system is supplied as an open system where the client’s staff can maintain and enhance the business intelligence of the system to meet their strategic goals and priorities. This required that the development tools were easy to learn and use to enhance the system by both DBAs and BAs.
• The software license costs for the BI tools must be low and scalable for both smaller operations and large corporations.
• The resulting system performance in both processing nightly transactions and supporting the volume of users had to be very scalable without changing the solution architecture.
• The media industry data source systems are composed of many different data technologies. It is essential to have a flexible ETL tool that could directly retrieve data from many of these different sources.

Keys To a Successful BI Solution for Media:

The technology requirements, that ultimately lead Decentrix to select the Microsoft BI tools, that were key to the success of building a sophisticated Data Warehouse and Business Intelligence solution for Media can be summarized as:

• Database Technology
  In choosing the database technology, the above criteria required an industry standard SQL based solution that was proven for large transaction volumes and performance scalability at reasonable cost. Microsoft SQL Server met those criteria.
• ETL Technology
  To meet the criteria for ETL, an easy to use and maintain data transformation tool was required that was also highly functional for the hundreds of complicated data transformations required. Microsoft SQL Server Integration Services had the easy to use and learn graphical data flow features and was included as part of the SQL Server license.
• OLAP Technology
  The requirements for the OLAP technology were the most demanding. The functional demands for Many to Many support as well as the calculation support for Pacing calculations required an advanced tool. Responsive performance was also required of this technology when there were many rich dimension attributes and many users. Microsoft SQL Server Analysis Services stood out as the leader in this demanding set of requirements.
• Analysis Technology
  An interactive analysis tool was required that could also be a report design tool. It had to be easy to use but also display the advanced analysis supported by the OLAP relationships. Microsoft Excel was chosen for this technology as much for the existing familiarity by most Client users. Complimenting this design tool was the Excel Services support of the Microsoft Office SharePoint Server which supported the publication and dissemination of the analysis. The strong security authentication provided by the Windows Active Directory network logon allowed transparent security both to the SharePoint portal sites as well as the data security of the cube.