Data Warehouse Design and OLAP for Business Intelligence

Desklib provides solved assignments and past papers to help students succeed.

Contents
Part Number 1- Data Warehouse Design...................................................................................2
Part Number 2- OLAP Queries..................................................................................................5
References..................................................................................................................................6
List of Figures
Figure 1: Customer table............................................................................................................3
Figure 2: ScoreInformation table...............................................................................................3
Figure 3: StockInformation........................................................................................................3
Figure 4: Trading_Stock............................................................................................................3
Figure 5: Customer Data............................................................................................................4
Figure 6: Trading_Stock Data....................................................................................................4
Figure 7: StockInformation Data...............................................................................................4
Figure 8: ScoreInformation Data...............................................................................................4
Figure 9: Entity Relationship Diagram......................................................................................5
Figure 10: Output 1....................................................................................................................6
Figure 11: Output 2....................................................................................................................6

Part Number 1- Data Warehouse Design
Data Dictionary
Customer’s Table
Name of Column Data Type Null Constraints
Customer_ID Int N Primary Key
Customer_Firstnam
e (Li et al., 2016)
Varchar(50) N
Customer_Lastname Varchar(50) N
Customer_Contact Int N
Customer_Address Varchar(50) N
Customer_Age Int Y
ScoreInformation’s table
Column name Data Type Null Constraints
Scr_ID Int N Primary Key
Scr_Number Int N Check constraint
Scr_Commission Int N
StockInformation’s table
Column name Data Type Null Constraints
Stck_ID Int N Primary Key
Stck_Name Varchar(50) N Check constraint
Trading_Stock’s table
Column name Data Type Null Constraints
Trd_ID Int N Primary key
Trd_Date Date N
Trd_Type Varchar(50) N
Customer_ID Int N
Stck_ID Int N

Scr_ID Int N
Accnt_Number Varchar(50) N
Table Creation
Figure 1: Customer table
Figure 2: ScoreInformation table
Figure 3: StockInformation
Figure 4: Trading_Stock

Data Population
Figure 5: Customer Data
Figure 6: Trading_Stock Data
Figure 7: StockInformation Data
Figure 8: ScoreInformation Data

Entity Relationship Diagram
Figure 9: Entity Relationship Diagram

Part Number 2- OLAP Queries
1. Select count(*)AS RECORDS from StockInformation.Customer
INNER JOIN StockInformation.Trading_Stock on Customer.Customer_id =
Trading_Stock.Customer_ID WHERE CONCAT
(Customer.Customer_FirstName,Customer.Customer_LastName) = 'JohnAlan' AND
TO_CHAR(Trading_Stock.Trd_Date,'MM')=07; (Guimaraes, M., 2018)
Figure 10: Output 1
2. Select SUM(ScoreInformation.Scr_ Commission) as TOTAL_CHARGES
from ScoreInformation INNER JOIN Trading_Stock
On ScoreInformation.Scr_ID = Trading_Stock.Scr_ID
Where (ScoreInformation.Scr_Number=4 or ScoreInformation.Scr_Number=5)
AND TO_CHAR(Trading_Stock.Trd_Date,'YYYY')=2003; (Foster, E.C. and Godbole, S.,
2016).
Figure 11: Output 2

References
 Li, S., Zhang, S., Zhang, Z., Li, S., Jia, K., Hu, Z., Ping, L., Hu, Y. and Li, Y., 2016.
Design and Implementation of 3D Model Data Management System Based on SQL.
In Mechanical Engineering and Control Systems: Proceedings of 2015 International
Conference on Mechanical Engineering and Control Systems (MECS2015) (pp. 438-441).
 Guimaraes, M., 2018. Data warehouse and data mining with SQL server. Journal of
Computing Sciences in Colleges, 34(1), pp.233-234.
 Foster, E.C. and Godbole, S., 2016. Overview of Microsoft SQL Server. In Database
Systems (pp. 461-467). Apress, Berkeley, CA.