Online Analytical Processing Data Driven Decision Support System Paper


Question Description

Requirement and case attached below , 1.5 page, size 12, 1.5 space.

Unformatted Attachment Preview

Automation in Construction 12 (2002) 213 – 224 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: (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

Tutor Answer

School: Boston College




Institutional Affiliation



Decision Type
Online Analytical Processing also known as OLAP is a calculating mechanism which makes it
possible for its users to more efficiently and carefully question and excerpt data for the purposes
of investigating it from diverse viewpoints. Online Analytical Processing business intelligence
questions usually help in forecasting of sales, making financial reports, analyzing of business
trends, budgeting and other business planning aspirations (Zhang, J. 2003).
Online Analytical Processing (OLAP) can enable a user to be able to view the data, whereby, the
data can be displayed in a spreadsheet displaying all of the organization products that were sold
in a certain region like in the month of March, and OLAP will enable the user to be able to
contrast the profits of that month with the profits of the same product in the month on August
and then be able to see a difference of the sales of other products in the same region around that
time period.
User Type
The users who benefit from the use of Online Analytical Processing (OLAP) are people such as,
organization managers, company directors and also company supervisors. This is because, the
functions of Online Analytical Processing (OLAP) are making financial reports in the
organization, making budgets, help in the forecasting of sales, analyze the trends of business and
making financial reports, activities which are mostly done by the top most managers who run the
organization. Online Analytical Processing (OLAP) does greatly assist company directors and
managers, because it assists them on how to make very critical and crucial decisions concerning
the organization which may result to the organizations success and even failure sf not done
correctly and Online Analytical Processing (OLAP) also helps to determine the organizations
future (Chau, K. W. 2003).
System Type
The Online Analytical Processing (O...

flag Report DMCA

I was on a very tight deadline but thanks to Studypool I was able to deliver my assignment on time.

The tutor was pretty knowledgeable, efficient and polite. Great service!

I did not know how to approach this question, Studypool helped me a lot.

Similar Questions
Related Tags

Brown University

1271 Tutors

California Institute of Technology

2131 Tutors

Carnegie Mellon University

982 Tutors

Columbia University

1256 Tutors

Dartmouth University

2113 Tutors

Emory University

2279 Tutors

Harvard University

599 Tutors

Massachusetts Institute of Technology

2319 Tutors

New York University

1645 Tutors

Notre Dam University

1911 Tutors

Oklahoma University

2122 Tutors

Pennsylvania State University

932 Tutors

Princeton University

1211 Tutors

Stanford University

983 Tutors

University of California

1282 Tutors

Oxford University

123 Tutors

Yale University

2325 Tutors