Stock Analysis Solution
Database and Data warehouse project : Group 4

Team Members
5220426005
5220426006
5220426011
5220426012
5220426015
5220426029

Overview
SET_DW
SET_DB
Normalized
Relational
Database
STAR
Schema
Database
M
O
L
A
P

Database Diagram - Overview
SET_DB is OLTP database for Stock analysis solution which designed by using Normalized Relational database methodology.
Key, Relationship and Constraints also applied

Database Diagram Stock
SET_DB This is an overview of all tables which create for store stock price data and companys basic information.

Database Diagram - Finance
SET_DB This is an overview of all tables which create for store Financial Statements data and companys basic information.

Data Warehouse Diagram - Overview
SET_DW is database which support OLAP for Stock analysis solution. Its designed by using STAR schema. There are 4 dimensions and 3 fact table, all data will populate by using ETL process, except Date dimension.
Date dimension was pre-populated from SSAS then modified to follow Kimball concept.

DW Diagram Stock Price History
Stock Price Historyis group of fact table and dimension to support various historical data of stock prices
Symbol dimension is using SCD Type I to maintain the data. It was create from de-normalization technique to increase understandability and reduce the complexity of joining table.
Date dimension was pre-populated from SSAS then modified to follow Kimball concept.

DW Diagram Dividend: SCD Type2
Benefit (Dividend) is group of fact table and dimension that support tracking of the benefit of stock and it also support Trial Test of SCD type II , how to manage, update etc.
Dividend dimension was define as a SCD Type II dimension. Each change in attribute will create another record.
Date dimension was pre-populated from SSAS then modified to follow Kimball concept.

DW Diagram Financial Ratio
Financial Ratiois a group of fact table and dimension that support value which use for calculate Financial ratio on the fly by using calculate filed which done by OLAP Tools
Date dimension was pre-populated from SSAS then modified to follow Kimball concept.

ETL Process Overview

ETL Dim_DividenedType
Dimension Dim_DividenedTypewas designed using SCD Type I
Any update on Dividend Type Description filed system will update on this dimension using replace method

ETL Dim_Symbol
Dimension Dim_Symbolwas designed using SCD Type IAny update on filed listed below system will update on this dimension using replace method

• Company Name
• Industrial Category
• Industrial Group
• Stock Type Description