TDWI | Training & Research | Business Intelligence, Analytics, Big Data, Data Warehousing

kicker

MGM MIRAGE Deploys Near-Real-Time Data Warehouse

deck

summary

• January 4, 2005

This article originally appeared in What Works, Volume 12, which published in November 2001.

Commentary by Charlie Shepard, Lead Systems Engineer, MGM MIRAGE

Company Overview

MGM MIRAGE, headquartered in Las Vegas, Nevada, is a multi-billion dollar entertainment company with almost 50,000 employees. The company operates 18 casino properties—14 in North America and four on other continents—including several Las Vegas strip marquis names such as MGM Grand, The Mirage, Bellagio, New York-New York, and Treasure Island.

To service guests, MGM MIRAGE employs a large number of heterogeneous systems which vary from property to property. These include three different Hotel Reservation Systems and multiple room service and restaurant systems, casino systems, slots systems, and so on. To manage these systems, the company maintains an IT staff of over 200 people. Infrastructure issues are addressed by an eight-person systems engineering group—responsible for hardware and architecture decisions across all of the company’s systems.

Business Challenge

MGM MIRAGE wanted to better understand its customers, and use this information to drive business decisions leading to improved customer satisfaction and increased revenues. To do this, they needed to pull together information from across the enterprise, capturing all customer behavior in a single location regardless of which systems were used to service each customer or the specific properties they visited. “Across our domestic operations alone, we have 17 operating systems and 192 applications,” said Charlie Shepard, lead systems engineer for MGM MIRAGE. “The average customer staying at one of our properties generates dozens of transactions per day, resulting in hundreds of millions of transactions per year across all our properties. To capture a three-year history of customer behavior, we needed to pull together an extremely large amount of data.”

Solution

To centralize data on customer behavior and use this information to make better business decisions, MGM MIRAGE deployed a near-real-time data warehouse and analysis solution using Windows® 2000 Advanced Server and SQL Server 2000 Enterprise Edition. The MGM MIRAGE Consolidated Customer Database (CCD) resides on two Dell 8450 eight-processor servers, each configured with four gigabytes RAM. External storage is provided by a Storage Area Network (SAN), with two Dell PowerVault storage cabinets providing enough capacity for three terabytes of data. Tivoli Storage Manager orchestrates backup to multiple StorageTek PowderHorn silos.

“Our CCD resides on an active-active cluster that we built using Microsoft Clustering Services,” said Shepard. “One node of the cluster manages the data warehouse, which resides in a single SQL Server 2000 database. The other node is running Epiphany, which receives updates from the data warehouse and provides views into the customer data through Epiphany’s data marts. By configuring the two servers as an active-active cluster, which is easy to do with SQL Server 2000, either node can fail and the other will take over the workload. Of course, we tested this and it worked perfectly.”

Recognizing Customer Value in Near Real Time

To achieve a solution capable of consolidating customer transactional data from across the company’s properties in near real time, MGM MIRAGE turned to Las Vegas-based Systems Research & Development (SRD). “SRD’s near-real-time data warehouse product provides MGM MIRAGE with the ability to instantly recognize customer value across all its entertainment properties,” said Shepard. “Most data warehousing systems are batch-oriented, with data updated on a periodic basis, which means that the best these systems can do is deliver yesterday’s answers to today’s questions. Using SRD’s solution, built on SQL Server 2000, our data warehouse is current to within seconds of its contributing operational systems. This gives MGM MIRAGE the ability to deliver extraordinary new levels of customer service.”

Data from MGM MIRAGE’s operational systems is acquired and shipped to the central datacenter by the Central Messaging Engine (CME), a component of the SRD product that uses Extensible Markup Language (XML) and Microsoft Message Queuing Services (MSMQ) to route customer transaction data to the data warehouse in a standardized format. “Acquisition nodes at each property running services written in C++ extract data from each operational system, convert it to a standardized XML format, and place it onto a message queue for transmission to the datacenter,” said Jeff Jonas, president and founder of SRD. “When the record is received at the datacenter, it gets passed through several other nodes that prepare and cleanse the data prior to insertion in the data warehouse. The beauty of using MSMQ between each node is that each stage of the acquisition and cleansing process can be separately scaled as needed, with one node feeding many or vice-versa. We convert the data to XML at the beginning of the process because XML enables us to represent any type of customer transaction using one standardized message format. This makes the downstream processes much easier to manage. As an added benefit, because MSMQ is so tightly integrated with Windows 2000, tools like Performance Monitor can be used to monitor the health of their system with no additional work or costs.”

At the datacenter, a receiving node serializes each record, logs a copy, and passes it on to an address standardization node. The record then passes through a data quality management node before reaching the match/merge node, which looks at the cleansed data, decides if the record represents a new or existing customer, and stores the cleansed information in the data warehouse. “The data warehouse resembles a highly normalized Online Transaction Processing (OLTP) environment,” said Jonas. “It’s designed to move massive amounts of data, similar to how a physical warehouse manages hard goods. Once all MGM MIRAGE properties are online, the data warehouse will be over 300 gigabytes in size and will be processing 700 megabytes of new raw data every 24 hours, representing one million daily customer transactions. The existing eight-way server running SQL Server 2000 can handle twice this volume, meaning that MGM MIRAGE can meet their data warehousing needs for another four years without any additional investments. Because SQL Server 2000 is so easy to manage, the amount of time a single DBA has to spend to support the data warehouse is only one hour per week.”

Why MGM MIRAGE Chose SQL Server 2000

MGM MIRAGE selected SQL Server 2000 because it provided better price-performance and manageability than other database solutions. “By selecting SQL Server 2000, we were able to deploy a data warehouse capable of handling terabytes of customer data,” said Shepard. “Had we used Solaris and Oracle, our costs would have been much higher—the Microsoft platform provides a much better price-performance ratio. Our decision to use SQL Server 2000 permits us to leverage our existing in-house expertise, which will result in lower maintenance and support costs. There are more third-party solutions for the Microsoft platform, so we will always be able to find the products and services needed to extend the functionality of our data warehouse. As for scalability, we can always move up to Windows 2000 Datacenter Server, which will enable four-way clusters, up to 32 processors per server, and support for up to 64 gigabytes RAM.”