Warehousing for Easy Information Access

In the first part of this series, we talked about the benefits, market
proposition, some technologies involved and the tools that are available in the
market for business intelligence.

This time, we will be taking you into the world of data warehousing and its
structure definitions, various terms and entities associated with it. We will
talk about the data warehouse components and characteristics. We will list all
the challenges that are common while defining data warehouses. We will give you
a hands-on experience of our warehouse implementation.

Why do you need a data warehouse? This is the most common query you face
while convincing others to adopt the technology and implement structured data
system in their organization. The volume of data that one has to deal with in an
organization comprises thousands of accounts, product lists, transaction
details, and inventory and customer requests present in fragmented format (as
databases, files and spreadsheets). Each of these databases, files and sheets
has to be updated on a daily basis in a heterogenous environment of data
sourcing and collection and uploaded to stay active, which is not easy. For
those who implement data warehouses, this data would run into terabytes and
across different locations. So how do you extract any specific bit of data that
you want and within minutes? For this, specific architectures have to be laid
down to ensure data flow of your choice in a warehouse.

A properly configured warehouse has different as well as unique databases
integrated together. So if you want to build a data warehouse, you need a team
of dedicated IT professionals with good understanding of your business so that
they can correlate different data and implement data structures in the most
suitable manner for their organization. This involves categorizing databases
that house data from each department spread across locations and link them
accurately across structure in defined business process. And it's not the end
of exercise once the warehouse is implemented and has gone online. Since a
warehouse is not a static application which once installed or configured will
work painlessly, the team has to modify its properties quite often with every
change in the market conditions and company's management decisions.

Warehouse architectures

In many cases, a data warehouse contains the living history of the organization
while they usually contain historical data, often collected from different
sources such as OLTP (On-Line Transaction Processing) systems, legacy systems,
spreadsheets or even text files. A data warehouse combines this data, cleanses
it for accuracy and consistency, and organizes it for ease and efficiency of
querying. Some definitions of a data

warehouse include several elements such as a data preparation area, the
cleansing process, the database that holds the data warehouse data, and the
tools that organize and present the data to client applications. Other
definitions restrict the data warehouse to the database that contains the data
warehouse data. In large data warehousing applications, data is often segmented
into specialized components, called data marts that address individual
components of the organization. Some definitions consider data marts to be part
of the data warehouse; others consider them to be separate entities. The
intended meaning of the term data warehouse is usually clear from the context in
which it is used.

Critical solutions such as warehouses require a careful analysis of what is
needed and how a subsequent warehousing solution has to be implemented. While
going for a warehouse solution the choice of architecture matters a lot, as it
is to be based on factors such as organization's

infrastructure, business requirements and desired system structure. What
architecture one goes for is also affected by the location of the data marts and
the location of the central control. For example, an organization might go for
an architecture that allows data to be present, distributed and

controlled centrally or even independently. You can have architectures where the
data marts are independent or inter-connected or go for a warehouse that spans
through your organization. The scope here refers to the usage and not physical
location.

Architecture components

The architecture of the warehouse is made up of a set of components with clearly
defined operational and structural boundaries. At the base is what is termed as
Operational Data Sources or Data Sources which communicate with the layer above
them, namely Data Access Layer. This layer serves as an interface between the
data sources and the Data Staging Area. It is responsible for 'cleansing'
the data, ie bringing all the data from different sources to a universal format
before it is passed on to further layers in the warehouse. After this layer
comes the Data Warehouse Layer which is the organization's warehouse. Layers
above this are typically another Data Access Layer for acting as an interface to
processes and applications that are conceptually placed in the top most layer of
the architecture called the Information Access Layer or the User Analysis Layer.

These layers can be considered to be a part of the larger Application
Messaging Layer. This layer is responsible for transfer of information within
itself, ie between the components that make up this layer and also between its
component layers and external data sources like the Metadata.

Implementation

Implementing a data warehouse begins with a thorough phase of analysis of your
business, its requirements and environment, and analyzing shortcomings in the
current BI. Repeating processes iteratively leads to a better understanding of
the overall state of business and tells you where and how much the problem is.
So you need to gather business documents and analyze them to formulate the
decisions aforesaid.

After this, you analyze other requirements which affect running business
processes in the organization. The activities that constitute this phase include

end-user requirement analysis . This revolves around the data needs of a
day-to-day information worker. The functional requirements are evaluated next;
this answers questions such as what new information analyses are needed and what
pitfalls of the current information analysis

systems are, if they exist. The information infrastructure is also evaluated to
find out whether the current infrastructure suffices for the implementation or
an expansion is required.

After all the analyses, comes the warehouse modeling phase. This involves
modeling the warehouse at both logical and physical levels. This modeling is
quite different from the modeling of a relational database. For instance, in
relational database modeling a direct end-user interaction is never a priority
whereas here the modeling is done so that the warehouse is end-user centric.
Unlike a database, the modeling here is done keeping in mind the fact that the
warehouse is being designed for data analysis and not just data storage in a
qualified pre-defined manner.

While these issues are addressed in the conceptual modeling, the physical
modeling looks into aspects such as how the implementation will proceed.

So it is not that hard to configure a warehouse. All it takes is an
initiative to implement the process and take that first step. After that, DTS
takes care of most of the things and you can get structured answers to your
queries.

Anadi Misra and Anubhav Verma