Peppermill Gets Fast Results with IBM i Data Warehouse

May 8, 2012 Alex Woodie

There is a stream of thought among business intelligence software vendors that says the IBM i server is a lousy place to put a data warehouse. But that bias didn’t sway the IT folks at Peppermill Resorts, a Reno, Nevada-based chain of casino resorts that recently went live on an IBM i-based data warehouse that has resulted in much faster data extracts, much fresher data for marketing analysis, and significantly speedier production systems.

Peppermill Resorts is the largest privately held casino group in Nevada. The company runs six casinos (five with hotels) in Reno, Sparks, Henderson, and Wendover. In addition to gambling and hotels, the 5,000-employee company offers other amenities, such as high-end restaurants, spas, wedding chapels, and convention space.

Supporting this large, multi-property operation is a collection of IBM i applications commonly used in the casino industry, including the casino management system (CMS) from Bally Technologies, the lodging management system (LMS) from Agilysys, the Stratton Warren System (SWS) purchasing system from Agilysys, and Infinium financials from Infor. Critical non-IBM i applications include the SpaSoft package from PAR Technology and restaurant and point-of-sale (POS) software from MICROS Systems, among others.

Effective marketing is a very important aspect of Peppermill’s business, as it is for all consumer-oriented businesses. The company tasks its marketing group with knowing–to the greatest extent possible–who its customers are, when they’re likely to visit a property, what they like to do, and how much they might spend. Based on this information–which is largely stored in the applications and their underlying databases–the marketing team crafts targeted campaigns aimed at enticing guests back to the resorts. It sounds simple enough, but the challenge, of course, lies in the details.

The Case for a Data Warehouse

With such a diverse array of applications and databases, just getting to the data in a timely manner poses problems. In Peppermill’s case, the marketing team made extensive use of Microsoft Access to extract raw data from production systems and hammer it down into usable summaries. This proved to be a less-than-ideal approach, for several reasons.

For starters, using Access on laptops was just plain slow. The process of extracting 200 million rows from the CMS database and then summarizing it could take up to a week. “Our database marketing team would take a long time to crunch the numbers,” Peppermill IT director Jeff Lew told IT Jungle. “They indicated it took them a week to crunch the numbers to get to a result. A week’s a long time to be crunching numbers.”

The extracts also caused performance problems with Peppermill’s live production databases. Because these were ad-hoc requests, Lew’s team couldn’t write specialized indexes to speed the query, and the result would be slower response time for other IBM i applications, including kiosks and credit card authorizations.

“It’s important to senior management that we don’t have people standing in line for long periods of time trying to check in,” said Greg Larsen, a systems architect with Peppermill. “Since all that stuff happens on the iSeries, when they launch those massive queries that would just consume ungodly amounts of resources. It just ripples through the property, and their phones start ringing.”

A built-in limitation with Microsoft Access was also part of the problem. “Access can only deal with tables that have 32 or fewer indexes,” Larsen continued. “So we actually had to delete some of our indexes because Access couldn’t deal with it. We were limiting what we can do on iSeries because of the limitations on Access.”

These are all clear indications that Peppermill was in need of a centralized data warehouse. Lew and his team of IBM i administrators, who joined Peppermill in February 2010, had all worked together previously at another large Nevada casino, which ran IBM i applications and a Unix-based data warehouse. The experience with that data warehouse would prove beneficial to the selection process for Lew and his team at Peppermill.

The DW Selection Process

Once the decision was made to implement a data warehouse, Lew’s team researched the best way to make that happen. In previous jobs, the Peppermill IT team members had experience with data warehouses running on IBM i and Unix. Lew also researched various Windows options.

Poor performance with a multi-million-dollar Unix-based data warehouse at a previous employer left a bad impression on Larsen. “We had to throttle back the iSeries application because they were choking [the Unix-based data warehouse],” he says. “They could not take the transactions as fast as the iSeries could put it out there, so they had to dumb down all the iSeries applications because they were choking the data warehouse.”

The desire not to be dependent on consultants was also a factor. “The applications are all iSeries-based so we will work on it,” said Buck Paiva, a systems architect with Peppermill’s IT department. “We’re very familiar with the database, so not only would there be a learning curve for the product, but the consultants who came in to do this wouldn’t know anything about the database, they would have to come to us anyway.”

The IBM i server is often portrayed as a relic–especially by vendors of Windows and Unix business intelligence solutions. But the Peppermill IT team wasn’t influenced by any of that. “Their salespeople have done a pretty good job at painting the iSeries as old technology,” Larsen said. “The green screen really hurt us. They see those and they say, ‘Oh, my God. That’s old technology!’ But it’s not. The green screen is old technology. But iSeries hardware is state-of-the-art. People just don’t get that.”

In the end, Peppermill selected RODIN, a native IBM i extract, transform, and load (ETL) tool from Coglin Mill. Some of Lew’s team members had worked with RODIN at a previous employer, and found it to be “robust” offering. It was also considerably less expensive than the Windows-based alternatives that Lew had looked at. “Our technology expertise is in the iSeries,” Lew said. “From a financial standpoint, we already had a box and some seasoned programmers that would be able to address any needs that users had if we went with an iSeries solution.”

In addition to the RODIN ETL tools, Peppermill selected the SEQUEL reporting tools from Help/Systems which is also an IBM i-based application. This combination allows Peppermill’s IT staff to do all the heavy lifting and manipulation of data on a powerful System i server, and present the database marketing team with neatly summarized tables of fresh data, which that team manipulates on the laptop with a SEQUEL GUI.

The DW in Action

Speed is the biggest benefit of Peppermill’s data warehouse. For starters, now that queries are pointed at a dedicated data warehouse box, the production Power 720 no longer gets bogged down when presented with a large SQL statement, so production applications run faster. “Now that we have offloaded that onto the data warehouse, we get just almost stunning performance in our production environment,” Paiva said.

RODIN itself is also quite fast with its ETL processes. The tool is able to extract 200 million rows of data from the backup Power 720 in about 30 minutes, compared to several days using Access running on laptops. “It puts the burden of data processing back where it belongs–on the big hardware versus the PCs,” Paiva said. “Plus it’s backed up every night. We have all the normal IT safeguards protecting the data warehouse.”

That speed translates into fresher data, which helps the marketing group make better, more informed decisions. The data warehouse is refreshed nightly, so the data is only one day old, versus more than a week old when the system was based on Access. “We’re able to get to the data much quicker, and we’re able to free up the database marketing team to do other tasks,” Lew said.

The database marketing group is quite impressed. “They love the fact that we have summarized data for them, and how quickly that it’s available,” Paiva said. “And even when our users change their minds on what they want some of their data sets to look like, rather than it be a week-long process to make the change, it’s literally just maybe an hour or two to change the data sets, change the extract program, recompile everything, and re-implement. It’s amazing how fast we can rebuild.”

Peppermill’s executives also see the value in having a data warehouse. “I’ve had conversations with senior management, and they’ve been extremely pleased with RODIN and SEQUEL,” Lew said. “They’ve had previous experience with implementations of data warehouses being Windows-based and they had indicated it took a year or two years to get it done, and millions of dollars, and still not getting what they wanted out of it, whereas we were able to get this up and running within weeks and providing some valuable data.”

The roll-out of the data warehouse and reporting tools went smoothly, but there were some small bumps in the road. The biggest concern was the way that Peppermill stored data–in separate database libraries running on the same IBM i server. Coglin Mill didn’t support this setup initially, but the vendor modified the product to adapt to Peppermill’s unique needs. The modification took a couple of days, according to Peppermill. Coglin Mill also adapted RODIN to support a database that’s used by one of the non-IBM i applications; Peppermill has yet to roll that out into production, but was impressed by the vendor’s responsiveness.

Despite the challenges, the implementation is ahead of schedule, Lew said. The data warehouse rollout is also under budget, thanks to the recently announced partnership between Coglin Mill and Help/Systems, which saved Peppermill some money on license fees. The cost of the IBM i software is significantly less than what the Windows option would have cost, Larsen said.

Currently, Peppermill is primarily using RODIN and SEQUEL to analyze data in the CMS, which is the heart of the company’s operation. But the company has plans to expand the data warehouse to include data from the restaurants and spa, which are also important aspects of the business.

“By combing all this with our casino play, we’ll actually be able to get a better picture of what our customers are doing on the property,” Larsen said. “You may have a valuable customer, who doesn’t game, but they spend a lot in the spa or they eat in the restaurant. You should know about that customer and you should look to market to that customer.”

The company also has plans to implement a separate data warehouse for analyzing financial data, which will provide a more detailed picture of company operations.