Data Warehousing

With about 30 vendors pouncing upon the

small but burgeoning pie of the data warehousing market in India, the user is more

perplexed than ever. Here's an attempt to sift from the hype and hoopla some issues

dogging the corporates, who do have reasons to use this technology: the competitive and

compelling reasons of informating their businesses.

Channel V



The Problem: With more and more exclusive music channels dishing out music 24
hours a day, Star TV's Channel V was faced with two problems: how to retain a loyal

viewership and how to get a database of this audience for future use when the company

would launch its Direct-to-Home (DTH) services in the country. If only had the company a

way to find out the popular tastes of the music-loving audience, the Channel could tackle

these problems. For instance, the channel could not take a quick decision on who should be

awarded the Pop King of 1998.








The Solution: Channel V discovered a simple solution-data warehousing. The
process involved building a database by compiling the viewers' feedback. Once the

information was compiled, the analysis tools could tackle any number of tough questions

and the list of awardees was there in no time. The same technique has been deployed by

Star TV to prepare a customer database for its DTH services in the country. The list

already includes profiles of 12,000 potential customers and the company hopes to take this

number to at least one million as soon as it receives a license to launch its services in

India.







Domino's Pizza


The Problem: Since its inception in 1960, Domino's recipe for success has been
to offer a limited menu through take-home orders or delivery. For over 35 years this meant

delivering Pizza. But with increasing competition and changing consumer tastes, the

company's problem was to identify other easy-to-deliver food of choice for its consumers,

while at the same time sticking to its 'Total Satisfaction Guarantee' policy. The other

problem was to manage its own operations of $2.5 billion annual sales, accruing from 5,100

company and franchised pizza outlets at 700 corporate locations worldwide, processing

between 35,000 and 45,000 W2 forms each year.







The Solution: The company decided to switch to an open systems approach,
replacing its proprietary information system. First, Domino's converted its distribution

and control applications used to run its US distribution centers. It chose Informix-SE,

Informix-4GL and Informix-SQL because the products offered the greatest flexibility and

superior price/performance. Later on, the company standardized on Informix software for

all company-wide applications. The first applications implemented include store data

consolidation, data warehousing and human resources/payroll. Now the Informix-based

store-data-consolidation system collects point-of-sale data such as food costs, sales

information, and labor costs, in order to analyze and calculate key performance

indicators, which are then compared to 'expected' values. This helps identify in-store

problems early, and gives management in the field and at corporate headquarters accurate

and timely information to track operations. Because of these changes, Domino's now

supplies bread sticks, chicken wings, and other easy-to-deliver food from all its stores.

The company now plans to collect data about distribution, audits, human resources,

finances and much more. Issues such as payroll, benefits, legal claims, unemployment

claims, workmen's compensation etc were addressed by replacing its proprietary

paper-intensive HR systems with PeopleSoft HRMS software optimized for Informix. The new

PeopleSoft application provides detailed information about Domino's employees and enables

the company to automate and adapt human resources tasks and business processes to meet its

needs.







AP Government


The Problem: An IT-savvy Chief Minister that he is, Chandrababu Naidu's system
is an integral component of the huge databases on the government servers in the state. The

various modules covering the entire gamut of the state administration such as electricity

board, urban development, water supply, electoral rolls etc keep pouring in loads of data

into the servers. The CM's problem was to make business sense of the data flowing in,

identify trends and patterns to increase efficiency, build accountability, reduce costs

and improve governance of the state.







The Solution: The CM decided to build a pilot Executive Information System (EIS)
and handed over the base architectural and design phase of the project to Pune-based

C-DAC. This is a highly centralized and protected system, with daily data updates of data

undertaken by a core wing which constitutes CM's office, NIIT, APTS and a few others. It

took the state over two years to build the current database which contains data ranging

from power, transport, GIS, roads and buildings, police, education, civil supplies,

photo-ID cards, family and rural welfare, to name a few. Data analysis and query tools

were used on selected modules of the data subsequently to see the patterns. For instance,

Naidu closely monitored the coal reservoir levels when flood situation was causing havoc

in the state in 1997. And the result, in 1998, the CM directed the authorities to switch

over from thermal to hydel power generation as the reservoir levels rose up. The outcome

was Rs2 crore saving per day for the government. It is understood that the state

government is contemplating building up a data warehouse of the entire data collected in

the state for leveraging on such benefits.

These are undoubtedly success stories of people and businesses which have emerged as
leaders in the functioning of the businesses. But how did they all succeed? Not by sheer

magic. All that they have been doing is to look deeper and deeper into the crystal ball

technology called data warehousing. It is the emergence of Data Warehouse as an effective

tool for business intelligence that is all set to be the next IT wave in the Indian user

segment. Fierce competition, a strong customer orientation and the need for

better-informed decisions are pushing companies to warehouse, lest they wear out.






According to Bill Inmon, who is considered to be the originator of data width="400" height="290" align="right"> warehousing concept, a data warehouse is defined
as a subject-oriented, integrated, time-variant and non-volatile collection of data in

support of management's decision-making process. A data warehouse, consequently, is a

repository of historical and current data of a company, stored in an organized format in

order to transform it into meaningful business information through the use of several

tools. A data warehouse is an electronic data store that cleanses and transforms data

obtained from many sources and in many forms to a consistent, uniform format, so that

users can extract what is directly relevant to their business needs.






The first feature of a data warehouse is that data are organized according to subject
instead of application. For example, an insurance company using a data warehouse would

organize the data by customer, premium and claim, instead of by different products. As

such a typical insurance company data warehouse would have data captured in insurance

policies, loan transactions, premiums paid data etc which would be classified in terms of

customers and subjects. This type of classification would then help the company to analyze

which segment of the market is preferring which of the company's offerings. The data can

also be then studied from different points of view, like geography wise, product wise,

income category wise and thus aid in designing better product design for the needs of the

market.






The second feature of a data warehouse is that it is integrated as data residing in
separate applications in the operational environment while moving into the warehouse

assumes a consistency and uniformity. Essentially, what this means is that as a company

grows in size and complexity, the nature of data collected by different arms of the

company may wary in terms of structure. For instance, there is an example of a service

company which at one point in time had as many as 36 different databases of different

structures, on different platforms. When the time came to create a marketing program, the

company decided to integrate the entire bevy of databases into one single structure. While

the effort involved was 'nightmarish' in the words of the MIS chief, at the end of the

day, the company was able to put some method into the madness and make sense out of the

mountain of data to create an island of information, which was useful in understanding the

market acceptance of the product portfolio amongst different buying segments. Essentially,

the data warehouse attempts to store current and historical data for the purpose of

comparisons, trends and forecasting and therefore has a time-variance. Data in a warehouse

is not volatile as it is not updated or changed in any way once the data enters the

warehouse, but is loaded and accessed later on.






A data warehouse can essentially be used in a Decision Support System (DSS) and an
Executive Information System (EIS). DSS is a system that provides managers with

information they need to make decisions. These systems have the effect of empowering

employees at all levels, providing them access to business and financial information that

directly impact their productivity and quality of work. While EIS is a concise snapshot of

how the company is doing. It allows greater flexibility in 'slicing and dicing' data and

allows exploration of data through multiple dimensions. The objective of any data

warehouse is to enhance the quality and speed of the decision made and translate

information into business intelligence, giving an edge over others in a competitive

environment. A data warehouse can be used to understand business trends, make rational

forecasting decisions, take product mix decisions, identify core competencies or for

profitability analysis. The US-based supermarket chain Wal-Mart is a classical example.

With an estimated 1 terabyte of raw data, which is captured on a regular basis from its

stores across the world, the data warehouse of Wal-Mart holds approximately 65 weeks of

historical data classified by item, merchandisers, geographies etc. This data is then

analyzed to understand buying patterns of the products carried by the supermarket, the

sourcing pattens, the inventory carrying patterns etc, which would have a direct impact on

the cost structure. This in turn helps the company to remain one of the largest and, more

importantly, profitable chains in the world.






The drivers


"The need for better-informed and timely decisions is the primary factor that leads
to data warehousing," says G Anandan, Business Manager (Cognos), IIS Infotech.

"With the liberalization of economy, competition is getting tougher and fiercer,

hence there is a need to do business over a large geographical area. Databases in

organizations are swelling and growing with growing maturity of IT. Hence the requirement

of solutions which provide hassle free ad hoc access to data to predict trends, forecast

and analyze." He should know. For Cognos, a leading provider of solutions in the

international business intelligence arena, claims to have a 25% market share with its

software installed on about 600 PCs in the country. Companies such as Star TV, Malayala

Manorama, C-Marc India and SmithKline Beecham Consumer Healthcare are some of the

customers of this Canada-based $300 million company which has partnered with IIS Infotech

for distributing its products here.






Having weighed the pros and cons of implementing a data warehouse solution as a user
organization, Anwer Bagdadi, GM (Information Management) at Godrej-GE Appliances Ltd,

classifies the need for data warehousing among companies in India under three stages.

"The first and the most important need is Query, Reporting and MIS tool. The second

stage requires Analysis, Drill Down and DSS tool, while the need in the third stage is for

Mining, Predictability, Profitability, Planning and Business Intelligence tools."

While Muthuswamy Gabriel, Acting Country Manager, Tech Services Director at Informix AP,

says, "The demand from the corporate decision makers for competitive business

analysis and information-that will enable them to make the million dollar decisions for

the company-is one of the propellers for data warehousing."






Competition is the biggest business driver of data warehousing, as in any other part of
the world. Global success stories of businesses that have reaped the harvest of data

warehousing are generating substantial interest and driving the adoption of data

warehousing in the country presently. "The data warehousing concept in India is

driven by MNCs which have a presence in India," says Gourish Hosangady,

National Technical and Sales Manager, SAS Institute India Pvt Ltd. More than half of the

20-odd projects in the country are those of MNCs such as Citibank, MaxTouch, ACC, Pepsi,

Modi Telstra and Godrej-GE. Nevertheless, competitive Indian industries and companies have

been quick to adopt this technology. For instance, in the banking and finance sector,

Reserve Bank of India, State Bank of India, IDBI, ICICI Bank, and the National Stock

Exchange are jumping onto the data warehousing bandwagon. It is believed that one of the

reasons for the financial industry to embrace data warehousing solutions is the

predominance of global competition and the presence of international players in India. For

instance, Citibank, with one of the largest customer bases for credit cards in this

country would need to be countered with solutions which help competitors to understand the

market nuances as well, if not better. The fact that banks such as Citibank are highly

technology savvy puts additional pressure on Indian competitors. And IT solutions such as

data warehousing can create a level playing field.






The second driver for data warehousing applications is the sheer increase in data. With
the increasing competition and the need for understanding the market increasing

day-by-day, the amount of data that is collected by the organization has simply

multiplied. As the volume of data increases, so does the need to informate the data and

create information streams which will run into the critical groups in the company. Ever

increasing technological prowess may provide a method to store the data, while increasing

sophistication of business needs will drive the need to analyze the data to make coherent

decisions. "Availability of new technologies like massively-parallel processing

system, parallel database technology from vendors such as IBM and Oracle, and new

intuitive query and reporting tools like MS Excell, Cognos and Brio are driving the data

warehousing segment from the technology standpoint," says DV Jagadish, Deputy GM,

(Emerging Business), Tata IBM. "To add to this, the ever increasing data build-up is

also driving this segment since data in average organizations is said to double every

three years and only 7% of this is actually analyzed. Thus, there is more and more data

which corporates are realizing they possess which could be analyzed for better-informed

decision making."






Awareness


Despite the first rung of companies in the banking and finance, FMCG, hospitality and
service industries taking the lead, the awareness levels about data warehousing is

significantly low. "There is awareness, but it is not sufficient enough. The advent

of several global vendors together with the media are working toward constantly increasing

this awareness," says Hosangady. Santhosh G, Business Manager (Data Warehousing) at

Wipro Infotech, is happy that there has been a significant improvement in the level of

awareness in the market place in the last two years. "However, the key point is that

this awareness in many cases is largely localized to the CIOs and Systems departments.

Even there it is yet in the concept understanding stage and is a long way away from actual

implementation. In many cases, the business users do not seem to be demonstrating enough

interest in deriving business benefits from this technology," he says.






That may be a clich‚. For it is more or less clear that for data warehousing
technologies to permeate more into organizations, the business imperatives of the

technological solution must be stated and proved right on top. For instance, there are

still organizations in the country which do not deploy data warehousing solutions simply

because of lack of faith and an RoI justification. According to a Gartner Group report

published early last year, "it is difficult to ascertain definitively whether a

product is used in a structured data warehousing environment or in a conventional DSS

context...a data warehouse must be compiled using a variety of components glued together

with a lot of blood, sweat and tears. This reduces product cohesiveness, increases market

fragmentation...and makes quantifying more challenging." So unless and until the

industry evangelizers take a lead in this direction, data warehousing will not see a rapid

ascent that it surely deserves.






The user perspective


Just in about one year of operating in the Indian environment, software vendors and
implementers have begun to face the Indian side of the data warehousing market, and are

trying to address it. "The prime difference between data warehousing in India and

other parts of the world is the availability and nature of organizational data. Data

sources most often seem to be disparate, in multiple locations and often on different

hardware platforms and operating systems. Also, very often data is unclean and thus

extraction, transformation and cleansing of data is a challenge in the Indian

context," explains Jagadish. "One of the key areas of difference is customer

audit data being unavailable in India," says Bagdadi. "Thus one does not know

why a customer buys what he buys."






Also corporates and users are identifying the Indian requirements and trying to address
them with the best possible solution. While MNCs prescribe the software package to be used

in India in keeping with the parent company's experience, the Indian partners realize the

local requirements and choose what is best for them. Take the Star TV case, for instance.

"Flexibility and user-friendliness were the top-most priorities in choosing a package

that could generate any kind of report for the user's requirements. The Hong Kong and UK

offices of Star TV are using Business Objects and there was a lot of pressure on us to use

the same software," says Pramod Raghav, Manager (Software Development), Star TV

India. "After detailed evaluation, we found that Cognos was better able to generate

online reports and hence we chose them." Similarly, Godrej-GE chose Metacube from

Informix for India, while its parent company uses Oracle.






"The basic difference in India is our lack of IT maturity and hence we call every
OLAP or Data Analysis Project as a data warehouse! As most companies in India put their

operational systems in place, the confusion will clear up," observes Shekhar

Dasgupta, Country Manager, Oracle Software India. It is to be noted that in this solution

space, Oracle has about 35 sites doing DSS or some kind of data mart implementation.






While the market is besieged with products and implementers who can offer services, the
top-most priority of the user segment is user-friendliness and flexibility of the product.

A very careful and critical market that India is, typically all users would like to do a

pilot implementation to get the feel for the product features and implementation

requirements, before commissioning a project.






"We carried out an extensive evaluation of various solutions offered by bidders for
our data warehouse project. Wipro ranked first amongst the five bidders in our

techno-commercial evaluation. Further, because of its track record of successful

implementation of mission-critical projects in capital markets automation, we felt Wipro

is the right choice for providing systems integration services for the project,"

explains Satish Naralkar, Sr VP and Head of Technology at NSE. Incidentally, NSE is the

biggest data warehousing project in the country, with the first phase of the project alone

accounting for 500 gigabytes of data.






"Experience and domain knowledge of systems integrators and consultants are major
considerations from the user perspective," says Dasgupta. "Ability to manage

huge volumes of information, integrate/interface with existing systems on heterogeneous

platforms and vendors' commitment on the product's life and continued enhancements are

other considerations," he adds. To this end, Oracle will soon release a comprehensive

data warehouse building tool.






Confusion with ERP


At a time when measures are taken by vendors and implementers alike to build greater
awareness, there seems to be some amount of confusion between ERP and data warehousing in

the market. Worse still, budget constraints are seeing companies opt for one technology

over the other. A relatively new entrant in the market, Chennai-based Open Business

Solutions India, in partnership with US-based Systech Systems, has made this observation.

Says Sridhar Ramaswamy, MD, OBSI, "We found that companies such as TVS Electronics

and Spencers stalled the data warehousing projects for the time being as they had already

made significant investments in ERP." The scene is further complicated by the

attempts of ERP vendors launching data warehousing solutions along with ERP solutions. The

recently launched SAP Business Intelligence Warehouse is a case in point.






"The fact of the matter is that the two systems are quite distinct in the way they
are built and also in the way they would be accessed. ERP is a transaction-based (updated

data) system while a data warehouse is query-based (historical data). And hence there

cannot be a merger of the two technologies," explains Jagadish. Agreeing with him is

K Padmanabhan, VP, TCS, "Maybe the ERP vendors are extending their turf in the data

warehousing segment too. But as technologies, these complement each other and we would see

that, increasingly, more Indian companies which have taken to ERP will graduate to data

warehousing also."






However, there is an issue here. A company which implements ERP would logically also focus
on the organization's data collection and management processes. To say that the two are

exclusive of each other could perhaps be out of touch with real issues. While it is

accepted that ERP is transaction based and data warehousing is historical data based, any

business decision would need the two to work cohesively with each other. In fact the moves

of the ERP vendors to provide data warehousing solutions is probably a manifestation of

this need for cohesion that is being articulated from the user's end. Secondly, the

transactions in most cases are functions of and finally end up as pieces of data which

then would need to be stored for further archiving and analysis.






Commenting on the status of data warehousing scene in India, Dasgupta says that "by
classical definition of data warehousing, there is no single data warehouse implementation

in the country. We have OLAP (Online Analytical Processing) on transactional data and some

Data Mart Suites addressing line of business or departments' DSS needs. These are being

implemented by professionally managed companies; some of them are large Indian corporates

and some are MNCs or MNC joint ventures." Essentially, a data mart is information

pertaining to a smaller department or line of business or a product



within an organization, while a data warehouse is the sum total of these smaller data
marts or departments in an organization.






The vendors have therefore started to focus on a bottom-up approach in implementations.
Rather than going in for enterprise-wide data warehousing solutions that call for high

investment and longer periods of time, vendors are looking at starting off on projects

with small departmental marts. "For, cost on average for a departmental data mart is

anywhere between Rs30 lakh and Rs40 lakh as opposed to a full-fledged data warehouse which

costs upward of Rs1 crore," says Jagadish. Besides, an organization can get the

results on investments of a data mart even before the project is completed. Justifying

this is a 1997 study published by International Data Corporation, describing 62 companies

of all sizes and in different industry segments that had implemented a data warehouse

solution. The results of the study revealed a mean RoI of 400%. While the companies which

implemented data marts showed an average RoI of 600%.






The forerunners


A look at the Indian market shows that banking and finance, retail, telecom and FMCG
companies are in the forefront of using data warehousing technology because they are

already in a highly competitive environment. Typically, data warehousing is seen as a need

in markets where it is not the product but the customer who decides the volume and the

market in these segments, and where geography and time are not constraining entities. The

need arises because the company has to monitor the behavior and trends continuously for

detection of abnormality, fraud etc.






As per the Mercer Group, an independent consultant, among the largest solutions segments
where business intelligence is being used is customer relationship management, fraud and

delinquency detection, supply chain management and human resources. Business intelligence

is implemented in many different ways, in different industries. Customers like banking,

telecom and retail companies are using it for marketing purposes such as customer

relationships, cross selling and effective promotion/campaign management. Distribution and

manufacturing companies, on the other hand, can use data warehousing information to

streamline business operations in areas such as financial and sales analysis, forecasting

and SCM. While some customers like insurance and credit card companies use it to detect

fraudulent practices.






"Industries that have closely followed in going the data mart way are petroleum and
derivatives, shipping and transport, power, metals manufacturing and fabrication. Other

industries in the medium bracket who have planned for DSS-related assignments are in the

field of durable consumable goods, automotive ancillaries, construction and cement,

fertilizers, automotive vehicles, chemicals, pharmaceuticals, paper, paints,

service-transport, hotels, textiles and fabrics, ready-made garments and footwear,"

according to MP Ullas, Executive Manager, DSS Consulting Group, Tata Infotech Ltd. Tata

Consultancy Services has also observed similar trends in the market. "We are seeing

that manufacturing and health care segments are poised for a growth next in the

market," says Padmanabhan.






Implementers are busy identifying new industries and niche segments to operate from.
"We have classified the market into three categories and we will have a focus on each

of the areas. This includes the small (- Rs300 crore), medium (+ Rs300 and - Rs1,000

crore) and large (+ Rs1,000 crore) companies based on their annual revenues," says

Ullas. Wipro, on the other hand, has set up a Center of Excellence for Data Warehousing,

where it showcases its technological innovations and demonstrates pilot projects.






Despite these measures, the user community is an unhappy lot. "In my opinion there is
need for strong drive and visibility creation in this area. Product and service vendors

are not investing enough currently and it looks like wait-and-watch policy," says

Anwer.






Long-term benefits


What companies need to understand is that data warehousing will result in long-term
benefits in standardizing data across the organization. "A company can build a single

repository of market survey data-across time and product groups-for quick and flexible

reporting, better monitoring of brand performance in terms of market shares, percentage

growth, impact of promotions and an integrated data source for ad hoc querying and

reporting," says Ullas. Product companies can have information on shelf movement,

cross linkages, customer profiling, usage patterns etc, and sales trend analysis,

sectorial analysis and product mix behavior can be measured and understood quickly.






"Better geographic and marketing understanding, accurate information for better or
more timely decision, direct access for decision makers to relevant business information,

effectiveness of sales promotion, improvement in target setting, receivable management,

improvement in transaction systems by removing the inconsistency and continuous feedback

to planners and management about deviation from measurement are other tangible benefits

that an organization can gain in no time," says Anwer, who is presently leveraging on

the information generated form the data warehouse in his organization.

 

Monitoring

with Mouse!



A thorough sweep of the Chief Minister's Information System in Andhra Pradesh could tell
that you are venturing into one of the most complicated data warehouses in the making. The

CM's System is an integral component of the huge databases on the government servers in

the State. The base architectural and design phase of the project has been assigned to

C-DAC, inter-operability between the different levels being an important consideration.

Standardization is the buzzword. The various modules covering the entire gamut of state

administration are being codified to provide uniformity in design and also unique IDs are

being issued. Also the base map of the state is being standardized on a 1:250,000 scale

which is to be shortly upgraded to a 1:50,000 scale, using GIS tools.






Centralized database


Currently, the CM's Information System is highly centralized and protected but is to seen
be available on the web using internet with state-wide WAN and the governmental intranet

when the data will be ported and accessed on browsers. This is to enable easier

upgradation of the data warehouse.






The site is daily updated by a core wing, which constitutes the CM's Office, NIIT, Andhra
Pradesh Technology Services and a few others. The current database has taken over two

years to be developed. NIIT has been very active in the project and is also credited with

web enabling the system. Different modules in the system have been assigned to different

companies. They range from power, transport, GIS, roads and buildings, police to

education, civil supplies and photo-ID cards, and family and rural welfare.






Substantial gains


The best part of the whole database is the analysis done using the latest data tools.
Chandrababu Naidu is said to be using the Andhra Pradesh State Electricity Board (APSEB)

module very regularly. The Plant Load Factor (PLF) in the power plants incidentally, has

gone up by 8 percent in the last one year. The coal reserves too have seen significant

stock being built up. Naidu was also closely monitoring the reservoir levels when flood

situation was causing havoc in the state last year. An interesting utility in 1998 was

when the CM directed the authorities to switch over from thermal to hydel power generation

as the reservoir levels rose up. The outcome was Rs 2 crore saving per day for the

government.






The information generated on a regular basis ranges from serious issues like the crime
rate in different regions to the courteousness of the APSRTC staff.! Among the regular

briefings also include the payments of the treasury department, performance of various

educational institutions etc. The database offers a thorough analysis besides detailed

inputs of each and every element in it. Surveys are conducted to draw logical conclusions

to the data loaded on the system. The core applications are to be part of the AP Value

Added Network Services.