Situation

Lloyds TSB, with a history that dates back to the 18th century, is one of the largest financial groups in the United Kingdom. It has grown steadily over the years through acquisitions and by creating new services within its organization. Lloyds TSB was formed in 1995 by the merger of Lloyds Bank and the TSB Group, along with Cheltenham and Gloucester—one of the largest mortgage providers in the U.K.—and Scottish Widows—one the U.K.’s largest providers of life, pension, and investment products. The group serves more than 16 million customers across three divisions—U.K. Retail Banking, Insurance and Investments, and Wholesale and International Banking. It has more than 2,100 branches throughout England, Scotland, and Wales under the names Lloyds TSB, Lloyds TSB Scotland, and Cheltenham and Gloucester. At the end of 2007, the group had nearly 70,000 employees and total assets of £353 billion (U.S.$519 billion).

With Microsoft BI tools, business users can … answer many of their own questions. As a result, they send our insight analysts more focused questions, which makes our analysis process more efficient.  Matt Sherriff Data Warehouse Development and BI Manager, Customer Value Management Lloyds TSB

From the early 1990’s, both Lloyds and TSB had Teradata systems, which were merged into a single data warehouse when the companies merged. Anyone who needed to access information in the Teradata Warehouse needed Structured Query Language (SQL) training and database knowledge, despite the fact that various business intelligence (BI) tools had been acquired in some departments. “Because the individual departments had bought all of these BI tools independently, we had several licensing agreements and little pockets of expertise in different departments,” says Matt Sherriff, Data Warehouse Development and Business Intelligence Manager for the Customer Value Management (CVM) group at Lloyds TSB.

Because of the need to understand the complex data and to write SQL queries, all reports for decision makers had to come from CVM. The group created the reports using the Microsoft Office PowerPoint® presentation graphics program or Microsoft Office Excel® spreadsheet software. This made it slow and laborious for decision makers in the various business units to quickly gather the exact information they needed to more effectively develop services and market them to existing customers.

“Our clients, who are end users in various business units such as credit card services and the loan department, wanted more information about customers’ spending behavior so that as a whole the company could maximize the value of the services that it offers,” says Sherriff. “Because those end users didn’t have SQL knowledge, they depended entirely on the 60 or so customer insight analysts in CVM to translate their business questions into database language and report back the answers. Analyzing the data to get it to the point where we could deliver it to them in a usable form was quite time-consuming, which meant that CVM was becoming a bit of a bottleneck.”

The CVM group wanted to eliminate the bottleneck and provide the information to decision makers in a more usable form. To achieve that, it first wanted to provide end users with easy-to-use querying tools and plain English names for the data fields so that they could quickly access data to get answers to many of their own questions without having to learn SQL and data acronyms. This would free up the CVM insight analysts to spend more time on detailed analytics. Second, the group wanted to expedite the analytics process so that when more complex queries came through, its analysts could respond quicker.

In 2007, the Lloyds TSB central IT group was looking to standardize on one BI solution across all Lloyds TSB divisions. While the Lloyds TSB CVM group knew that it needed a better solution to meet its colleagues needs, the question was how best to create that solution. “We wanted to create a single version of the truth by consolidating all of our BI applications and islands of information into a new set of technologies that would make it easy for analysts and end users to access the data that they needed to gain actionable insight that they could share with their colleagues,” says Sherriff.

Solution

At about the same time that Lloyds TSB was looking to standardize its BI environment, Microsoft and Teradata announced that they were working together to optimize interope-rability between Microsoft Business Intelli-gence solutions and the Teradata Warehouse. This team effort focused on helping information workers to access, analyze, and report on critical data more quickly, and on helping IT to streamline the delivery of business intelligence applications. It was exactly what Lloyds TSB was looking for.

“The alliance between Microsoft and Teradata was quite appealing because we were already using Teradata as our data warehouse and our employees already knew how to use Microsoft products,” says Sherriff. “Also, from an IT point of view, the Microsoft BI stack delivers more value for the cost.”

After the dashboards were built, business users no longer had to wait a week or more for month-end reports. They just went in and hit refresh to see the latest data.  Matt Sherriff Data Warehouse Development and BI Manager, Customer Value Management Lloyds TSB

The Lloyds TSB Teradata Warehouse consists of approximately 40 terabytes of data on two Teradata production servers and uses Active Data Warehousing techniques to make operational intelligence more accessible to end users. The data warehouse employs the Teradata Financial Services Logical Data Model (FS-LDM), which takes critical informa-tion from four key areas—sales and market-ing, profitability, risk management, and channel management—and creates common understanding across all business functions.

In late 2007, representatives from the Lloyds TSB CVM group traveled to the Microsoft Technology Center in Reading, United Kingdom. There the group built a test environment by using some of the organization’s own data to run high-level tests of the Microsoft BI stack—including Microsoft SQL Server® 2005 Analysis Services and SQL Server 2005 Reporting Services, Office Excel 2007, Microsoft Office PerformancePoint® Server 2007 business intelligence software, and Microsoft Office SharePoint® Server 2007—to see whether the tools met the company’s needs.

Based on the positive outcome of those tests, Lloyds TSB decided to run a more in-depth proof of concept for its credit card business unit back at its offices in London and Bristol. To get to the evaluation stage as quickly as possible, the company chose to base the proof of concept on an existing, manual management information solution for the credit card unit, which provided information about customers’ spending behavior and profitability. The manual solution already had a data mart and related reports based on Office Excel 2003 spreadsheet software.

Microsoft provides a comprehensive set of BI technologies that help enterprise to maximize value.

The development and testing team included database administrators, software architects, and security experts from the IT group along with data experts, BI experts, and business area experts from CVM. The proof of concept included an online analytical processing (OLAP) cube, three dashboards, a query tool that was built on Office Excel 2007, and a BI portal built on Office SharePoint Server 2007 Enterprise Edition. Once the infrastructure was in place and running smoothly, the cross-functional team delivered prototypes in each of these areas within a month and then released several upgrades. The OLAP cube points to the company’s credit card services data which is the materialized semantic layer in. The Teradata Warehouse, consisting of approximately 600 million rows of data. The dashboards and the query tool access the data through the cube.

“We tested the solution both with CVM insight analysts and with business unit colleagues, who didn’t have database experience, so that we could see how effectively the new solution put rapid, flexible BI output into the hands of the end users in the business units,” Sherriff explains. A group of three CVM insight analysts and three technicians from the central IT group did the initial data validation testing. Another group of 10 nontechnical business users—from the Managing Director level and below—tested the dashboards and the query tool, which are accessible through the Office SharePoint Server 2007 BI portal.

The dashboards provide high-level performance metrics and trends, and they continue to evolve as more reports and increased interactivity are added. Business unit colleagues can easily modify dashboard filters to focus on specific sets of metrics by products or sub-products. CVM insight analysts also use the dashboards as a starting point to determine which areas they need to look at in more detail. “CVM insight analysts can click the dashboard and see which areas aren’t performing and then use the query capability to dig into those areas in more detail,” says Sherriff. They can also use Excel Services in Office SharePoint Server 2007 to drill deeper into the data in spreadsheets that are linked to the dashboards.

By using the Microsoft BI tools, I was able in 20 minutes to create an analysis that had been on my ‘to do’ list for two months.  Camilla Lewis Senior Portfolio Manager Lloyds TSB

CVM insight analysts can also upload to the BI portal pieces of analysis that they’ve done and make them available to others in the credit card business unit. “We’ve set up some analysis workspaces where insight analysts can put a teaser that summarizes a more detailed piece of analysis that they’ve done and add a link to it,” Sherriff says.

During the nine months of testing the proof of concept, the CVM team received a great deal of positive feedback from users. Lloyds TSB expects to take full advantage of SQL Server 2008 BI capabilities when it completes deployment of its BI solution. When fully deployed, the solution will be used by approximately 1,000 employees across Lloyds TSB consumer banking operations.

Benefits

By using Microsoft BI tools with the Teradata Warehouse, Lloyds TSB expedited analysis and reporting. The company also put in-depth analysis capabilities and consistent, accurate data in the hands of business users, with minimal training. It gained all those benefits for approximately 80 percent less than the cost of competitive solutions.

Fast Answers

With the combination of Microsoft BI tools and the Teradata Warehouse, Lloyds TSB was able to automate more than 70 percent of its BI requests. “After the dashboards were built, business users no longer had to wait a week or more for month-end reports. They just went in and hit refresh to see the latest data,” says Sherriff.

This significantly expedites decision making and puts the appropriate data directly into the hands of business unit employees. “By using the Microsoft BI tools, I was able in 20 minutes to create an analysis that had been on my ‘to do’ list for two months,” says Camilla Lewis, Senior Portfolio Manager for Lloyds TSB.

The insight analysts in the CVM group also saw tremendous efficiency improvements. “After implementing the Microsoft BI tools, an analysis process that previously took 19 steps now takes only 9 steps,” says Sherriff. “Queries that used to take five minutes to write in SQL and a minute to run now run automatically in just a few seconds by using Microsoft BI tools and the Teradata Warehouse. In addition, month-end reports that used to take someone five days to produce now are automatic.”

When an executive refreshes a dashboard, that data comes directly from the Teradata Warehouse in real time, so we don’t have multiple copies of data, but rather a single version of the truth.  Matt Sherriff Data Warehouse Development and BI Manager, Customer Value Management Lloyds TSB

In-Depth Insight

Lloyds TSB business units are able to understand their customers in more detail so that they can more effectively address their needs. “With the Microsoft BI tools and the Teradata Warehouse, we are able to provide our business users with much more targeted and focused intelligence, rather than just doing a big data dump that they have to wade through to find what they really need,” says Sherriff. “With Microsoft BI tools, the things managers really need to see are right in front of them.”

Because it is so much faster and easier to query data by using the BI tools, business unit users and CVM insight analysts are able to focus their queries more effectively. “With Microsoft BI tools, business users can access data to answer many of their own questions. As a result, they send our insight analysts more focused questions, which makes our analysis process more efficient,” says Sherriff.

Similarly, the Lloyds CVM insight analysts are able to use the dashboards and reports to quickly narrow down the scope of their analyses and then use the query tool to focus on what they need to look at in more detail.

Consistent, Accurate Data

By using Microsoft BI tools with the Teradata Warehouse, Lloyds TSB provides management, business unit employees, and insight analysts access to the same underlying data. “When an executive refreshes a dashboard, that data comes directly from the Teradata Warehouse in real time, so we don’t have multiple copies of data, but rather a single version of the truth,” says Sherriff. “This means that when users ask the same question they come up with the same answer, so they don’t have to spend a lot of time comparing answers.”

The BI portal also provides a central place where any of the analysts can upload pieces of analysis that they’ve done and make them available to others in the organization. This eliminates the need for redundant efforts and helps both analysts and management to get answers more quickly.

Short Learning Curve

Because Microsoft BI tools are similar to other productivity tools in the Microsoft Office suite that Lloyds TSB employees already use, business unit users required only a couple hours of training. “One of the reasons that Lloyds TSB chose the Microsoft BI stack was because we could give our user community access to all the data and functionality they needed without requiring them to learn any new software, because they were already using Office Excel,” says Sherriff.

The ease of use will also save training time for the CVM insight analysts, who previously had to go through a long learning process to understand how to write efficient SQL queries and how to run a standard data model. Now with Microsoft BI tools, insight analysts need only a day of training.

80 Percent Less Cost

In comparing the cost of implementing Microsoft BI tools and the Teradata Warehouse with other BI solutions, Lloyds TSB found a significant cost advantage with Microsoft. “The cost per seat with Microsoft BI tools, which included putting in the production infrastructure, was 80 percent less than the cost per seat for just the user license of a broad cross-section of competitive products,” says Sherriff.

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

For more information about Teradata products and services, visit the Web site at:
www.teradata.com
www.teradata.com/microsoft

For more information about Lloyds TSB Group products and services, call 44 (0) 845 3000 000 or visit the Web site at:
www.lloydstsb.com

Microsoft Business Intelligence

Microsoft provides a comprehensive set of business intelligence technologies that make it possible for enterprises to maximize the value of their business data through timely analysis, reporting, and sharing of knowledge. These technologies extend from Microsoft Office system personal productivity tools to comprehensive Microsoft SQL Server data integration, reporting, and analysis tools. The entire portfolio is extensible with the Microsoft Visual Studio® development system to seamlessly integrate line-of-business applications and workflows into enterprise BI workflows.

For more information about Microsoft BI tools, go to:
www.microsoft.com/bi
www.microsoft.com/teradata 

This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.

Document published January 2009 

Top of page