The next step in the project is to create a final project summary for the project sponsors. Use what you learned in the Weeks Two, Three, and Four Individual assignments to create a final summary for
Michelle Smith
Data Warehouse Evaluation
University of Phoenix
01 July 2019
Table of ContentsIntroduction 1
Evaluation strategies 2
SQL Queries 4
Summary of findings 5
References 6
Introduction
This paper looks at the data evaluation strategy of our data warehouse of the “higher education student aid” data. We also look at the specific evaluation queries. The paper will also provide sample results per query and lastly conclude with a summary of findings. With no further ado, let us start with the data evaluation strategy.
Evaluation strategiesWe need to define data warehouse so as to get to know what we are going to perform evaluation strategies on.
According to Begg C and Connolly T (2015) a data warehouse is a “subject-oriented, integrated, time-variant, and nonvolatile collection of data in support of management’s decision making process”.
According to Demarest M (1995) a data warehouse can be evaluated in various areas and they include:
Capacity
Loading and indexing performance
Operational integrity
Reliability and manageability
Client/server connectivity
Query processing performance
We will evaluate out “higher education student aid” data warehouse using the above areas as mentioned by Demarest M (1995)
Capacity
How much storage capacity does our data warehouse has? As we have mentioned, a data warehouse stores a huge volume of data. We need to ensure that our data warehouse has a huge storage capacity such as 250 terabytes.
Loading and indexing performance
We will evaluate our data warehouse on loading and indexing performance. A data warehouse loads a lot of data within a narrow time window. This means that loading of the data needs to have a high performance so as not to disrupt the system. We will also evaluate how well the data is indexed and record found during search.
Operational integrity, reliability, and manageability
The data in the data warehouse needs to be available at all time and when needed. We will also evaluate the data reliability, i.e. when a transaction happens, it should either complete the transaction successful or rollback when failure occurs. The data warehouse should also have a mechanism of recovering from failure and restoring the data. These functions are provided by the data warehouse management system. Data warehouse management system should also provide the functionality of where users cannot input null values e.g. through the use of SQL commands such as NOT NULL.
Client/server connectivity
Data warehouse depends on the network connectivity and online reporting tools such as OLAP. We will also evaluate the connectivity of our network. Our network needs to be of high speed so as to ensure fast data transmission.
Query processing performance
This is an evaluation of how fast our data warehouse can handle complex queries. We need to evaluate the query processing performance of our data warehouse.
SQL QueriesSELECT * FROM School;
SELECT schoolId, SchoolName, SUM(AmountOfLoans) from School, DirectLoanSubsidized WHERE School.SchoolId = DirectLoanSubsidized.SchoolId GROUP BY ROLLUP(SchoolId, SchoolName);
The evaluation of our “higher education student aid” data warehouse gave us various findings and they include:
We are currently operating on a one terabyte capacity. We need to increase the capacity to 250 terabytes.
We have a good network but still it is not that fast enough when it comes to data transmission; we need to install a high speed network.
We don’t need to do anything for other services offered by the data warehouse management system such as reliability, query processing performance, recovery etc.
We will have quality data and the area that we need to improve is the capacity and the network speed.
Begg, C & Connolly, T (2015). Database Systems. A practical approach to design, implementation, and management. Retrieved from http://people.stfx.ca/x2011/x2011asx/5th%20Year/Database/Database%20Management%20Textbook.pdf
Demarest, M. (1995). A data warehouse evaluation model. Retrieved from http://www.noumenal.com/marc/oracle7.html