PREMIER Bankcard, LLC (PREMIER) one of the largest VISA and MasterCard credit providers in the United States, needed to enhance scalability and performance for its business intelligence (BI) data warehouse and online transaction processing (OLTP) databases. “BI began as an area of research for us, but has become absolutely mission critical,” says Dan Zerfas, Vice President of Software Development at PREMIER. The company enhanced its BI infrastructure by upgrading its 17-terabyte data warehouse to Microsoft® SQL Server™ 2008 Enterprise (64-bit), hosted on a server computer with 16 Intel® Itanium® 2 processors. PREMIER also upgraded its OLTP database to the 64-bit version of SQL Server 2008. The upgraded deployments provide a better view of the business, enterprise-grade scalability, maintenance without scheduled downtime, and easier database management.
PREMIER Bankcard, LLC (PREMIER), headquartered in Sioux Falls, South Dakota, is one of the nation’s leading credit card providers to the underserved, yet creditworthy market. A sister organization to PREMIER Bank, the company, with more than 3,000 employees, provides VISA and MasterCard services to more than 5 million customers, and prides itself in providing some of the highest levels of customer service in the industry.
As part of the company’s commitment to providing great customer service, several years ago it deployed an online transaction processing (OLTP) database hosted on earlier versions of Microsoft® SQL Server™. The OLTP database, which is now 2.5 terabytes, includes customer account data generated from customer representatives at PREMIER’s five service locations, where they process more than 500,000 credit card applications per month and constantly update current customer accounts.
The company further enhanced its customer service, and gained greater insight into customer needs, by creating a data warehouse, which has grown to 17 terabytes, to support reporting and analytics against data imported from the OLTP database and other key data sources. This early business intelligence (BI) deployment, based on Microsoft SQL Server 2000 database software, worked so well that the company’s 50 analysts—as well as senior executives—found ever more ways to derive value from the information to better meet customer needs.
As use of BI at PREMIER evolved into a mission critical role, the company needed to enhance its data warehouse and analytics infrastructure to keep pace with the company’s nearly 10 percent annual growth, and it’s even more steeply rising demand for BI services, including reporting and analytics. The company also needed to upgrade its OLTP database.
To meet its needs, PREMIER required a solution that would provide:
- Enterprise-grade scalability, deemed especially important as the company already hosts more than 35 terabytes of data across about 100 instances of SQL Server, including the single-instance 17-terabyte data warehouse, which grows by about 13 gigabytes (GB) per day, or more than 4 terabytes a year.
- Reduced need for database maintenance requiring scheduled downtime, as BI becomes too mission critical to take down.
- Enhanced performance to support up to 60 concurrent queries per second against the data warehouse.
- Ability to support OLTP rates of up to 2,000 transactions per second during the busiest periods of providing customer service.
PREMIER Bankcard upgraded its OLTP database and data warehouse BI infrastructure to Microsoft SQL Server 2008 Enterprise (64-bit). The 2.5 terabyte OLTP database is hosted on a single instance and runs on an HP ProLiant DL585 computer with four dual-core 64-bit processors and 32 GB RAM. The relational database supporting the data warehouse is deployed an HP Integrity rx8640 computer with 16 Intel® Itanium® 2 processors and 64 GB of RAM.
The data warehouse BI infrastructure, deployed on the Microsoft Application Platform, has a multi-tier architecture that includes:
- ETL Tier. Microsoft SQL Server 2008 Integration Services provides the extract, transform, and load (ETL) functionality as the company imports information from more than a dozen sources, including the OLTP database, transaction data from a third-party service provider, First Data Resources, and credit bureaus. ETL is hosted on a server computer with four 32-bit dual-core processors and 32 GB of RAM.
- Relational Data Tier. The 17-terabyte data warehouse, which contains some 80 fact tables, about 850 dimension tables, and 45 billion rows of data, is hosted on SQL Server 2008 Enterprise (64-bit) running on Microsoft Windows Server® 2003 Datacenter Edition for Itanium-based Systems. PREMIER uses the Table Partitioning feature of SQL Server 2008 to divide data into monthly tables, while administering the multiple tables as a single entity. The company uses the Online Indexing feature of SQL Server to defragment indexes without having to schedule downtime. The data warehouse runs on an HP Integrity rx8640 server computer with 16 Intel Itanium 2 processors and 64 GB of RAM. Storage is on an HP StorageWorks XP24000 with 27 terabytes of raw capacity.
- Analytics Tier. Microsoft SQL Server 2008 Analysis Services is used to create and manage 70 multidimensional data cubes that contain pre-aggregated summaries so that queries can be answered quickly. The analytics tier is hosted on a 64-bit server computer with 16-processors and 64 GB of RAM.
- Reporting Tier. Microsoft SQL Server 2008 Reporting Services—a comprehensive, server-based solution for creating, managing, and delivering real-time information to support daily operations and decisions—is used for creating some 4,000 reports against the data warehouse. The reporting tier is hosted on a 32-bit server computer with 4-processors and 16 GB of RAM. The reports, most of which are accessed through a portal created using Microsoft SharePoint® Server 2007, are used by approximately 2,700 PREMIER employees, with an average of 200 concurrent users.
The company’s internal developers use Microsoft Visual Studio® 2008.
PREMIER Bankcard found it easy to upgrade to SQL Server 2008. The company began by first upgrading its test data warehouse system and then its test OLTP system.
Because the company is a heavy user of SQL Server Integration Services, and includes its own custom code for ETL, it recompiled all of its Integration Services packages prior to the upgrade and tested the packages on its development server.
The test upgrades and Integration Services preparation made for a swift and uneventful upgrade of the production systems.
“It took about 5 hours to upgrade our ETL box to SQL Server 2008 because we wanted to ensure our Integration Services Packages were working properly, which they did,” says Van Zanten. “Our data warehouse server and OLTP server each required just a couple of hours to upgrade. The entire process went smoothly, and without any problems.”
Upgrading its 17-terabyte data warehouse and 2.5-terabyte OLTP databases has provided PREMIER Bankcard with a number of benefits, including providing a better view of its business, enterprise-grade scalability, maintenance without scheduled downtime, and easier database management. The company is also enjoying efficient ETL with Integration Serevices, and data compression.
Better View of its Business
||We have about 9,000 concurrent users generating a continuous 700 transactions per second, sometimes more than doubling to 2,000 transactions per second. With the 64-bit version of SQL Server 2008 running on Itanium 2 processors we see no limit to our ability to scale our transaction processing.
||Ron Van Zanten
Directing Officer of Business Intelligence
PREMIER’s new BI infrastructure is providing it with a better view into its business, and helping the company’s 50 analysts keep up with the growing demands for BI insights to guide decision making.
“BI began as an area of research for us, but it has now become absolutely mission-critical,” says Dan Zerfas, Vice President of Software Development at PREMIER Bankcard. “We’re in a business where some of the most important questions asked by upper management begin with ‘What if.’ SQL Server 2008 Analysis Services is a tremendous tool for answering those questions. Managers want to analyze the data to see trends and cause-and-effects to better guide our decisions. BI has become a central element of how we run our business.”
The power of analytics to answer ‘What if’ questions is especially important when dealing with issues that guide credit decisions.
“We just had a meeting earlier this week with a department that makes decisions on the credit worthiness of our customers,” says Zerfas. “Their question was: ‘What if we made a slight percentage point change in our approval rate for one element of customer qualification? What would this change?’
“It used to take months to answer this type of question, assembling and analyzing the data,” says Ron Van Zanten, Directing Officer of Business Intelligence at PREMIER Bankcard. “With Analysis Services and the rest of our BI environment on the Microsoft Application Platform we can provide even better results within just a few days. The drill-through capabilities of Analysis Services are very important to our analysts.”
SQL Server 2008 Reporting Services is used to provide value from the data warehouse, with some 4,000 reports, as employees throughout the company make use of various report sets to better serve customers and enhance internal operations. Integration of Reporting Services and Analysis Services with Microsoft Office 2007 Excel also extends the value of the data warehouse.
“Our analysts can do anything with Excel, so integrating Excel as a front end for our BI infrastructure is extremely popular,” says Zerfas. “It makes it easier to explore the huge wealth of data we have in our 17-terabyte data warehouse because our people are so used to working with Excel. They can get the information they need without having to go between their spreadsheet and a separate analytics tool.”
Upgrading its data warehouse to SQL Server 2008 Enterprise (64-bit) hosted on the Itanium 2-based HP Integrity rx8640 computer has given PREMIER the enterprise-grade scalability it required to keep pace with the rapid growth of its data warehouse, which is expected to exceed 30 terabytes within the next five years. Itanium 2 server computers help to speed time-to-information and real-time business decision-making through extreme parallelism and computational capabilities such as extreme compute parallelism, massive caches, and high memory addressability. SQL Server support for 64-bit architecture is an important element of providing scalability. With the earlier infrastructure the company used Address Windowing Extensions (AWE), a Windows Server feature used to support large memory configurations. But the company prefers the native memory support available with 64-bit technology.
“We wouldn’t be where we are today with our data warehouse without 64-bit technology,” says Van Zanten. “We couldn't properly manage our 17-terabyte database without the benefit of the extra memory that the 64-bit version of SQL Server 2008 provides. When we were operating with the old 32-bit infrastructure we were hard pressed on performance, even when our database was only 3 terabytes. With our 64-bit SQL Server 2008 data warehouse running on Itanium 2 processors, and the ability to add all the memory we could need, scalability isn’t an issue, even for our rapid data growth.”
The company is also enjoying the scalability gained for its OLTP database. “We have about 9,000 concurrent users generating a continuous 700 transactions per second, sometimes more than doubling to 2,000 transactions per second,” says Van Zanten. “With the 64-bit version of SQL Server 2008 running on X64 processors we see no limit to our ability to scale our transaction processing.”
Maintenance without Scheduled Downtime
PREMIER has eliminated much of its need for scheduled downtime by using the Table Partitioning and Online Indexing features of SQL Server 2008 to enable database maintenance while remaining in operation.
Table Partitioning enables fast data loads and simplified maintenance for very large tables by giving database administrators the ability to treat multiple tables as a single entity. Online Indexing enables concurrent modifications (updates, deletes, and inserts) to the underlying table or clustered index data and any associated indexes during index data definition language execution—meaning the index can be defragmented while the database continues to work.
||Our analysts can do anything with Excel, so integrating Excel as a front end for our BI infrastructure is extremely popular. It makes it easier to explore the huge wealth of data we have in our 17-terabyte data warehouse.
Vice President of Software Development
“Using Table Partitioning and Online Indexing, we’ve reduced the time we spend on maintenance by 80 percent, and are able to complete our maintenance without scheduling downtime,” says Van Zanten. “Because we can now maintain our tables as needed, as opposed to only when we could schedule the time, we’ve found that queries run more predictably—which means faster. Fast query processing is essential to both our OLTP and data warehouse systems, and perhaps particularly for our OLTP system where even a second’s delay is multiplied by millions of customer interactions per month.”
The company uses Table Partitioning for the data warehouse which currently holds six years of data using 72 partitions. “Partitioning greatly simplifies maintenance, and along with Online Indexing helps us to keep all of our data accessible 24x7,” says Van Zanten. “With our old system we would have to go down for four hours to re-index, and we simply can’t afford that downtime anymore.”
Table Partitioning and Online Indexing have also helped restore weekends, especially three-day weekends, for the company’s team of five database administrators.
“SQL Server 2008 has done away with so much of the scheduled downtime that used to take the fun out of our holiday weekends,” says Zerfas. “Fortunately scheduled downtime is about the only downtime we ever see. We’ve been running on SQL Server since version 7.0 and during this period of nearly a decade we’ve experienced less than 12 hours of unscheduled downtime.”
Easier Database Management
Hosting more than 35 terabytes of data across more than 100 SQL Server instances, PREMIER is keenly interested in database management. Though the company has always found it easy to work with earlier versions of SQL Server, it is finding benefits from a feature that was introduced with SQL Server 2005—Dynamic Management Views. The company also uses the Extended Events (xEvents) feature, new for SQL Server 2008, which provides a lightweight mechanism that supports capturing, filtering and acting upon events generated by the server process.
PREMIER has found DMV Reports—which give database administrators a greater transparency across multiple aspects of database health and performance, providing performance data and recommendations for improving database performance—especially helpful in enhancing query performance.
“We’re always on the lookout for ways to improve performance,” says Van Zanten. “DMVs make it easy to assess efficiency of query performance. We can see I\O usage, track memory contention, monitor index usage, and in so many other ways see exactly what is happening with database performance. With our earlier system it was very difficult to piece together that information. In contrast we can use the DMVs to continually improve our operations.”
The company uses DMVs to measure its overall performance against its own informal service level agreements.
“We use DMVs to regularly monitor query response times against our OLTP, data warehouse, and about 10 other significant databases,” says Van Zanten. “These databases are automatically tested every 10 minutes, with results recorded for analysis. This testing shows that our OLTP system averages a response time of about 70 milliseconds, all throughout the day. The data warehouse averages a response time of 700 milliseconds. Our internal standard is that we want to keep the OLTP system response time under 250 milliseconds because for users, that means instant response. Similarly, sub-second response for queries against the 17-terabyte data warehouse also provides the users with what feels like instant data. This is the kind of service we’re able to provide using DMVs to tune our SQL Server 2008 deployments.”
PREMIER Bankcard values the xEvents feature because it is efficient and extremely lightweight in collecting performance data.
“We have so much volume on our OLTP system, that it used to be difficult to do traces as we run up to 2,000 transactions a second,” Van Zanten says. “With xEvents we can do a direct-targeted trace event and put that with virtually no impact on the production server as opposed to trying to set up a separate test environment to try to duplicate a problem.”
||Using Table Partitioning and Online Indexing, we’ve reduced the time we spend on maintenance by 80 percent, and are able to complete our maintenance without scheduling downtime.
||Ron Van Zanten
Directing Officer of Business Intelligence, PREMIER Bankcard
Zerfas notes: “SQL Server 2008 and the rest of the Microsoft Application Platform gives us the enterprise-grade scalability and integrated BI infrastructure we need to find more and better ways to enhance the customer experience and build our business.”
Efficient ETL with Integration Services
PREMIER Bankcard is a heavy user of ETL, importing data from more than 60 sources, so it was delighted to find that SQL Server 2008 Integration Services was even faster, straight out of the box, than the SQL Server 2005 Integration Services that had been serving the company well.
“The first thing we noticed after upgrading to SQL Server 2008 was that the data pumping was faster,” says Van Zanten. “Integration Services has always been a good data mover, and we were happy to see it get even faster because every morning we take about 200 million rows of data into our ETL box and publish between 70 and 80 million records to the data warehouse after it's been cleaned and standardized and filtered.”
The company appreciates the completeness of the ETL toolset that Integration Services provides because of the variety of data types the company works with. “We take the information from more than 60 sources, ranging from mainframe shops to XML and everything in between. You learn fast that just because someone says their data is XML formatted doesn’t mean that it is correctly formatted. Integration Services is flexible enough that you can write any kind of code necessary, or use custom components that someone else has built to make sure the data is clean before sending it off to the warehouse. We need the flexibility and power of Integration Services because we get information from so many different sources.”
Internal developers at PREMIER Bankcard have made innovative use of Integration Services to apply business logic to incoming data to enhance antifraud and other measures to protect customers.
“We receive about 500,000 credit card applications a month,” Van Zanten says. “If somebody applies for a credit card 28 times with the same address but a different name or the same name but a different social security number or any number of other factors, we have business rules that flag possible fraud. We’ve found that Integration Services is the quickest way to spread all this information around our systems every morning. Integration Services provides a powerful tool for integrating business rules into workflow.”
Previously the company had used separate code, developed using Visual Studio and the Microsoft .NET Framework, but decided to move the code into Integration Services after hearing someone talk about ETL capabilities at a Microsoft Professional Association for SQL Server (PASS) conference.
“We heard an Integration Services guru from Microsoft talking about innovative uses of Integration Services at PASS and realized this was exactly what we needed to do,” Van Zanten recalls. “We moved our .NET-based code into Integration Services and were impressed by what a powerful tool it is. We now have Integration Services working in concert with our data warehouse to interrogate incoming data to protect against fraud.”
The Data Compression feature of SQL Server 2008 has helped PREMIER Bankcard save storage space while enhancing performance.
“Using page compression reduced the size of one of our data warehouse fact tables from 2.2 terabytes to 700 gigabytes,” Van Zanten says. “Another fact table went from 1.8 terabytes to 900 gigabytes. We were pleasantly surprised to find that compression actually improved query response times. We had anticipated slower response times because of the overhead of compression, but found that our queries actually ran 10 to 15 percent faster.”
Compression has helped the company extend the life of existing storage resources. “At some point we will have to add another cabinet to our SAN, which we anticipate will cost more than $300,000. The Data Compression feature of SQL Server 2008 will enable us to delay that purchase by a couple of years.”
Windows Server 2008, SQL Server 2008, and Visual Studio 2008
Windows Server 2008, SQL Server 2008, and Visual Studio 2008 provide a secure and trusted foundation for creating and running your most demanding applications. Combined, the products offer advanced security technology, developer support for the latest platforms, improved management and Web tools, flexible virtualization technology to optimize your infrastructure, and access to relevant information throughout your organization.
For more information about Windows Server 2008, go to: www.microsoft.com/windowsserver2008
For more information about SQL Server 2008, go to: www.microsoft.com/sql/2008/default.mspx
For more information about Visual Studio 2008, go to: www.microsoft.com/vstudio
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 Intel products and services, visit the Web site at: http://www.intel.com/
For more information about PREMIER Bankcard, LLC products and services, visit the Web site at: http://www.premierbankcard.com/
This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.
Document published August 2009