Real-Time Financial Data Warehouse
The Executives of ABC University wants to know the financial condition of their organization. The executives need such a repository in which most recent data is available from different departments for analysis. For this we need to develop a Real-Time DWH having most recent data for effective decision making.
A DWH collects the data from multiple operational source systems (OLTP – On-Line Transaction Processing) and stores it in a central repository, which is further used for analysis. However traditionally, the data in DWH is loaded periodically – typically in a daily, weekly or even monthly basis which means that its data is not up-to-date. The data which is saved in OLTP between those time periods (when data is loaded) is not included in reports generated for analysis from DWH.
Real-Time DWH is a solution of this problem as it provides the most recent data for analysis. When any data is entered in OLTP, It is reflected in the repository of Real-Time DWH. We have to perform ETL process at the moment of insertion of any new record in OLTP. It is done with the special techniques.
Pre-requisite:
Student should have the strong concepts of Databases and Data warehouse
Functional Requirements:
1.This is a desktop application
2.There are two users of this application, Admin and Executive.
3.Admin can add or remove any Executive.
4.Different departments in the university are using their own databases (OLTPs) to store the information according to their need.
4.1.Accounts department stores the information of revenue such as fees submitted by the students and money earned by the selling of the software products developed by its software house.
4.2.Finance department stores the information about the expenses of the university such as salaries of employees, utility bills, furniture, electrical and electronic devices.
4.3.Registration department stores the information about the student’s bio data including the degrees in which they are enrolled.
4.4.HR department stores the information about the employees of the university (Faculty & Non Faculty).
5.All the departments (above) are using SQL server DBMS.
6.The star schema in this application will have the multiple fact tables.
7.The procedure of ETL will took place at the time of insertion of any new record in OLTP. For this we will use triggers. (Student can use any other technique after getting the approval from his concerned supervisor).
8.Executive has the interface to view different reports regarding expenses and revenue.
8.1.The executive will view the reports regarding expenses. For example he will be interested to see the total expenses or a particular type of expense in a particular time period etc.
8.2.The executives will view the reports regarding revenue. For example he will be interested to view the total revenue or a particular type of revenue at the particular time period etc.
9.Reports will be generated on run time on the basis of different parameters given by the particular executive. Parameters have been discussed above in section 4.1, 4.2, 4.3 and 4.4
10.Interface must provide the facility to Drill Down and Roll UP Operations.
Tools:Microsoft.Net Framework, Java, SQL Server
Supervisor Name: Shabib Aftab
Email ID: