Microsoft SQL ServerCustomer Solution Case Study

Data Warehouse and Multidimensional Cubes Save Services Firm Time and

OverviewCountry or Region: United StatesIndustry: Professional Services

Customer ProfileBased in St. Louis, Missouri, TALX offers a suite of payroll and human resources services to help organizations meet today’s demands for reduced costs and higher service levels.

Business SituationTALX needed a data warehouse to provide a consistent reporting environment and to reduce the time its managers spent in generating reports.

SolutionTALX deployed a data warehouse based on Microsoft® SQL Server™ 2000 and Microsoft Windows Server™ 2003. SQL Server Analysis Services is used to create multidimensional data cubes.

Benefits Time and money saved in

creating reports Consistent data for reporting More efficient customer service Enthusiastic user adoption

“With our SQL Server data warehouse we can generate reports in seconds that used to take us days. That’s a much appreciated boost in productivity.”Laura Browder, Manager of Corporate Development, TALX

TALX, an industry-leading provider of outsourced human resources services including employment verification and unemployment tax management, needed a better reporting solution to unify data from sources including an Oracle transactional database, a SalesLogix customer relationship management application, and a Microsoft Business Solutions–Solomon accounting solution. Working with Quilogy, a Microsoft® Gold Certified Partner, TALX deployed a data warehouse running on Microsoft SQL Server™ 2000 Enterprise Edition and the Microsoft Windows Server™ 2003 Enterprise Edition operating system. The company uses SQL Server 2000 Analysis Services to build data cubes for analytics. The solution saves TALX time and money by unifying reporting and eliminating the problems of conflicting data that existed before the deployment. TALX uses its better view of its business to enhance customer service.

SituationTALX is the market leader in two of its chosen fields: employment and income verifications, and unemployment tax management. The TALX suite of electronic services also includes tax credits and incentives, paperless pay, time tracking, W-2 management, I-9 management, and onboarding (new employee enrollment) services. These solutions provide employers and employees with universal and secure access to pay and human resources data through intranets, portals, phone, and Web.

TALX provides its services to more than 7,000 organizations, including Bank of America, Boeing, Microsoft, Pfizer, Sears, and Walgreens. The company’s popular The Work Number

service frees companies from the time-consuming task of having to verify employment or income data every time an employee applies for a loan or requires verification for other reasons. TALX also helps its customers reduce unemployment tax costs with its UC eXpress service. Across its range of offerings, TALX uses automated services to replace paper-based, manual processes common to many payroll and human resources departments.

As with most organizations, TALX identified accurate and timely reporting as a critical element for managing operations and identifying new opportunities. However, since TALX didn’t have a data warehouse for centralized data storage, people

were spending too much time pulling together data to create reports. Managers needed to obtain transactional data from its Oracle databases supporting The Work Number and other services. Accounting information, including general ledger and invoicing information, came from a Microsoft® SQL Server™ 2000 database supporting Microsoft Business Solutions–Solomon. SQL Server is part of Microsoft Windows Server System™ integrated server software. Customer data came from the company’s SalesLogix customer relationship management (CRM) database running on SQL Server, while other information was stored on spreadsheets created with Microsoft Office Excel® 2003 spreadsheet software.

Requiring individuals to accumulate their own data sets from this heterogeneous collection of sources led to inconsistencies as reports that were intended to represent the same facts differed in the results presented because of variations in how they were created and the source of data they used. Gathering the data and assembling the reports was a time consuming process, and many reports were already a month behind when they were first distributed.

To enhance the accuracy and timeliness of its reporting infrastructure, TALX managers determined that it needed to create a business intelligence (BI) data warehouse that would:

“Beyond benefiting from the centralized data store, SQL Server Analysis Services has brought precision to how we analyze the data.”Laura Browder, Manager of Corporate Development, TALX

Unify disparate data sources. Serve as a single version of the

truth. Reduce the time managers spent

compiling reports, providing more time for analysis of the data.

Enable more analysis of the information for better decision making.

SolutionThe TALX IT team conducted a proof of concept study to determine which database to use in creating the data warehouse. The team found that Microsoft SQL Server, along with a broad range of third-party tools, provided the easiest platform for creating and managing the solution it needed. Working with Quilogy, a Microsoft Gold Certified Partner, TALX deployed a BI data warehouse running on Microsoft SQL Server 2000 Enterprise Edition and the Microsoft Windows Server™ 2003 Enterprise

Edition operating system. Windows Server 2003 is the foundation of Microsoft Windows Server System integrated server software.

SQL Server Data Transformation Services (DTS) is used to perform extract, transform, and load (ETL) processes against data brought into the warehouse. The first phase of the BI data warehouse is focused on company-wide revenue and The Work Number service. Additional TALX services will be added to the data warehouse with the rollout of future phases scheduled for 2006.

All tiers of the BI data warehouse are co-hosted on an HP ProLiant DL585 computer with four AMD Opteron 64-bit processors and 8 gigabytes (GB) of RAM. Storage is on an HP EVA5000 system area network (SAN).

Though currently deployed as a 32-bit solution, TALX plans a future upgrade to the 64-bit version of SQL Server 2005, and the 64-bit version of Windows Server 2003 Enterprise Edition.

A second instance of SQL Server on the same hardware hosts a complete copy of the data warehouse for quality assurance testing.

The multi-tier BI data warehouse includes:

ETL Tier. Source data is imported from numerous systems including:− Oracle-based transactional

databases running on Windows Server 2003.

Data Flow – Some 25 DTS packages are used in bringing source data into the data warehouse.

− Microsoft Solomon accounting data, including general ledger and invoicing information, hosted on SQL Server.

− SalesLogix CRM information hosted on SQL Server.

− Numerous additional sources including Excel spreadsheets.

Some 25 DTS packages are used for ETL. SQL Server stored procedures are used to implement business rules.

Staging Tier. DTS packages are used to load source data into a staging area, where it is processed according to which data mart it is destined for. A series of key mapping tables is maintained in the staging tier to allow the incorporation of consistent nomenclature throughout the data warehouse solution. The key mapping allows the data warehouse to maintain a reference back to the source data, while removing any constraining factors those keys may place on the production data warehouse.

Data Warehouse Tier. From the staging tier, information is loaded into the 500 GB relational data warehouse, which runs on a single instance of SQL Server.

Data Marts Tier. Information from the data warehouse is used to populate three data marts:− TALX Revenue—for analyzing

overall corporate revenue. The TALX Revenue data mart supports analysis of adjusted revenue for all TALX business units. Revenue is viewable by

business unit, service line, general ledger account, customer, sales region, and time period.

− The Work Number Transactions—for analyzing detailed usage patterns of TALX verifiers. The Work Number Transactions data mart supports analysis by employer, verifier, industry, sales region, and time period, as well as many other categories.

− The Work Number Transaction Value—for analyzing revenue generated by customers and services. The Work Number Transaction Value data mart allocates revenue back to the original transactions for revenue-per-transaction analysis.

The online analytical processing (OLAP) data marts, created using SQL Server Analysis Services, include three fact tables, nine multidimensional cubes and 27 dimensions. Cubes are refreshed nightly.

Reporting Tier. Data analysts use ProClarity for generating reports against the cubes. A second phase of the project, planned for early 2006, will use SQL Server 2005 Reporting Services.

Presentation Tier. TALX will be using Microsoft Office SharePoint® Portal Server 2003 and Decision Support Panel, by Decision Support Panel, a Microsoft Gold Certified Partner, and ProClarity to support general reporting to some 400 users across the organization.

“Even before our beta release—while we were still in the QA process—we had people using our new data warehouse to help them make better business decisions.”Bryan Garcia, Chief Technologist, TALX

BenefitsCreating the BI data warehouse and multidimensional OLAP cubes has provided TALX with a number of benefits, including time and money saved in creating reports, consistent data for reporting, more efficient customer service, and enthusiastic user adoption.

Time and Money Saved in Creating ReportsThe TALX IT group faced a quandary common to many organizations: How to justify the investment in a data warehouse. “We knew among ourselves that a BI data warehouse would be a tremendous help for TALX in terms of running the business more efficiently,” comments Bryan Garcia, Chief Technologist at TALX. “We knew a BI data warehouse would enable the entire company to operate wildly better. But how do you quantify ‘wildly better’? We decided to simply show that the BI data warehouse would generate enough savings that the net cost would be zero, and we won approval.”

Laura Browder, Manager of Corporate Development at TALX, was an early champion for the BI data warehouse and helped to make the business case for deployment.

“We did a study and found we were wasting time and money assembling reports,” Browder advises. “We had people pulling information from the different databases and manually

putting together spreadsheets. We had numerous employees each spending an average of two days per month assembling reports—sometimes duplicating efforts to arrive at the same numbers. With our SQL Server data warehouse we can generate reports in seconds that used to take us days. That’s a much appreciated boost in productivity.”

Other savings and related benefits are more difficult to quantify, but still are seen as significant. “Our data warehouse is refreshed daily and provides us with more timely information,” Browder notes. “Because of all the manual processing that used to be required in assembling reports, it simply took too long to get the reports out. This meant that at the end of July we might be putting out May reports, because the data was already a month or two behind.”Timely information helps the company more precisely gauge the effects of new marketing programs. “With our BI data warehouse we can, for example, immediately see the impact of a price change,” Browder says. “We can test a new program in one market and if it works, we can quickly roll it out to other markets. Conversely we can also immediately see if a program is not being accepted well in the market, and just as quickly end it. Access to precise and timely information helps us to make better decisions and better meet the needs of our customers.”

Consistent Data for Reporting

“The BI data warehouse is giving us information that we really couldn’t see before. It helps us identify trends within our own operations, as well as in the greater marketplace.”Laura Browder, Manager of Corporate Development, TALX

As with other well-managed organizations, TALX wanted to implement a BI data warehouse to achieve consistency in reporting. Its lack of a centralized data store allowed for too many variations to enter into the report creation process.

The over-arching issue was the need for a common data repository, where reconciled revenue and other information was available, yet access to this information was managed based on employees’ roles in the company. The information in the data repository is critical for TALX to manage current client relationships, and to cross-sell and target new business from both clients and prospects. In addition, the information is used to develop new service offerings. The data warehouse is currently accessed by 25 power users, who create and distribute reports companywide. As adoption of the data warehouse grows, the user community could increase to more than 200.

“Consistency in reporting was our top goal in creating our data warehouse,” says Browder. “We needed one common data source because we were pulling information out of our Oracle transactional systems, accounting information from our Solomon application, CRM data from SalesLogix, and raw data from a range of other sources. Data points were pulled by different people in different ways, and on top of that, everyone would take their own approach to slicing and dicing the

data. All of this resulted in inconsistent data.”

Assembling data manually could be especially difficult because of the number of transaction types TALX supports. Previously, the creator of a report might inadvertently combine different transaction types.

The data warehouse has provided new analysis capabilities for those who were already working with such data, while opening up the analysis capabilities to a whole new group of users.

“Our SQL Server data warehouse has eliminated the inconsistencies that used to make reporting so difficult to work with,” says Browder. “Once you experience the consistency of a data warehouse you wonder how you managed the business without one. Beyond benefiting from the centralized data store, Analysis Services has brought precision to how we analyze the data.”

More Efficient Customer ServiceThe BI data warehouse provides a central repository that makes it easier for TALX employees to deliver exceptional customer service. Account managers, for example, have an immediately accessible central point for answering customer questions.

“Our data warehouse will make life easier for our account managers and the customers they support,” says Browder. “Before our data warehouse,

if a customer had a question on their billing, they would call their account manager, and the account manager would have to call accounting, and accounting would pull the invoice or look it up in the system and then get back to the account manager. With our new data warehouse all the middle steps will be gone. On their own, the account manager can immediately access the information they need to resolve customer queries.”

The BI data warehouse provides views into the business that weren’t readily possible before—including trending information for long-term planning, and granular data for more precisely meeting customer needs. “The BI data warehouse is giving us information that we really couldn’t see before,” Browder says. “It helps us identify trends within our own operations, as well as in the marketplace. For example, If we can see an uptick in use of our employment verification services in a specific geography or market segment, we can analyze customer demand to see if we should be focusing our efforts on marketing the same service in different areas of the country or in different market segments.”

The data warehouse is making it easy to examine detailed data that otherwise would have been too time-consuming to extract for analysis. “For example, we are interested in seeing how many of our transactions cover a customer’s active [current]

employees and how many cover inactive [former] employees,” explains Browder. “Prior to our data warehouse this type of information would have been extremely difficult and time consuming to find. Now it is instantly available for analysis. The ability to analyze particular data like this throughout our organization will help us fine-tune existing products and identify the need for entirely new products.”

Enthusiastic User AdoptionUsers' response to the BI data warehouse has been enthusiastic. “We started getting requests for access to our BI data warehouse before it was completed," concludes Garcia. “Even before our beta release—while we were still in the QA process—we had people using our new data warehouse to help them make better business decisions.”

Microsoft Windows Server SystemMicrosoft Windows Server System integrated server infrastructure software is designed to support end-to-end solutions built on Windows Server 2003. It creates an infrastructure based on integrated innovation, Microsoft’s holistic approach to building products and solutions that are intrinsically designed to work together and interact seamlessly with other data and applications across your IT environment. This helps you reduce the costs of ongoing operations, deliver a more secure and reliable IT infrastructure, and drive valuable new capabilities for the future growth of your business.

For more information about Windows Server System, go to: www.microsoft.com/ ‌ windowsserversyste m

For More InformationFor 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: www.microsoft.com

For more information about Quilogy products and services, call (866) 784-5649 or visit the Web site at: www.quilogy.com

For more information about Decision Support Panel products and services, call +46 8-669 03 40 or visit the Web site at: www.dspanel.com

For more information about ProClarity products and services, call (208) 344-1630 or visit the Web site at: www.proclarity.com

For more information about TALX products and services, call (314) 214-7000 or visit the Web site at: www.talx.com

© 2005 Microsoft Corporation. All rights reserved. This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY. Microsoft, Excel, SharePoint, the Windows logo, Windows Server, and Windows Server System are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. All other trademarks are property of their respective owners.

Document published September 2005

Software and Services Microsoft Windows Server System

− Microsoft Windows Server 2003 Enterprise Edition

− Microsoft SQL Server 2000 Enterprise Edition

− Microsoft Office SharePoint Portal Server 2003

Technologies− Microsoft SQL Server 2000

Analysis Services

Hardware HP ProLiant DL585 computer with

four AMD Opteron 64-bit processors and 8 GB RAM

HP EVA 5000 SAN

Partners Quilogy ProClarity Decision Support Panel