Creating a Retail Information Environment at Glaxo Wellcome UK
A solution based on MS OLAP Services, MS SQL Server 7.0 and Brio Explorer version 6.0 to support business activity in the retail segment of Glaxo Wellcome UK. Glaxo Wellcome UK is part of Glaxo Wellcome plc (now GlaxoSmithKline), one of the world's leading research-based pharmaceutical companies.
The retail segment of Glaxo Wellcome UK needed to replace an Oracle Express environment that had grown over its 10-year life to become too complex and cumbersome to manage. Managers responsible for the segment needed to be able to report on the market and track performance indicators. They also needed to respond to change and compete in new ways. The existing system was too rigid to accommodate new business deals and was unable to support the business objectives of the segment efficiently. Sales reporting was fragmented, making it difficult to quickly pull together an authoritative set of information.
The complexity of the existing system presented a number of costly issues:
- data was dispersed around a number of systems
- great reliance was being placed on a few individuals with an expert knowledge of the system
- the process of gathering and analysing data was time-consuming
- analytical work was being duplicated
- inconsistencies were arising in ad-hoc reporting and analysis
- confidence in information was falling
- the ability to segment and target customers was limited
- stand-alone spreadsheet solutions were proliferating
- support costs were escalating
- vital decisions were being delayed
In short, decision-making was being hampered.
Thorogood was asked to work as part of a team to identify technical options appropriate to this and other similar business requirements and to implement a specific solution to meet the need for an up-to-date Glaxo Wellcome UK Retail Information Environment.
The Retail Information Environment need arose at a time when the business was reviewing its strategy for delivering Business Intelligence solutions. A careful study of the leading OLAP and data warehousing technologies was undertaken, including products from Oracle and Microstrategy already in use by Glaxo Wellcome UK. As a consequence of the review it was decided to put MS SQL Server 7.0 and MS OLAP Services to the test.
Requirements analysis and development took place over a four month period. Thorogood's role was to provide MS OLAP Services and MS SQL Server 7.0 expertise, and to assist with:
- capturing and understanding the business need
- understanding the technical possibilities
- designing and implementing the technical solution
- selecting the front-end tool, in this case Brio
- building weekly feeds from Glaxo Wellcome's Oracle relational databases using Microsoft's Data Transformation Services (DTS)
- cube design, incorporating MDX custom measures skills transfer.
The system draws information on a weekly basis from four Oracle relational databases and an Oracle Express database, all running on Unix servers. MS Data Transformation Services is used to extract information and move it to a MS SQL Server 7.0 environment on NT. The Data Mart has five dimensions: product, customer, geography, distribution and time, and the fact tables currently cover two years with approximately 26 million rows, and will build to cover five years.
Multiple fact table schemas are used to handle different measures at different levels, as appropriate. Its current size is 2.5 Gigabytes. MS OLAP Services is used in 100% MOLAP mode. To control sparsity and to join fact tables, 25 cubes are built and joined into two virtual cubes. The MS OLAP cubes database currently occupies 2.3 Gigabytes. Brio Explorer 6.0 is used to provide the user interface, facilitating multi-dimensional query, reporting, and high-quality presentation. The Brio tool offers the ability to mix relational and multi-dimensional query results in the same views and to deploy on the desktop or the web.
Thorogood was uniquely placed to contribute to the creation of Glaxo Wellcome UK's Retail Information Environment for a number of reasons:
- as an independent company specialising in Business Intelligence solutions, we were able to draw on our knowledge and experience of the leading technologies, including those from Oracle, Hyperion, Microstrategy and Microsoft
- we worked closely with Microsoft for 12 months prior to the commercial launch of MS SQL Server 7.0 and MS OLAP Services, during which time many of our consultants were trained in the technology
- we began gaining 'early adopter' experience with customers using beta versions of MS OLAP Services as early as five months before the commercial launch in November 1998
- we have close links with many of the vendors providing front-end tools for MS OLAP Services, including Brio, Business Objects, Cognos and ProClarity
- we have worked closely with Glaxo Wellcome UK for over a decade
This project shows how Thorogood can help take the risk out of adopting the new generation of low-cost Business Intelligence tools for maximum business benefit.
Benefit to the client
The new system meets the business goals of the retail segment managers:
- standard reporting objectives have been met and information is quickly available to a wider business audience
- more managers have greater flexibility to view and analyse the business, and their own performance
- data is being sourced from the company's key operational systems and staged in a single data mart, giving complete confidence in the quality of information being acted on within the Retail Information Environment
- managers are able to quickly and easily obtain the information they need
In addition to meeting business objectives for the Retail Information Environment, IT constraints on business strategy have been greatly reduced, as have the ongoing costs of supporting the Retail Information Environment. There have also been benefits in terms of IT strategy. The project has demonstrated the power of MS SQL Server 7.0 to Glaxo Wellcome UK which has adopted it as its strategic way forward for Data Marts.