In the modern information age it is imperative for
the organization to have its information at its fingertips. To meet
this need for information, additions to systems are often developed
to give consolidated management overviews of the operational
information. Often, consolidated views require information from
several operational systems and this leads to separate systems,
often referred to as MIS (management information systems) or
decision support systems. Sometimes the consolidated management
information is represented in a specialized system often referred
to as a data warehouse.
A definition: A data warehouse is a system that contains the
data of an organization and is structured specifically for query,
analysis and reporting.
In this particular philosophy the key to this
definition is the words, "structured specifically", which implies
that the way the information is stored in a data warehouse is
different from normal systems. The primary reason for this is that
we need data models that can speed up querying and
reporting.
The technique used for modelling data warehouses
is called multidimensional modelling and uses a particular data
construct known as a star schema. The star schema (sometimes
referenced as star join schema) is the simplest data warehouse
schema where a single "fact table" containing a compound primary
key, with one segment for each "dimension" and with additional
columns of additive, numeric facts. A fact table consists of the
measurements, metrics or facts of a business process. It is often
located at the centre of a star schema, surrounded by dimension
tables. A dimension table is one of the set of companion tables to
a fact table and normally contains attributes or (fields) used to
constrain and group data when performing data warehousing
queries.
Modelling data warehouses is a daunting task and
often focuses on establishing a data model only. Often the user
requirements are not formalized in a specification. The rest
of this article will be dedicated to discussing the analysis tasks
to be performed in establishing the requirements and initial models
for the data entities.
Rambling: A sales manager has the requirement to have a
small decision support system in the form of a data mart. To
support this requirement she needs to report on sales per customer,
per region, per organization, per location, per product, per time
period, or any combination of the above.
Note. For simplicity, we are excluding the ETL
(extraction, transformation and load) from the scope of the
model:
Simple approach
Step one: Define the scope of the data
warehouse.
The data warehouse at the highest level will
always have the following components:
-
Manage
reporting – This typically includes all of the end user
functionality whereby reports and queries may be
managed
-
Maintain
data warehouse – The maintenance of the warehouse focuses on
the loading of information into the data
warehouse
-
Administer data warehouse – This includes
all of the tasks necessary for a data warehouse administrator to
manage the data warehouse
At the highest level the data warehouse may be
represented as follows in a package diagram:
Figure 1: Package
diagram
Step two: Per package, define the functions necessary to the
data warehouse
For package: Manage reporting, after discussion
with the sales manager, we have determined that the following
functions are required:
-
Display
sales by:
-
Customer
-
Region
-
Product
-
Organization
-
Time
period
-
Region,
product, organization, customer, time period
-
Optionally a report may be
generated
The use case diagram for this may look like the
following:
Figure 2: Use case
diagram
Alternatively the extended use cases may be
described as scenarios against the base use case: Display
sales.
Step three: from the use case diagram we can then derive the
class diagram and after further consultation with the sales manager
also the attributes and operations. This may lead to the following
class diagram (also applied the star schema using the fact table
and the dimension tables):
Figure 3: Class diagram
Conclusion
Using the steps above a model of the data
warehouse can be created rapidly to be used as a requirements
specification for a commercial product, or for the detailed design
of a data warehouse to be developed in-house.
Making life easy!
References: Data warehouse definitions sourced
from Wikipedia 
