West coast retailer fixes data anomalies

For London Drugs Ltd., the prescription for IT success was in data reconciliation. The Vancouver-based retail chain, with 59 outlets in British Columbia, Saskatchewan and Alberta is currently streamlining data using reporting services by implementing a business intelligence (BI) product from Microsoft Corp.

According to manager of solutions information technology Scott Riddell, London Drugs has been gradually migrating its mission-critical processes, such as human resources, inventory and merchandising, from legacy applications operating on IBM Corp.’s AS/400.

The company needed a reporting tool. London Drugs had been working on integrating its many ERP systems within its IT environment and experiencing various data anomalies.

There was so much data, and yet so little information, Riddell noted. “When you bring in an ERP for supply chain or anything it has its own set of rules that don’t always match the rules that you want to run your business,” Riddell said.

“The supply chain system works in and of itself but it doesn’t fully address the rules we want and supposed to observe. We wrote some very long queries in the database to go across the grain to look for the example where the rules have been broken because the application doesn’t enforce those rules.”

As a mostly Windows shop, the company is currently using Microsoft’s Biztalk Server as a hub to integrate its multiple business process applications. Microsoft offers the Reporting Services product as a free add-on to SQL Server 2000, which made its implementation a no-brainer, Riddell said. “We already know SQL,” Riddell said. “Since we already had the scripts, we just turned them into stored procedure, attached them to the report server and scheduled them to run at friendly times of the day.”

Released last January, Darren Massel, SQL Server and BI product manager for Mississauga, Ont.-based Microsoft Canada Co. said the server-based platform complements existing data warehousing and BI features of Microsoft SQL Server, Microsoft Business Solutions and Microsoft Office.

The Reporting Services product generates reports in a variety of file formats, including Excel, Adobe PDFs, Web pages, and XML-formatted information. Developers can author reports by using an add-on to Microsoft’s Visual Studio.Net development tool, Massel said.

Scheduled for release at the end of the year, a future edition of SQL Server, code-named Yukon, will expand the number of utilities for moving and reformatting data along with ETL (extraction, transformation and loading) capabilities.

Beta-testing begun last fall, Riddell said. The offering has a Visual Basic background similar to Crystal Reports. Once it snaps into the IDE, users select a data source, a query and then a layout pane, Riddell said. Currently the company offers 25 reports to its stores, suppliers and distribution centres, with plans to offer 60 reports in the near future.

Future projects include ramping up point-of-sale (POS) and promotion planning support. There are obvious benefits to consolidating data and having access to real-time information, Riddell said. “And now that we’re providing the users this they can go and get the information themselves. And they’re turning that into feedback on own work and correcting the mistakes quicker.”

Making a splash

According to Stamford, Conn.-based Gartner Dataquest, enterprises spent more than $2 billion on BI last year with spending forecasted at US$2.3 billion by 2005. With the release of Reporting Services for SQL Server 2000, Microsoft muscles into a BI market already dominated by vendors such as Actuate Corp., Cognos Inc. and Business Objects SA. Experts note that while these vendors have a licensing connection to the SQL Server database, it is unlikely to pose a restriction for these third-party tools as they have a head start in working with Visual Studio IDE integration.

Steve Lucas, director of Microsoft strategic programs for Business Objects (headquartered in San Jose) noted the Microsoft entry wouldn’t affect Business Objects’ product strategy. Many developers are still using Crystal Reports, Lucas said, adding Crystal Reports will continue to be shipped as part of Microsoft Visual Studio.NET, both the current version and the next release, code-named “Whidbey”.