Data Warehouse Architecture Solution for Adventure Works

1Data Warehouse Architecture Solution for Adventure Works
DATA WAREHOUSE ARCHITECTURE SOLUTION FOR ADVENTURE WORKS
[Student Names]
[University Name]
[Lecture Name]
[Date]

2Data Warehouse Architecture Solution for Adventure Works
Abstract
The data warehouse is a process that helps in the storage of data in a centralized location where
the data is extracted from multiple locations the data warehouse is used by the business owners
in the implementation of the business intelligence in order to improve on the handling of their
organizations performance through the analysis of the data warehouse data.
This report involves the implementation of the data warehouse for the adventure works database
this data warehouse will as a result assist in the analysis of the products orders in average, their
costs and the location of orders in a specified month and year. Therefore to get the answer for the
above question the dimensional modelling was done through the creation of fact and dimensional
tables, however the use of star schema was to give the specific solutions to the questions used.
The data is then validated before loading to data warehouse in order to check its quality by use of
the Data Quality Assurance rules where any wrong data is inserted in the data logs table , to
implement the data warehouse the SQL server was used.
Table of Contents

3Data Warehouse Architecture Solution for Adventure Works
Abstract............................................................................................................................................2
1 Introduction and requirements analysis...................................................................................5
1.1 Business requirements and proposed data warehouse solution........................................5
2 Solution design and development............................................................................................7
2.1 Development methodology (Kimball approach)...............................................................7
2.2 Detailed requirement analysis...........................................................................................8
2.3 Dimensional modelling...................................................................................................10
2.4 Physical database design.................................................................................................13
2.4.1 Partitioning..............................................................................................................13
2.4.2 Indexing...................................................................................................................13
2.5 ETL design......................................................................................................................14
2.6 Data quality assurance....................................................................................................15
2.7 Key DW technique/technology: Star schema.................................................................15
3 Evaluation and Lesson Learned.............................................................................................16
3.1 Evaluation of data warehouse performance....................................................................19
3.2 Lessons learned from the solution design and development...........................................19
4 Conclusion and future development......................................................................................19
4.1 Conclusion......................................................................................................................19
4.2 Future developments.......................................................................................................20
5 Reference...............................................................................................................................20

4Data Warehouse Architecture Solution for Adventure Works
1 Introduction and requirements analysis
The Data warehouse is a database that is centralised in nature and is used to store data that is
obtained from multiple sources and the stored data is used for analysis and generation of the
reports using some deliverables.
All the historical data is analysed through the use of the queries where the main data warehouse
data sources is from online transaction processing system and all the businesses transactions logs
are stored in this database where the data is consolidated from various sources of data.
The Extract transform and load (ETL) is used in the consolidation purpose of the data to be used
in the creation of the data warehouse where the consolidated data is obtained from various OLTP
data sources.
The Business intelligence is a vital aspect that is used in the business database approaches which
are used in the computing industries and it is used to define the data warehousing and reporting
theories.
To develop the proposed data warehouse the BI tool is used which is part of the Microsoft SQL
Server Reporting Services since the Microsoft SQL server has a flexible and multi-purpose
report solutions where the reports are designed, installed and run on.
1.1 Business requirements and proposed data warehouse solution
This report involves the building of the data warehouse of the database adventure framework
where the adventure works is a company’s database that has various tables including the
products, sales, and production, territory and time details.
Below are the various business requirements:
i. For each product, display & sort working order based on the average/total days it has
been late from starting (ActualStartDate-ScheduledStartDate) & from ending
(ActualEndDate-ScheduledEndDate), average actual resource hours, average cost
deviation (PlannedCost-ActualCost), scrapped quantity, actual quantity (StockedQty-
OrderQty) and average/total location cost (Location.costRate *
actualResourceHours) at a specific month/year.
ii. For each working order, display & sort based on the total number of (unique)
products it produced at a specific month/year.