4-page Case Study - Posted 11/15/2007

Views: 1552

Rate This Evidence:

Austrian Broadcast Corporation (ORF)

Austrian Broadcasting Corporation Prepares for Euro Cup Soccer with SQL Server 2008

Austrian Broadcasting Corporation Radio & Television (ORF) has an IT group that helps its sportscasters and other reporters provide exceptional live sports coverage by providing nearly instantaneous analytics and other sports information from its sports database and related data warehouses. Looking ahead to the live coverage it will provide for the 2008 Euro Cup soccer championship games, which will be held in Austria and Switzerland, ORF wanted to ensure it had the best database infrastructure possible. After testing Microsoft® SQL Server™ 2008, ORF decided to upgrade from SQL Server 2005 even before the official release of SQL Server 2008. The organization has seen even faster data processing since upgrading, and is enjoying enhanced data management through using new SQL Server 2008 features such as Policy-based Management and Change Data Capture.

Situation

Austrian Broadcasting Corporation Radio & Television (ORF) is the Austrian national public service broadcaster. Funded from a combination of television license fees and revenue from limited on-air advertising, ORF is the dominant player in the Austrian broadcast media.

Long admired for its excellence in presenting winter sports such as downhill and Nordic skiing, ORF has used the Microsoft® Application Platform to provide its sportscasters with real-time updates and immediate background information to help present additional depth to its coverage. In June 2008, ORF will provide its viewers with live radio and television coverage of the Union of European Football Associations (UEFA) EURO 2008 football (Euro Cup soccer) championship games, which will be held in Austria and Switzerland. The Euro Cup is often described as the third most popular sporting event in the world, after the Summer Olympic Games and the soccer World Cup.

Looking ahead to broadcasting the 31 matches of UEFA’s EURO 2008, ORF was eager to enhance its IT infrastructure including a collection of data warehouses that support sport statistics and analytics. Additionally, ORF wanted to enhance its ability to encrypt data to protect operational information and to enhance its data auditing capabilities to ensure compliance with Austrian government regulations.

…using Policy-based Management in SQL Server 2008, we can ensure that the policy is followed. ...Looking at the efficiencies and time savings we'll be gaining, Policy-based Management has an enormous potential for cost savings. Claudia Schrammel Project Manager and BI Architect Austrian Broadcasting Corporation Radio & Television (ORF)

Processing speed is paramount at ORF IT because one of its top responsibilities is supporting on-air sportscasters and reporters in the field with split-second analytics to help television viewers and radio listeners better understand the significance of what is happening during live broadcasts.

The company, which had been delighted with its early adoption of SQL Server 2005 running on the Windows Server® 2003 Enterprise Edition operating system, was eager to upgrade to SQL Server 2008 to gain whatever data processing speed enhancements it could, as well as to begin benefiting from new features that would simplify overall database administration.

Solution

After evaluating a beta version of SQL Server 2008, ORF became an early adopter of the new version, deploying it on many of its most critical databases including its sports data warehouse and five other performance measurement data warehouses. The organization decided to deploy early, even before the product was officially released, in order to take advantage of a number of new features and enhancements in SQL Server 2008, including:

• Policy-based Management. ORF is using Policy-based Management to set and enforce compliance with policies for system configuration—for its internal and external database developers. ORF anticipates this new feature in SQL Server 2008 will reduce operational costs by enforcing adherence to its internal best practices.
• Change Data Capture. SQL Server 2008 introduces the Change Data Capture (CDC) feature that captures complete content of changes and maintains cross table consistency. CDC also works across schema changes. ORF is using CDC to replace its internally developed code that used triggers to capture changes.
• Advanced Data Auditing. SQL Server 2008 provides comprehensive data auditing which ORF uses to show compliance to government regulators requiring that certain events can be traced to when, where, and by whom changes were made.
• Transparent Data Encryption. SQL Server 2008 includes transparent data encryption that enables ORF to encrypt data in an entire database without the need for application code changes.
• Star Join for Data Warehouse. SQL Server 2008 provides improved query performance for common data warehouse scenarios. Star Join query optimizations reduce query response time by recognizing data warehouse join patterns, and ORF has found reduced data loading time as well.
• Hierarchy ID Data Type. SQL Server 2008 enables database applications to model tree structures in a more efficient way than previously possible.

ORF also makes extensive use of SQL Server 2008 Integration Services. Integration Services, introduced with SQL Server 2005 and enhanced for SQL Server 2008, provides a robust set of extract, transform, and load (ETL) tools.

“In our business we work with information, from all kinds of systems, that arrives in all kinds of formats,” says Gerald Schinagl, Project Manager and Systems Architect for the Sports Database at Austrian Broadcasting Corporation Radio & Television (ORF). “We work with text formats, XML formats, flat files, different relational databases, feeds from Web services, and more. We have to do a lot of data extraction, data cleansing, and manipulation before loading it into a data warehouse, and Integration Services makes the ETL we perform much easier.”

Solution execution was simplified because it was easy for ORF to upgrade its databases from SQL Server 2005 to SQL Server 2008.

“We were amazed at how easy it was to upgrade,” Schinagl says. “We have a fairly complex collection of data. We have perhaps 50,000 soccer matches in XML, including our own data type based on using SQL CLR [common language runtime] so we were expecting some issues to arise, and none did. It worked like a breeze.”

Benefits

Upgrading its database infrastructure to SQL Server 2008 is helping ORF to provide analytics and related data to its live broadcast teams even faster than it had before, which will help it to stand out in its live coverage of the 2008 Euro Cup soccer championship games. Database management has been simplified through use of the new Policy-based Management and Change Data Capture features of SQL Server 2008. ORF also is benefiting from enhanced data auditing capabilities, easier-to-deploy encryption, faster data loads using Star Join schemas in the data warehouse, and more efficient data modeling using Hierarchy ID.

SQL Server 2008 provides an excellent auditing framework that performed very well in our testing. Claudia Schrammel Project Manager and BI Architect, Austrian Broadcasting Corporation Radio & Television (ORF)

“Everything we do is driven by the need for speed,” Schinagl says. “If a ski jumper has just completed an exceptional performance, our broadcasters need to know immediately what the significance of the scores mean. We do incredibly complex analytics. We do analytics in certain areas that we believe no other sports broadcaster can match.”

All work is done, though, under tremendous time pressure.

“We must deliver our analytic results immediately or there is no value,” says Schinagl. “We have a saying that the information only has value in the moment an event happens. One minute later the information is worth half what it was before. Two minutes later it may well be worthless. This is why we continuously look for the fastest data warehousing and analytical solutions, and this is why we upgraded so quickly to SQL Server 2008.”

Easier Database Administration with Policy-based Management 
Business Intelligence (BI) is increasingly important to ORF as it deploys solutions to help it more swiftly and completely present relevant information to its sportscasters and reporters working in the field. The BI deployments have been well received, but as multiple data warehouses and supporting services were created for different uses and by different developers, ORF didn’t have a policy for naming databases, tables, cubes, SQL Server Integration Services packets, stored procedures, and other elements.

“After a while it became apparent that we needed internal guidelines for how objects should be named, how they should behave, and other basics to simplify administration and support of our infrastructure,” says Claudia Schrammel, Project Manager and BI Architect at Austrian Broadcasting Corporation Radio & Television (ORF). “We now have policy in place, and using Policy-based Management in SQL Server 2008 we can ensure that the policy is followed. This is especially valuable when working with external programmers.”

Prior to Policy-based Management, new code had to be reviewed to ensure that it complied with ORF policy.

“This used to be a big task, whenever new application code was turned in we had to walk through it to verify that all database objects complied with our policy,” Schrammel says. “Using Policy-based Management, we can check our existing databases to make sure all objects comply. And for new development work we can give contractors our set of rules and, using Policy-based Management, make sure that developers don't have a chance to create an object that doesn't comply with our rules. Looking at the efficiencies and time savings we’ll be gaining, Policy-based Management has an enormous potential for cost savings.”

SQL Server 2008 provides transparent data encryption that we can implement without having to touch application code or client computers. This makes it far easier to protect our data with encryption. Gerald Schinagl Project Manager and Systems Architect for the Sports Database, Austrian Broadcasting Corporation Radio & Television (ORF)

“We sometimes have very complex update cascades in which several tables are involved in crisscross actions,” says Schinagl. “In some of these cases our triggers don’t capture every change in the way it should be captured because of the complexity. Yet adding extra code to our triggers would slow down the system.”

The CDC feature of SQL Server 2008 eliminates the need for setting triggers to capture changes, as CDC tracks database changes asynchronously and exposes the changes through a relational interface. This enables ORF to easily track changes based on their specific requirements and consume the change data using Transact-SQL or other data access methods.

“The Change Data Capture feature gives us the information we need and frees us from the task of creating and testing triggers,” says Schinagl. “Capturing data changes using triggers and our internally developed code could be very time-consuming.”

Enhanced Data Auditing
ORF is making use of advanced data auditing capabilities built into SQL Server 2008. “Some of our databases contain highly sensitive information and we need to track exactly who changes data and when,” says Schrammel. “SQL Server 2005 had auditing capabilities, but we had to do quite a bit of our own programming to ensure we were getting the data we needed. SQL Server 2008 provides an excellent auditing framework that performed very well in our testing.”

The advanced data auditing built into SQL Server 2008 will help ORF demonstrate compliance to government regulators. “Because we are a public company, every one to two years a government organization performs IT testing against our applications to ensure we comply with security and other requirements,” says Schinagl. “This testing is easier when we can simply show them the auditing framework that Microsoft provides, and demonstrate how efficiently it works. This makes for an easier test than explaining that we’ve built 50 triggers, and this is how the triggers work. Instead we can say: ‘These are the rules that are turned on, and here is the information that they provide.’”

Easier to Deploy Encryption
Some of ORF’s databases contain proprietary business information that the organization needs to keep as secure as possible. “We already have strict limits on who can see the data, and we use SQL Server 2008 auditing to verify this,” says Schinagl. “Beyond that we have also been asked to protect the information with encryption. While SQL Server 2005 supported encryption, it required quite a bit of work on the client side to implement. SQL Server 2008 provides transparent data encryption that we can implement without having to touch application code or client computers. This makes it far easier to protect our data with encryption.”

Faster Data Loads Using Star Join
ORF is making good use of the enhanced support SQL Server 2008 provides for Star Join schemas and Star Join query optimization. A Star Join schema, which consists of a fact table and related dimensional tables, streamlines query responses against a data warehouse. Optimizations reduce query response time by recognizing data warehouse join patterns.

In addition to faster query processing, ORF has found that data loads more quickly into its data warehouses since upgrading to SQL Server 2008 with its enhanced support for Star Join schemas. “By enabling the Star Join optimization in SQL Server 2008, we saw an immediate 15 percent improvement in data loading,” says Schrammel. “We consider that a great advantage when you can get 15 percent faster data loading without having to change a line of our own code.”

More Efficient Data Modeling
ORF is enjoying the greater flexibility it has in combining data from different parent/child hierarchies within its data warehouses, using the new Hierarchy ID feature of SQL Server 2008. Hierarchy ID, with its ability to more efficiently model tree structures, enables the database to understand relationships between rows in the same table far better than with a parent/child relationship.

“Within the typical data warehouse it is easy to work with the information in a parent/child hierarchy, but it can be difficult when working with a mix of data from different hierarchical levels,” says Schinagl. “We may need to work with level 3 costing information from all of our departments, and with level 2 data from the sports side, and it used to be difficult to mix hierarchies like this for analysis. With Hierarchy ID it is much easier to work with data across hierarchy fields.”

In summary, as ORF prepares for its live broadcast coverage of the 2008 Euro Cup, it is delighted with the benefits it is enjoying since upgrading its Sports Database and related data warehouses to SQL Server 2008. The organization can provide its broadcasters with analytics and other key data even more quickly than before, and a host of new features is making it easier to manage, audit, and encrypt its stores of information.

Microsoft SQL Server 2008
SQL Server 2008 is a secure, reliable and scalable platform for business critical applications with transparent encryption, comprehensive auditing and enhanced mirroring, while reducing the time and cost of managing data with enterprise wide policies. SQL Server simplifies development of data driven applications with integration of Visual Studio® and the Microsoft .NET Framework, support for any type of data, and consistent storage from the device to the data center. SQL Server delivers the right information to all users with advanced visualizations, powerful reports and integrated analysis.

For more information about SQL Server 2008, go to: www.microsoft.com/sqlserver

For More Information
For more information about Microsoft products and services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Canada Information Centre at (877) 568-2495. Customers who are deaf or hard-of-hearing can reach Microsoft text telephone (TTY/TDD) services at (800) 892-5234 in the United States or (905) 568-9641 in Canada. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information using the World Wide Web, go to: http://www.microsoft.com/

For more information about ORF products and services, call (+43) 1 878 78 0 or visit the website at: http://www.orf.at/

This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.

Document published November 2007