DATA WAREHOUSE: THE BUILDING BLOCKS
In operational systems, we store data by individual applications. In the data sets for an order
processing application, we keep the data for that particular application. These data sets
provide the data for all the functions for entering orders, checking stock, verifying customer’s
credit, and assigning the order for shipment. But these data sets contain only the
data that is needed for those functions relating to this particular application. We will have
some data sets containing data about individual orders, customers, stock status, and detailed
transactions, but all of these are structured around the processing of orders.
Similarly, for a banking institution, data sets for a consumer loans application contain
data for that particular application. Data sets for other distinct applications of checking
accounts and savings accounts relate to those specific applications. Again, in an insurance
company, different data sets support individual applications such as automobile insurance,
life insurance, and workers’ compensation insurance.
In every industry, data sets are organized around individual applications to support
those particular operational systems. These individual data sets have to provide data for
the specific applications to perform the specific functions efficiently. Therefore, the data
sets for each application need to be organized around that specific application.
In striking contrast, in the data warehouse, data is stored by subjects, not by applications.
If data is stored by business subjects, what are business subjects? Business subjects
differ from enterprise to enterprise. These are the subjects critical for the enterprise. For a
manufacturing company, sales, shipments, and inventory are critical business subjects.
For a retail store, sales at the check-out counter is a critical subject.
Integrated Data
For proper decision making, you need to pull together all the relevant data from the various
applications. The data in the data warehouse comes from several operational systems.
Source data are in different databases, files, and data segments. These are disparate applications,
so the operational platforms and operating systems could be different. The file
layouts, character code representations, and field naming conventions all could be different.
In addition to data from internal operational systems, for many enterprises, data from
outside sources is likely to be very important. Companies such as Metro Mail, A. C.
Nielsen, and IRI specialize in providing vital data on a regular basis. Your data warehouse
may need data from such sources. This is one more variation in the mix of source data for
a data warehouse.
Time-Variant Data
For an operational system, the stored data contains the current values. In an accounts receivable
system, the balance is the current outstanding balance in the customer’s account.
In an order entry system, the status of an order is the current status of the order. In a consumer
loans application, the balance amount owed by the customer is the current amount.
Of course, we store some past transactions in operational systems, but, essentially, operational
systems reflect current information because these systems support day-to-day current
operations.
On the other hand, the data in the data warehouse is meant for analysis and decision
making. If a user is looking at the buying pattern of a specific customer, the user needs
data not only about the current purchase, but on the past purchases as well. When a user
wants to find out the reason for the drop in sales in the North East division, the user needs
all the sales data for that division over a period extending back in time. When an analyst in
a grocery chain wants to promote two or more products together, that analyst wants sales
of the selected products over a number of past quarters.
A data warehouse, because of the very nature of its purpose, has to contain historical data, not just current values. Data is stored as snapshots over past and current periods.Every data structure in the data warehouse contains the time element. You will find historical snapshots of the operational data in the data warehouse. This aspect of the data warehouse is quite significant for both the design and the implementation phases.
For example, in a data warehouse containing units of sale, the quantity stored in each
file record or table row relates to a specific time element. Depending on the level of the
details in the data warehouse, the sales quantity in a record may relate to a specific date,
week, month, or quarter.
The time-variant nature of the data in a data warehouse
- Allows for analysis of the past
- Relates information to the present
- Enables forecasts for the future
Non-Volatile Data
Data extracted from the various operational systems and pertinent data obtained from
outside sources are transformed, integrated, and stored in the data warehouse. The data
in the data warehouse is not intended to run the day-to-day business. When you want to
process the next order received from a customer, you do not look into the data warehouse
to find the current stock status. The operational order entry application is meant
for that purpose. In the data warehouse, you keep the extracted stock status data as snapshots
over time. You do not update the data warehouse every time you process a single
order.
Data from the operational systems are moved into the data warehouse at specific intervals.
Depending on the requirements of the business, these data movements take place
twice a day, once a day, once a week, or once in two weeks. In fact, in a typical data warehouse,
data movements to different data sets may take place at different frequencies. The
changes to the attributes of the products may be moved once a week. Any revisions to geographical
setup may be moved once a month. The units of sales may be moved once a
day. You plan and schedule the data movements or data loads based on the requirements of
your users.
Data Granularity
In an operational system, data is usually kept at the lowest level of detail. In a point-of sale
system for a grocery store, the units of sale are captured and stored at the level of
units of a product per transaction at the check-out counter. In an order entry system, the
quantity ordered is captured and stored at the level of units of a product per order received
from the customer. Whenever you need summary data, you add up the individual transactions. If you are looking for units of a product ordered this month, you read all the orders
entered for the entire month for that product and add up. You do not usually keep summary
data in an operational system.
When a user queries the data warehouse for analysis, he or she usually starts by looking
at summary data. The user may start with total sale units of a product in an entire region.
Then the user may want to look at the breakdown by states in the region. The next
step may be the examination of sale units by the next level of individual stores. Frequently,
the analysis begins at a high level and moves down to lower levels of detail.
In a data warehouse, therefore, you find it efficient to keep data summarized at different
levels. Depending on the query, you can then go to the particular level of detail and
satisfy the query. Data granularity in a data warehouse refers to the level of detail. The
lower the level of detail, the finer the data granularity. Of course, if you want to keep data
in the lowest level of detail, you have to store a lot of data in the data warehouse. You will
have to decide on the granularity levels based on the data types and the expected system
performance for queries.
