CURRENT TRENDS IN DATA WAREHOUSING METHODS AND

TECHNOLOGIES

Vera Ivanova

Assistant Professor, ITEC program, York University, Toronto, Canada

Keywords: Data Warehousing, Business Intelligence, Current Trends, Technologies, System Architecture, Analyzes,

Review.

Abstract:

Data Warehousing (DW) methods and technologies are in a new stage of their evolution and of their

amalgamation with the enterprise businesses, they serve. The main goals of this work are to identify, review

and analyze the latest trends in DW. A systematic approach is followed to recognize, define and analyze the

most important trends. The approach is based on the trends’ corresponding role and value in the business

processes and intelligence (BI). For this purpose we start with updated definitions of DW and BI and then

consider the generalized Architecture of today’s DW. We then “drill down” to analyze the DW problems

and trends in their solving for data quality provisions, regulatory compliance, infrastructure consolidation,

and standardization, corporate performance optimization and metadata management. This in-depth logical

analyzing approach results in comprehensible conclusions to be considered on the important early phases of

DW projects, as it is well known that early project decisions carry impacts for the whole DW system life

span.

1 INTRODUCTION

Even though DW have been evolving for the last 10

years experts, managers, watchdog agencies,

vendors and developers all agree that the DW

methods and technologies are in a new stage of

evolution (Agosta, 2004), (www.microsoft.com,

2005), (Singh, 2005). The highest priority post-

millennium IT project for many corporations was the

DW (Glick, 2005). The opinions on the DW new

features vary, due to their application and industry

specific nature. For example, a large retailer DW in

many aspects looks quite different from an

University Westermann, 2001),

(http://web.mit.edu/warehouse, 2005), yet they may

share similar new technology trends, or in some

cases identical driving business requirements.

The realities of the DW evolution across many

industries changed the way DW are used in terms of

operational tasks, publishing and interactive use of

broad audiences of employees, suppliers an partners.

Many DW implementations brought business

improvements with good ROI and still many failed

to do so.

DW technologies and methods today deliver far

more than key performance indicators to top

managers. An update is needed to recognize,

capture, review, analyze and define these trends and

changes. The value of similar works is in DW high

level architectural decisions and in the first steps of

large and expensive DW projects considerations, as

well as in attempts to improve , refresh the assets, or

consolidate old corporate DW that still exist.

2 DATA WAREHOUSING AND

BUSINESS INTELLIGENCE

Let us briefly consider the DW and BI updated

definitions for the purpose of this work. DW is the

concept to consolidate enterprise data from

production systems often of heterogeneous sources

and platforms and optimizes them for decision

making, monitoring, reporting, analysis and

interactive publishing. The data are extracted as they

are generated from transactional, operational, and

other system sources (see p.3), or on scheduled

periodical intervals. The main reason to separate the

DW data from other data is to optimize the

performance of complex queries that typical for the

above activities as the execution of similar queries

on the source systems would degrade them and is

not acceptable or possible. There are many other

business and IT reasons for separation, considered

further in details in this work. DW is implemented

297

Ivanova V. (2006).

CURRENT TRENDS IN DATA WAREHOUSING METHODS AND TECHNOLOGIES.

In Proceedings of the Eighth International Conference on Enterprise Information Systems - DISI, pages 297-301

DOI: 10.5220/0002498902970301

 SciTePress

on its own architecture and often infrastructure to

reflect the business specifics and to utilize the

available limited resources.

The classic definition of the founder W. Inmon

(Inmon, 2002) of DW as “subject oriented,

integrated, non volatile and time variant collection

of data in support of management decisions” is still

valid, but is overloaded and enriched with many new

features to accommodate the new technologies and

business needs. All new technologies (multimedia,

wireless, GPS, RFID, etc.) generate more and more

data and several large DW already exceeded 1

Petabyte (1K Terabyte). From the business

competition and regulatory compliance perspective

DW became a source and environment for many

new business processes to model , monitor and

optimize, even if they are in fact, operational.

The term BI is accepted for information

technologies, which transform business data into

meaningful information to support result oriented

strategic and sometimes tactical business decisions.

This broad term covers a broad field of applications

– from a simple reporting query tools to full scale

DW. We consider here this last case, where the DW

approach is to get data in different formats from

different sources (databases, web sites and services,

ftp servers etc.) and extract, consolidate, clean and

store them in formats, optimal for the business

analytical purposes and various processes. Presented

with accurate and timely intelligent reports in

business terms the enterprise decision makers can

develop and optimize processes and strategies. The

term BI is also used for advanced data analysis, data

mining and corporate performance measurement and

management processes. The tools and the

applications are industry and department specific.

For example, the financial departments use them for

budget management, the customer relations

department for marketing strategies; and the

executives for CPM. Many or all of these tools are

part of the DW architecture and infrastructure.

3 COMMON DW

ARCHITECTURE AND

INFRASTRUCTURE HIGH

LEVEL TRENDS

The amalgamation and realization of BI in DW is a

strong trend, altering in many ways the architecture

and infrastructure of the DW design and

implementation decisions. Fig.1 shows the general

system architecture of today’s medium and large

DW. Though in appears basically as it did years ago,

it is now loaded with solutions to support business

models and processes tracking, management and

optimizing, shown inside the middle DW layer. The

other new trend here is the broadly represented

advanced user interfaces for publishing, data

delivery and self-services, running usually from

corporate portals. They now often encompass the

whole enterprise, giving access to employees,

customer, suppliers and partners. Thus DW

essentially evolved from a decision support tool for

top managers to an IT infrastructure necessity in

many aspects vital for the enterprise (Gorla, 2003),

(Golfarelli, 2004), (Inmon, 2002) and certainly used

by a much broader audience.

The main high level attributes of this architecture are

power, flexibility and scalability. The power comes

from the consolidated DB (“knowledge is power”).

For example with an easy navigation from the

corporate portal any (or designated) employees can

browse the CPM dashboards. In companies with

thousands of employees worldwide this improves

the team efforts and environments. Next trend is the

enriched business analytical support, and ad-hoc, or

scheduled reporting services, available to anyone

from a thin browser client, or through a familiar

spreadsheet interface. The required by the business

flexibility is provided by the consolidated metadata

solution. Business rules change, processes improve,

and targets are re-defined frequently “on the go”. If

properly designed and propagated, the metadata

repository will effectively accommodate these

changes and enforce them to downstream

applications and services. The architectural

scalability is a factor on the ETL, DBMS and server

levels, shown in Fig.1. A DW configuration with

one DB server with one central metadata repository,

an application server to run all the tools and a Web

server for the user interface may be adequate for

medium scale businesses. For large global

businesses a distributed DW Architecture utilizes

several Data Marts with separate metadata

repositories and separate, or common ETL. The idea

here is to “tune” the individual metadata in every

Data Mart to the business requirements, defined by

business subjects, departments, or regions in order to

achieve adequate reporting and data mining

performance.

ICEIS 2006 - DATABASES AND INFORMATION SYSTEMS INTEGRATION

298

RDBMS

Extract

DW Management

and Administration

Refresh

Load

Clean

Transform

Data

Mining

Reporting

Services

OLAP

services

Data Mart Data Mart Data Mart

Scorecards

Dashboards

Alerts

METADATA

Employees

Customers

Suppliers

Partners

Legacy DB

Operational

Applications

Web Sites

Flat Files

Handhelds

SOURCES

ETL DISTRIBUTED OR CONSOLIDATED DB UI USERSSERVICES

APP.

SERVER

WWW

SERVER

CORPORATE

PORTALS

Figure 1: General DW system architecture.

The best Architectural and Infrastructure strategy

should match the present and forecasted business

requirements with the available project resources

(Chadhuri , 2004), (Imhoff , 2003). The known

approaches with clusters, federated DBMS, table

partitioning, load balancing Web farms etc. are all

applicable here, depending on the project budget.

4 CURRENT BI RELATED

TRENDS IN DW

4.1 Providing Data Quality in DW

Perhaps the most important trends in DW are the

well founded provisions for data quality. The rule

that “business is as good as its corresponding data”

has strong practical implications on DW. The DW

Institute estimates (www.knightsbridge.com, 2005)

the business losses due to poor data quality about

$600 bln. per year in USA.

The sources of poor data quality in DW are

numerous: data input errors, loading and

consolidation errors, conversion errors, DB integrity

errors, reporting exception errors etc. In business

processes such poor data can result in errors like

wrong customer order generation, incorrect invoices,

duplicate payments etc. The effects are low

efficiency employment and unhappy customers, i.e.

losses and low profit. On the executive level low

quality data can result in non adequate tactical and

strategic decision making and in inaccurate

performance estimations.

The data quality can be generally defined as

compliance to the particular set of IT and business

requirements. The best practices here are to

designate a person, or a team, responsible for the

data quality management and to specify the

requirements, usually in several improving cycles

(Leahy, 2004). Reviews show that many businesses

still do not have clearly defined, enforced and

managed data quality requirements. This is the

driving force behind the fast market growing for

data quality products and services (Geiger , 2005).

In some cases the DW quality requirements are

implemented as validation and consolidation rules

on the DW system ETL level, as well as on the

reporting and data mining levels. However the most

flexible and scalable way is the practice to include

and manage the data quality requirements on the DB

level as part of the metadata. There are many

benefits with this approach – end to end system

coverage, central management,, scheduled/on

demand cleansing and easy adding of new data

quality procedures, which all combined give cost

effectiveness.

The conclusion for this trend is that the DW data

quality assurance is a critical enterprise IT

component.

4.2 Regulatory Compliance and DW

Regulatory compliance brings new requirements to

both DW and BI in terms of certified financial

results, privacy protection, risk declaration, disaster

recovery, environmental protection etc. Recent

examples are legislation like the Sarbanes-Oxlley

(SOX) Act in USA, the Health Information Privacy

Act (HIPA) in Canada, the Basel II banking accord

requirements in Switzerland and alike. Demanding

data policies and special new business processes are

required to achieve compliance (Geiger, 2005),

(www.knightsbridge.com, 2005). These processes

and policies affect the DW as mandatory new

additions and improvements are needed in the DW

system and data change control, data quality

assurance, accelerated warning financial reports and

non-compliance monitoring by designated officer, or

a team. A part of these processes is the data quality

assurance, as considered in p. 4.1.

Whether the DW requirement changes are

significant, or not, when a certain business, as a

result of its activities, is affected by one, or several

legislative regulations, they can not be ignored and

the compliance should be provided by the required

processes.

4.3 DW Infrastructure

Consolidation and

Standardization

Many companies today are undertaking

infrastructure consolidation and standardization in

order to reduce and effectively manage the IT cost

(Glick , 2005). This is an ongoing trend after year

2000. This trend fully affects the DW systems, as by

their nature they grow, expand and increase in cost

CURRENT TRENDS IN DATA WAREHOUSING METHODS AND TECHNOLOGIES

299

in all terms of hardware spending/amortization,

software purchase and licensing and

managing/support cost. The consolidation should be

very well planned and leveraged by such goals and

provisions for system and data growth, timely query

and reporting responses, simplified and low

operational cost management and support. These

should be combined IT and business efforts.

On the operating system level the new virtual

servers technologies (Glick, 2005) allow

accommodation of large heterogeneous DW with

servers, running Windows, Unix, or Linux in a very

flexible consolidated hardware environment. The

administration, managing, support and versioning

costs are greatly reduced. On the DB level, it pays to

reconsider the consolidation of old and legacy data

marts and ETL tools with centralized approach and

streamlined new ETL processing tools.

The business trend is that calls for consolidation are

coming not only as a result from acquisitions and

mergers. It also comes from the understanding that

the latest technologies as federated DBMS servers,

web farms, network storage devices etc. allow

alignment of the BI processes with DW architecture

in a very cost effective manner with excellent ROI.

If properly designed and executed the consolidation

and standardization bring significant savings, up to

billions of dollars in many cases (Glick, 2005),

(Violino, 2005).

4.4 Corporate Performance

Optimization using DW

As a consolidated foundation of the enterprise

business data, the DW can be well designed for

loading, reporting and data mining. That is why it is

the ideal “natural” source and platform for corporate

performance measurement (CPM), management and

optimization, which makes this important usage of

DW a well recognized trend

(www.intelligentbusiness.biz,2005). The generalized

functional processes of CPM, based on DW, are:

- monitor financial and non financial results

on demand;

- - link strategies to day to day operational

activities;

- - configure and setup early warnings about

problems;

- - achieve accord with regulatory

compliance;

- - broaden decision making by modelling

new business scenarios.

The above functions comprise the BI essentials in

using DW for CPM. The emerging trend here is to

use DW for both strategic and operational decisions,

affecting back the DW performance requirements.

This allows the CPM activities to cover the

optimizing of all departments like customer

relations, financial operations, risk management,

sales, planning and supply chains.

The strategic and tactical models , used in CPM

evolved together with DW and BI. The most widely

recognized and developed to advanced stage are:

The Balanced Scorecard , Six Sigma and Malcolm -

Baldridge (Solomon, 2003) and others. The

implementation of similar models goes to the

business bottom line and core values and properly

applied sometimes brings dramatic improvements

with proven savings. This is a huge area and alone

may be a subject for a study. All DBMS vendors

include balanced scorecard and dashboard tools in

their DW solutions: Oracle in 10G, Microsoft in

SQL Server 2005, IBM in DB2 etc. Also, many

integration software vendors offer universal add-on

CPM frameworks (Violino, 2005), which could be

rapidly implemented – from days to weeks – on top

of existing DW, for example (Business Objects,

2005), (Amateo Data Warehouse, 2005) , and many

others. These tools get their data feed directly from

the DW, ensuring validated, consisted and timely

CPM.

Therefore in the case of newly projects, the CPM

will most likely come as a part of the rollout, while

in case of a relatively older implementations it will

be an added-on framework.

4.5 Metadata Management in DW

Considered the most important component of the

DW, the metadata essential role as definitive

information about data covers the entire contents of

DB, ETL, the reporting data mining, in other words

all data in DW and many, if not all processes. That is

why the DW metadata requires an careful

management. The functional groups of the metadata

could be conditionally divided as administrative,

business and operational. The administrative

includes: the definitions of setting and using the DW

itself, such as the DW schema, the sources DB

schemas, ETL rules, prepared queries and reports,

user profiles, access roles and control, data quality

procedures, multilingual support etc. The business

metadata includes the business rules and terms,

definitions and processes. The operational metadata

includes the structure of the DW monitoring,

including logs, audit trails, prepared archiving

packages of scripts, replication etc.

The management of the metadata encircles such

functions of sharing the metadata for design, setting,

using and operating across internal and external

ICEIS 2006 - DATABASES AND INFORMATION SYSTEMS INTEGRATION

300

applications, outside of DW; cleaning, normalizing,

restructuring, updates and publishing of catalogs. All

DW big vendors Oracle, Microsoft, IBM, Terradata,

Sybase et al. invest heavily in metadata management

and offer their integrated repository systems. There

is also a market for infrastructure integration

vendors like Informatica, CA, SAP, Platinum, Prism

et al.

4.6 Other New trends in DW

The following trends in DW will be considered in

details in the second part of this work:

- disaster recovering processes and practices;

- - new hardware technologies and devices in

DW;

- - new data mining methods and

technologies in DW;

- - efficient DW and metadata design;

- - outsourcing and DW as a paid for

services;

- - open source low price DW solutions.

5 CONCLUSION

We can now summarize the most important DW

trends, considered in this work:

-The DW methods and technologies track, support

and optimize more and more business processes;

than before;

-The amalgamation of BI in DW affects the

requirements to DW architecture design and

implementation, bringing more operational

requirements;

-DW is evolving from a decision support tool for top

managers to an IT infrastructure necessity for the

whole enterprise;

- Additional research is needed to analyze the

influence of new technologies in hardware, data

mining, outsourcing and open source movement on

DW.

REFERENCES

Agosta L. A Nov.2004. Time of Grouth for Data

Warehousing., DM Review

Overview of Data Warehousing in SQL Server 2005,

www.microsoft.com.

Singh C.,Sept.2005. Enterprise Datawarehousing is next

big thing, Economic Times.

Gorla N. 2003. Features to consider in a Data

Warehousing Systems. Comm. of the ACM, Nov.

2003, vol 46, No11.

Chadhuri S., Dayal U., 2004. An Overview of data

warehousing and OLAP Technology.

Geiger J., October 2005. Intelligent Solutions: You can be

sake if it is been certified. DM review magazine.

Golfarelli M. e.a., Nov. 2004. Beyond Data Warehousing:

What’s next in Business Intelligence? DOLAP’04.

Data Quality management. Executive Brief, ACC Services

Ltd., www.acl.com

Sen A., Sinha A., 2005. A comparison of Data

warehousing Methodologies. Comm. of the ACM,

March, V.48. No3

Demarest G., April 2005. Zen and the Art of Information,

Oracle Magazine.

Oracle Business Intelligence Warehouse Builder 10G,

www.oracle.com.

Knightbridge Solutions, 2005. Top ten trends in BI and

Data Warehousing for 2005,

http://www.knightsbridge.com

Overby S. , Nov.2003 41 Business Units, 9 CIOs, 1

Standard, CIO Magazine.

Inmon W. A history of database evolution, www.b-eye-

network.com

Riccardi E. , July 2005 Balanced Scorecard and its

information systems: The performance Data

Warehouse. SASE.

Leahy T. , April 2004. Better Data, Better Performance

Measurement, Business Finance.

Glick B. , October 2005. Measuring IT value

methodologies, Computing Business.

Violino B., March 2005. Frameworks boost business

efficiency. Optimize Mag.

Kamram N., Process Warehouse – The missing link in

Business Performance Management,

www.datawarehouse.com

Solomon M., Nov. 2003. Linking BUsiness INtelligence

to the balanced scorecard., TDWI lessons, v.16

White C., 2005. Corporate Performance Optimization

Guide, www.intelligentbusiness.biz.

Inmon W., 2002. Building the Data Warehouse, John

Willey & Sons.

Inmon W. e.a., 2001. Data Warehousing for E-Business,

John Willey & Sons.

Imhoff C e.a., 2003. Mastering Datawarehouse Design,

John Willey & Sons.

Westermann P., 2001 Data Warehousing: Using the Wal-

Mart Model, Academic Press.

MIT’s Data Warehouse, http://web.mit.edu/warehouse,

2005.

Business Objects Data Warehouse, 2005,

http://www.businessobjects.com.

Amateo Data Warehouse. 2005, http://www.amateo.com.

CURRENT TRENDS IN DATA WAREHOUSING METHODS AND TECHNOLOGIES

301