Accelerating Microsoft adCenter with Microsoft SQL Server 2008 Analysis Services

As an Internet platform for online advertising campaigns on Microsoft® Bing™, Microsoft adCenter offers customers a wide range of intelligence and analytics tools to target, reach, and engage the right audience with the right ad at the right time. A complex, multi-faceted Online Analytical Processing (OLAP) environment based on SQL Server® 2008 Analysis Services drives the adCenter intelligence solutions. This large-scale environment includes numerous data marts that consolidate information from various sources through SQL Server 2008 Integration Services. The adCenter data marts, based on SQL Server 2008 Enterprise Edition, maintain up to two terabytes of data in relational databases, equivalent to one month of Web activities at a current accumulation rate of approximately 60 to 70 gigabytes per day. The OLAP cubes built on top of these relational stores hold 13 months of data and are up to three terabytes in size.

Prior to SQL Server 2008, adCenter relied on Microsoft SQL Server 2005 as the technological foundation of its intelligence and analytics infrastructure. Among other benefits, SQL Server 2005 enabled adCenter to take full advantage of the 64-bit platform, the Unified Dimensional Model (UDM), and Multidimensional Expressions (MDX). However, it was difficult to sustain processing and query execution performance levels during peak usage as scalability became a limiting factor. At 500 users per cube, adCenter began to notice performance issues, challenging a continually growing user base. By upgrading to SQL Server 2008, adCenter was able to break through existing limitations without the need to replace server hardware or redesign infrastructure. The performance and scalability improvements are vital for keeping adCenter agile, competitive, and ready for further growth.

In comparison to its predecessor, SQL Server 2008 offers better hardware utilization, optimized cube processing, and substantially faster MDX query execution, and it promotes efficient UDM designs and code quality in analytics solutions. These are key factors that enable adCenter to increase its return on investment (ROI) in server hardware by allowing more users per server, while at the same time improving user experience and service level agreements (SLAs). For the most critical data marts, adCenter SLAs now state that 80 percent of the queries must return results in 3 seconds or less. Moreover, adCenter gained new options to track service level compliance and establish baselines for Analysis Services capacity planning. By exploiting the new monitoring capabilities in addition to Analysis Services traces, such as Management Data Warehouse (MDW) and new performance counters for Analysis Services-related system resources, adCenter can locate root causes of performance issues faster than with traces alone. This ultimately increases the productivity of OLAP developers when troubleshooting performance issues and optimizing analytics solutions.

The upgrade to SQL Server 2008 also allows adCenter to prepare for future OLAP infrastructure optimizations and relational data-warehousing technologies, such as SQL Server code-named Project "Madison." The adCenter Data Mart team has developed concrete plans to assess a load-balanced scale-out data warehouse appliance for the largest data marts and data warehouses. adCenter also plans to increase the size of the relational data stores to a massive volume of more than 150 terabytes as soon as Madison becomes available. This will allow adCenter to maintain between five and six months of Web activity data online, assuming an increase in data collection rates of one terabyte per day. For adCenter, the upgrade to SQL Server 2008 represents an important step toward the future.

The purpose of this technical white paper is to share Microsoft knowledge, experiences, and recommendations related to the architecture and design of SQL Server 2008 Analysis Services for high performance and scalability in a large-scale OLAP environment. This paper is not intended to serve as a procedural guide. Although many organizations have similar requirements, each enterprise environment also has unique needs, making it necessary to adapt the information discussed in this paper.

This white paper assumes that readers are IT professionals and technical decision makers, already familiar with Windows Server®, Active Directory®, and SQL Server. Specifically, knowledge about SQL Server 2008 Analysis Services, Integration Services, and Business Intelligence Development Studio is helpful. Detailed product information is available in the SQL Server 2008 TechCenter at http://technet.microsoft.com/en-us/sqlserver/default.aspx.