Enterprise Apps Special: Data Warehousing and Mining

Data mining empowers decision-makers at Ranbaxy

Ranbaxy is an Indian company that is truly global. An important part of its decision-making process is its business intelligence (BI) and data mining tools. Shipra Arora delves into the BI systems of this pharma major

Ranbaxy Laboratories houses a huge repository of data. However, what was required was access to right information at the right time, requiring a unified view of the entire data lying within the organisation and building business intelligence on top of that. This, the company realised, was a pre-requisite to sustain its leadership position in the changing market scenario where marketing dynamics were increasingly impacting growth rates. Data warehousing and data mining applications came to the rescue and allowed Ranbaxy to quickly and easily recover precise technical and business information from within the company’s rapidly growing pool of data.

Business challenge

The company had a host of systems that capture information and multiple data sources, giving rise to isolated islands of data. The environment had an ERP system (SAP 4.6C) as the base. Apart from data lying in SAP there was data available from various other sources—other applications within the organisation across different locations and data from external sources. However, the company was not able to provide a single unified view of that information to its users. This had its repercussions in the form of delays in reporting, further delaying the decision-making process. On many occasions, a manual collation of data was also required, which was then prepared in Excel sheets and given to the users. This led to delays in accessing the information and also caused problems in culling the relevant information out of the entire database.

According to Vijay Sethi, director-Business Operations, Ranbaxy Laboratories, the company considered pumping in the entire data into SAP and then trying to provide one view to the users. But it was pointed out that though theoretically the data could be pumped into SAP, it would put undue pressure on the ERP system. “We realised that SAP being a transaction system and not a true data warehouse or reporting system, this option would not really make sense. Loading so many users on reports would affect its performance.” For information, like historical data, instead of putting pressure on the transaction system, the best way to go was to deploy a data warehousing solution on top of which the company could do analysis with the help of a data mining tool.

Ranbaxy’s task was to find a powerful extraction tool, one that could delve into its disparate data sources to recover historical information. All that data would then have to be combined with a powerful, scalable business intelligence infrastructure that would have as its key challenge the ability to analyse multiple data sources and generate customised reports based on the requirements of a wide list of corporate users. This essentially required implementation of data warehousing and mining applications.

Decision-making process

As the company initially evaluated data warehousing and mining in late 2001, there were various presentations made, explaining the concepts. Ranbaxy even invited a leading data warehouse expert in the country, who addressed a group of business and IT users on what data warehousing and mining meant and how it could help the organisation. The business users essentially involved the marketing users (essentially at the middle and top management), at whom the applications were being initially targeted. After various orientation and introductory sessions the company evaluated the offerings available in the market. After analysing multiple technology offerings from different vendors, Ranbaxy zeroed in on Hummingbird ETL (extraction, transformation and loading) and Hummingbird Business Intelligence (BI) products. According to Sethi, it was decided during this process itself that the company would go for both data warehousing and mining applications as both are tightly integrated and that it would not really make much sense to put up a data warehouse without going in for data mining. Though the company found the data mining capabilities of the solution very limited, it decided to allow users to start using the solution. The plan was to go in for additional data mining tools on top of this solution at a later time.

Implementation

Ranbaxy started implementing the data warehousing and data mining solutions during early 2002. The solution took four to five months to implement. The company had initially rolled out the solution for its marketing set-up in India, thereby covering all marketing offices. It has recently started extending the solution for its manufacturing set-up too. According to Sethi, the company faced some issues with respect to expertise available in the country on the product and relating to change management as is the case with any software roll-out. The implementation was done by a leading IT consulting firm.

Out of nearly 1 TB of data residing in Ranbaxy’s storage boxes, the company has put over 100 GB of critical data in the data warehouse. The data warehouse has all the relevant data that is required for business intelligence activities.

Solution

While ETL extracts data from SAP and other sources, the BI tool provides the presentation layer. The ETL tool changes the company’s view to information by enabling mining of information from a mix of legacy databases, SAP as well as databases handling Microsoft SQL and Microsoft Excel formats. For handling SAP data, the tool includes a SAP MetaLink that helps users locate SAP data and a native SAP DataLink, specifically tailored for extracting data from SAP environments. The rest of the data can be extracted by using the native and ODBC connectivity functionality of the standard ETL DataLinks package. ETL was also used to retrieve legacy data from Ranbaxy’s MIS operations, as well as the other data in a SQL database. All data was then cleansed on a ‘staging server’ to produce a unified, standard version of data from multiple systems. The cleansed data was then used to populate Ranbaxy’s customised ‘data marts’—the smaller repositories of strategically aligned information.

Once extracted, the data

was headed for the company’s implementation of the BI tool, a suite of business intelligence tools that gives rapid access to wide ranges of enterprise information. It also provides a powerful yet simple query and reporting functionality to translate that data into a basis for intelligent, strategic business decisions. ETL has enabled the company to tie together multiple information sources, including its SAP applications, and consolidate enterprise-wide data into its data mart for reporting and analysis using BI. A BI tool was used for analysing and generating reports from data marts. Using BI, the reports were generated in different customised formats and were distributed to respective users.

The BI tool is put on the company’s intranet—myranbaxy.com. The company has defined the access rights within the application itself—specifying who can access the system and specific functions that can be accessed.

Functionality

In terms of functionality, the data warehousing and mining applications work in two ways on a day-to-day basis—push and pull. Sethi explains that on one hand the relevant content is pushed to relevant users on the other they can pull the content. Push would mean that the company decides on the users and what kind of information needs they have. The system generates automated e-mail to them where reports are attached and they are delivered on a daily basis. These reports include sales trends, outstandings trends—basically related to marketing areas, sales, collection, receivables, sales in a particular region, location or sales according to brands, etc. These are generally capsules of information that users need on a daily basis.

On the pull side the information is provided to relevant users in a BI interface. Users can log in and access the relevant information. Also, as part of the pull strategy, the company has introduced its dashboard initiative, wherein a dashboard resides on the desktops of select users from top management. These users can click on the icon provided on their desktops and see everything rendered in graphs, which they can further drill down to access specific information if they choose to. Business intelligence provides for a do-it-yourself model, where users decide what they want to see and cull that out from the system. According to Sethi, both pull and push are critical models and it is more a matter of user convenience.

Benefits

The new data warehouse architecture significantly reduces the time it takes to create ad-hoc reports essential for strategic analysis. ETL has enabled the extraction of data from various data sources using the same tool and has significantly reduced the time spent on getting the required information. The BI tool has allowed easy access to critical business information. Data warehousing and mining applications have essentially opened the door to a wide range of analytical processes, which not only allow Ranbaxy to see what it had, but also predict what is needed to be done. “The consolidation of enterprise data, including financial, sales, and product information, empowers our management to make faster and better decisions,” says Sethi.

With its new data warehouse operational, Ranbaxy was able to create a long list of customised reports, including comparisons with historical data. The BI tools have allowed a mix of Ranbaxy users—both sophisticated and casual technology clients—to build and run reports.

Future plans

Apart from rolling out the data warehousing and mining applications to the manufacturing side of the company, Ranbaxy will also be adding data mining tools from other vendors sitting on top of the data warehouse and the existing data mining capabilities. It has already piloted two data mining tools and will start formally rolling them out in the first quarter of 2004.

shipra@expresscomputeronline.com