Online Analytical Processing Data Driven Decision Support System Paper Requirement and case attached below , 1.5 page, size 12, 1.5 space. Automation in Co

Online Analytical Processing Data Driven Decision Support System Paper Requirement and case attached below , 1.5 page, size 12, 1.5 space. Automation in Construction 12 (2002) 213 – 224
www.elsevier.com/locate/autcon
Application of data warehouse and Decision Support System
in construction management
K.W. Chau a,*, Ying Cao b, M. Anson a, Jianping Zhang b
a
Department of Civil and Structural Engineering, Hong Kong Polytechnic University, Hunghom, Kowloon, Hong Kong, China
b
Department of Civil Engineering, Tsinghua University, Beijing 100084, China
Accepted 8 August 2002
Abstract
How to provide construction managers with information about and insight into the existing data, so as to make decision
more efficiently without interrupting the daily work of an On-Line Transaction Processing (OLTP) system is a problem during
the construction management process. To solve this problem, the integration of a data warehouse and a Decision Support
System (DSS) seems to be efficient. ‘Data warehouse’ technology is a new database discipline, which has not yet been applied
to construction management. Hence, it is worthwhile to experiment in this particular field in order to gauge the full scope of its
capability. First reviewed in this paper are the concepts of the data warehouse, On-Line Analysis Processing (OLAP) and DSS.
The method of creating a data warehouse is then shown, changing the data in the data warehouse into a multidimensional data
cube and integrating the data warehouse with a DSS. Finally, an application example is given to illustrate the use of the
Construction Management Decision Support System (CMDSS) developed in this study. Integration of a data warehouse and a
DSS enable the right data to be tracked down and provide the required information in a direct, rapid and meaningful way.
Construction managers can view data from various perspectives with significantly reduced query time, thus making decisions
faster and more comprehensive. The applications of a data warehousing integrated with a DSS in construction management
practice are seen to have considerable potential.
D 2002 Elsevier Science B.V. All rights reserved.
Keywords: Decision Support System; Project management; Construction; Data warehouse; On-Line Analysis Processing
1. Introduction
1.1. Using Decision Support System (DSS) in
construction management
At present, some transaction processing systems,
which are updated continually throughout the day, are
* Corresponding author. Tel.: +852-2766-6014; fax: +8522334-6389.
E-mail address: cekwchau@polyu.edu.hk (K.W. Chau).
often employed to run the day-to-day business of a
construction company [1]. For instance, if some
materials are delivered into the warehouse, the OnLine Transaction Processing (OLTP) will consistently
make additions to the inventory. However, it is usually
found in such systems that the construction process is
a ‘‘temporary’’ and ‘‘specific’’ activity, which means
the data of one project can seldom be used for another
project. Is that the true situation? Probably not,
because although construction products are ‘unique’,
some similarities still exist between them, and con-
0926-5805/02/$ – see front matter D 2002 Elsevier Science B.V. All rights reserved.
PII: S 0 9 2 6 – 5 8 0 5 ( 0 2 ) 0 0 0 8 7 – 0
214
K.W. Chau et al. / Automation in Construction 12 (2002) 213–224
struction processes and management skills are typically common to all projects [2]. How to analyze the
successes and failures of finished projects and how to
use the existing data to analyze patterns and trends for
new projects are the problems we have to face.
During the project control phase, in order to take
rectifying actions for any deviations in the performance, project managers often need timely analysis
reports to measure and monitor construction performance. They also need timely analysis reports to assist
in making long-term decisions [3]. It is found that
most of the reporting and analysis, time was spent on
collecting data from the various systems before the
analysis can be made. Managers want and need more
information, but analysts can provide only minimal
information at a high cost within the desired time
frames [4]. In order to provide information for predicting patterns and trends more convincingly and for
analyzing a problem or situation more efficiently, an
integrated Decision Support System (DSS) designed
for this particular purpose is needed.
An important role of a Decision Support System is
to provide information for users to analyze situations
and make decisions. Put in another way, a Decision
Support System provides information for employees
to make decisions and do their jobs more effectively
[5]. This decision-making can be of a long-term
strategic nature, such as analyzing event patterns over
several years to prevent or reduce the rate of occurrence of a particular event. Decision-making can also
be short-term and tactical in nature, such as reviewing
and changing the time schedule for a particular part of
a project. Good systems provide the information
needed, so that employees are better equipped to
make more informed decisions. Described in this
paper is the development of a prototype Decision
Support System, employing the new ‘data warehouse’
technology incorporating large quantity of analysis
information needed for both long-term and short-term
construction management decision-making.
appropriate data is available to the appropriate end
user at the appropriate time.
A data warehouse is a global repository that stores
preprocessed queries on data, which reside in multiple, possibly heterogeneous, operational query base
for making effective decisions [5]. The contents of a
data warehouse may be a replica of part of some
source data or they may be the results of preprocessed
queries or both. This method of data storage provides
a powerful tool-helping project organizations in making decisions. The architecture of a data warehousing
system allows a number of alternative ways to integrate and query (such as previous or projected)
information stored in it. Thus, a data warehouse
coupled with On-Line Analysis Processing (OLAP)
enables project managers to creatively approach, analyze and understand project problems. The data warehouse system is used to provide solutions for
construction problems, since it transforms operational
data into strategic decision-making information. The
data warehouse stores summarized information
instead of operational data. This summarized information is time-variant and provides effective answers
to queries such as ‘‘What are the supply patterns and
trends of various construction materials?’’, ‘‘How is
the material consumption this year different from its
counterpart last year?’’, ‘‘How many accidents happened in the last 10 years and how much did they
cost?’’, ‘‘What is the percentage increase in the cost of
human resources during the last 5 years?’’, ‘‘Did
machine repair have any influence on construction
progress? If so, what was the influence coefficient?’’
and so on. To extract this information from a distributed relational model, we would need to query
multiple data sources and integrate the information at
a particular point before presenting the answers to the
user. In a data warehouse, such queries find their
answers in a central place, thus reducing the processing and management costs.
1.3. What is new in our system?
1.2. Using a data warehouse to support a DSS
Being a new branch of the database community
developed in recent years [6], the ‘data warehouse’ is
a read-only analytical database that is used as the
foundation of a Decision Support System. The purpose of a data warehouse is to ensure that the
As a matter of fact, Decision Support Systems have
been applied in construction management for several
years. The early systems such as management information systems, report-oriented systems and so on are
often born with flaws [7]. Firstly, they are not separated from transaction systems completely and the
K.W. Chau et al. / Automation in Construction 12 (2002) 213–224
sharing of a database or data file slows down either
transaction or analysis process. Secondly, because of
the limitations of a relational database, users can only
observe their data from flat views. Thirdly, these
applications are all developed by computer specialists
in information centers after lengthy data analysis, but
sometimes not all the requirements of construction
managers are embodied sufficiently. These problems
could be solved in the Construction Management
Decision Support System (CMDSS) developed in this
study. The main characteristic of CMDSS is the
separation of the analysis database from the operational database, which renders the decision support
process much faster. Another advance is the use of
OLAP, which changes the data in a relational database
into multidimensional cubes that could be observed
from all perspectives. In addition, visualization (use of
graphic and presentation techniques) presents data
from several kinds of views. In CMDSS, moreover,
users could do a lot more on their own without
computer experts preprogramming everything for
them. Because of these advances, construction managers can make decisions more efficiently, which is
the key objective of our system.
2. System design
2.1. Design data warehouse for Decision Support
System
Existing data models used to design traditional
OLTP systems may not be appropriate for modeling
complex queries under a data warehouse environment.
The transactions in OLTP systems are made-up of
simple, predefined queries. For example, if we want to
know the latest arrival date of all materials in all
warehouses, we can use a Structural Query Language
(SQL) query such as SELECT flngMaterialID,
MAX(fdtmArriveDate) AS Date, flngMaterialType,
flngDepotID FROM tb_ Stock GROUP BY flngMaterialID, flngMaterialType, flngDepotID HAVING
(flngMaterialType = 2). In the data warehouse environments, queries tend to use connections between
tables and have a longer computation time, such as
Select Material.MaterialName, Material.MaterialKind, Inventory.Quantity from Material, Inventory,
Material INNER JOIN Inventory ON Material.Mate-
215
rialID = Inventory.MaterialID. The above query provides the name and type of materials, as well as the
quantity in the inventory. Since the information on
materials and inventory are put in different tables, a
connection between table ‘Material’ and table ‘Inventory’ is required. This kind of processing environment
warrants a multidimensional data model, a new perspective on data modeling.
The conceptual multidimensional data model can
be physically realized in two ways: (1) by using a
trusted relational database approach (star schema/
snowflake schema) or (2) by making use of a specialized multidimensional database. The ‘star’ schema is
adopted here mainly because of its clarity, convenience and rapid indexing ability [8]. The other methods are not so suitable here, since they involve more
or less much more complicated transformation, which
does not appear to be justified in our situation.
In concise term, a star schema can be defined as a
specific type of database design used to support
analytical processing, which includes a specific set
of denormalized tables. A star schema contains two
types of tables: fact tables and dimension tables. Fact
tables contain the quantitative or factual data about a
construction management entity. Dimension tables
are smaller and hold descriptive data that reflect the
dimensions of an entity. SQL queries then use predefined and user-defined links between the fact
and dimension tables within the star schema, with
constraints on the data to return required information.
A typical material inventory model with sample
dimensions and properties is shown in Table 1 for
CMDSS developed in this project.
The core part of any star schema is the fact table,
which is shown as Table 2.
Now, the whole star schema model could be
created. There is one material inventory fact table
and five dimension tables shown in Fig. 1. These
dimension tables are connected with the fact table by
foreign keys (FK), which can keep all the views
coherent.
Besides the inventory star schema, the example
given above, several other star schemas are designed
in our system, including material issuing, material
balance, material use, machine cost, machine use,
machine repair, human resource use, salary, progress,
noncompliance, event, etc. Each star schema includes
216
K.W. Chau et al. / Automation in Construction 12 (2002) 213–224
Table 1
Dimension and properties
Time dimension
Material dimension
Warehouse dimension
Supplier dimension
StoreKeeper dimension
Time_key
Date
Year
Month
Day
Quarter
Day of week
Material_key
Name
Type
Spec
Unit
Warehouse_key
Name
Position
Structure Type
Content Type
Management Fee
Name
Type
Street Address
City
Province
Country
Postal Code
Telephone
Fax
Email
Name
Birthday
Joining Date
Gender
a fact table and several dimension tables, just like the
inventory star schema.
In addition to the data model design, several other
steps should be taken before the data warehouse can
be completed. These steps are as follows:

Data is extracted from the source systems, databases and files.
Data from the source systems is integrated.
Data is loaded into the data warehouse.
Data is transformed into the format that can be
used by the front – end tool.
The process of a data warehouse design is shown
in Fig. 2.
In CMDSS, the ‘‘Import and Export Data’’ tool is
used to integrate data from distributed OLTP databases, files, etc.
With a view to transform the fact table and dimension tables in the star schema designed above into a
multidimensional cube that can be further explored by
the front – end tools such as Visual Basic, MS Access,
MS Excel, the OLAP tool is applied here. Microsoft
OLAP Services is based on and tightly linked to
relational databases. However, it is a real multidimensional information system, where all information is
modeled in terms of OLAP structures, not relational
structures [7]. The OLAP structures are a valuable
feature because many important analyses are difficult
or impossible to phrase in SQL using tabular structures. For example, one characteristic of most OLAP
applications is the need to provide fast access to
aggregated source data. Precalculating all possible
aggregations can lead to a tremendous increase in
the storage requirements for the database, while cal-
culating all aggregations on each occasion makes for a
slow query response time. The approach taken by
OLAP Services is to precalculate some of the possible
aggregate data values, and to leave any remaining
aggregation and all other calculations to be completed
at query time. Microsoft OLAP Services provides a
relatively well optimized solution [9].
In this case, cubes, dimensions, measures, hierarchies, levels and cells constitute the basic OLAP
structures. These, taken together, define the logical
structure of an OLAP database. A data cube is a
structure for housing multidimensional data. Everyone
using a data warehouse will use cubes when analyzing
the data. Measures are the data that we wish to
analyze, while dimensions define the organization of
these measures [10]. Our data warehouse may contain
an inventory table that has fields for location, time,
material, supplier, storekeeper, price, quantity and
total amount. If so, we will generally analyze price,
quantity and total amount by warehouse, time, material, supplier and storekeeper. In this case, price,
quantity and total amount will be our measures, and
warehouse, time, material, supplier and storekeeper
will each be a dimension. The elements of a dimension are called members. The path to organize mem-
Table 2
Fact table
Foreign Key
Measures
Material_key
Time_key
StoreKeeper_Key
Warehouse_key
Supplier_key
Price
Quantity
Total Amount
K.W. Chau et al. / Automation in Construction 12 (2002) 213–224
217
Fig. 1. Material inventory star schema.
bers in a cube is called hierarchy. For instance, the
time dimension may be organized in two hierarchies:
natural time hierarchy and fiscal time hierarchy. In the
former one, time may be organized in year, quarter,
month and date. While in the latter one, time may be
organized in fiscal month and fiscal week. A level
refers to a group of related members which share a
common meaning. For example, a level construct
named ‘month’ may contain all of the month-level
members in a time dimension. Each unique intersection composed of one member from every dimension
in the cube is called a cell [10]. For instance, the
intersection of July (member of Time) and Hong
Kong (member of Geography) constructs one cell,
and the value of the cell could either be measure, such
as price, quantity or total amount.
Microsoft OLAP Services is strongly based on
relational databases. All dimension levels and cube
measures need to correspond to columns of tables,
views or queries. They can be in many different tables
or all in one table, so long as dimension tables and
fact tables can be joined in a single query. OLAP
Services uses a highly declarative linkage among
dimension, cube structures and Relational Database
Management System (RDBMS) tables. Once the links
are created, OLAP Services will form all queries on
the linked tables and manipulate all query results.
Many cubes about material, machine, human
source, progress, quality and event are created in
our system on the basis of the star schemas designed
before. New multidimensional cubes can be added at
any moment by the users as the need arises. An
‘Inventory’’ cube created in our system is shown in
Fig. 3.
The major operations that could be done on OLAP
cubes are Selection, Roll-up, Drill-Down and Slice,
through which we can view data from all perspectives
and all levels [11].
Fig. 2. Process of data warehouse design.
218
K.W. Chau et al. / Automation in Construction 12 (2002) 213–224
Fig. 3. Inventory cube in Microsoft OLAP.
2.2. Design of DSS for construction management
The following fundamental questions should be
first understood prior to the design of a DSS: How
are reports and analyses shared between users? How
are structured navigation paths or command buttons
created? Is there a query controller to limit the
allowable elapsed time run for a query or to limit
the total number of rows that can be returned? Is there
an ability to run a query during off-peak hours to save
costs?. . .. These questions are quite important for
deciding the aim and the direction of a DSS, which
also means the success of it.
The purpose of the DSS is to enable analysts to
extract information quickly and easily. The data being
analyzed are often historical in nature: daily, weekly
and yearly results. For this reason, the System Development Life Cycle (SDLC) for DSS is quite different
from an On-Line Transaction Processing (OLTP)
system. The SDLC of a common OLTP system will
usually go through several phases: planning, analysis,
design, development, testing and implementation. The
focus of a DSS is data, not construction processing
and their associated functionality. This lack of domain
functionality implies a much faster development life
cycle.
Under the DSS application, a front – end tool is
employed to create predefined reports to accommodate the need for different levels of users to have
prebuilt reports to begin their analysis [5]. Hence, the
general data access processes are visualization of the
data warehouse, formulation of the request, processing the request and presentation of the results. After
the data warehouse design and OLAP transformation,
two steps are left to create a DSS application. The first
is to design the front –end interface. The second is to
generate codes to access and navigate metadata to
obtain information on the data in the warehouse, and
link it together with the front – end interface.
A major consideration in designing the DSS interface is the leve…
Purchase answer to see full
attachment