VIETNAM NATIONAL UNIVERSITY, HANOI INTERNATIONAL SCHOOL GRADUATION PROJECT BUILDING AND DEPLOYING DATA WAREHOUSE MODEL FOR FINANCIAL INSTITUTION Đặng Thùy Ngân Hanoi - 2024 VIETNAM NATIONAL UNIVERSITY, HANOI INTERNATIONAL SCHOOL BUILDING AND DEPLOYING DATA WAREHOUSE MODEL FOR FINANCIAL INSTITUTION SUPERVISOR: PhD. Nguyễn Quang Thuận STUDENT: Đặng Thùy Ngân STUDENT ID: 20070762 COHORT: QH2020 SUBJECT CODE: INS401101 MAJOR: Management Information System Hanoi - 2024 2 ACKNOWLEDGEMENTS I would like to extend my sincere appreciation to my supervisor, Dr. Nguyen Quang Thuan, for his unwavering support, invaluable guidance, and genuine enthusiasm throughout this thesis research. His dedicated mentorship has been instrumental in shaping the direction and quality of work, enabling me to acquire valuable knowledge and develop a profound sense of self-assurance in my chosen field of study.
I am deeply grateful to the Faculty of Applied Science for providing the necessary resources and fostering a conducive learning environment that has facilitated the successful completion of this research. The faculty members, particularly those within the International School, have imparted invaluable knowledge and skills, enriching my academic journey. Furthermore, I would like to express my heartfelt thanks to my family and friends for their steadfast support and encouragement during this challenging endeavor. Their belief in my abilities and constant encouragement, support has been a great source of strength and motivation.
Completing a graduation thesis is a demanding undertaking, and I acknowledge that I could not have reached this academic milestone without the companionship, support and guidance of all those mentioned above. I am sincerely grateful to each and every one of you for being an integral part of this remarkable journey. 3 DECLARATION I, Dang Thuy Ngan, hereby declare that the thesis entitled “Building the data warehouse model for institutions”, presented as a partial fulfillment of the requirements for the degree of Management Information System at International School, Vietnam National University, Hanoi is entirely my original work. The ideas, data, and information presented in this thesis have been appropriately sourced and referenced.
This thesis reflects my independent thinking and comprehensive understanding of the subject matter, without any falsifications, fabrication or manipulation of data or results. All imagines, figures, tables, and other materials utilized in this thesis have either been created by myself or have been sourced from the public domain, with proper permissions and acknowledgements from the original creators. I am fully aware that any violation of the aforementioned declarations may lead to consequences in accordance with the academic policies and integrity guidelines of International School, Vietnam National University, Hanoi. 4 ABSTRACT This project will present an overview of Data Warehouse systems in various large financial organizations currently in Vietnam and worldwide and solution to construct a Data Warehouse model aimed at addressing these difficulties faced by financial companies and subsequently implement the proposed solution using a test dataset from a financial institution in Vietnam.
The project will focus two main parts. The first part will focus on designing and building the components of the Data Warehouse model. The second part will involve deploying the test dataset during the User Acceptance Testing (UAT) phase at TP Bank into the developed model. 5 List of Acronyms DWH Data warehouse DB Data Base OLAP Online analytical processing OLTP Online transactional processing IBM International Business Machines RDMS Relational Database Management System TPBank Tien Phong Commercial Joint Stock Bank FSDM Financial Services Data Model STG Staging SOR System of Record AWM Atomic Warehouse DMT Data Mart ETL Extract - Transform - Load BI Business Intelligence DIM Dimension SCD TYPE 1 Slowly Changing Dimension Type 1 SCD TYPE 2 Slowly Changing Dimension Type 2 UAT User Acceptance Testing ODS Operational Data Store 6 List of Tables Table No Table name Table 2-1 Differences between Database and Data Warehouse Table 2-2 FSDM 9 concepts List of Figures Figure No Figure Name Figure 1-1 Proposed Data Warehouse model Figure 2-1 Star Schema model Figure 2-2 Galaxy Schema model Figure 2-3 Overview architecture of FSDM Figure 2-4 ETL Process Figure 2-5 Example of SCD Type 1 storage Figure 2-6 Example of SCD Type 2 storage on date T+1 Figure 2-7 Example of SCD Type 2 storage on date T+x Figure 2-8 Example of Snapshort storage Figure 2-9 Properties of an input stage of a Parallel Job in DataStage Figure 2-10 A Parallel Job in DataStage Figure 2-11 A Sequence Job in DataStage Figure 3-1 Data Warehouse Model Figure 3-2 Data Flow in Staging layer Figure 3-3 Data Flow in SOR layer 7 Figure 3-4 Data Flow in Data Mart layer Figure 3-5 Source data in Data Warehouse System Figure 3-6 Minus tables in Staging Area Figure 3-7 Data Relationship Diagram in SOR Area Figure 3-8 Entity Relationship Diagram in SOR Area Figure 3-9 Dimension Table Structure Figure 3-10 Slowly Change Dimension Processing Figure 3-11 Entity Relationship Diagram Figure 3-12 Data in Minus layer of Loan Master table Figure 3-13 Sequence Job loads data from temporary table to storage table Figure 3-14 Data in Fact Table Figure 3-15 Configure parameter and run job Figure 3-16 Console of running job Figure 3-17 Table PD_DIM_DA (Product Dimension) Figure 3-18 Table AR_ANL_FCT (Arrangement Analysis Fact) Figure 3-19 Data Warehouse flow in DataStage Figure 3-20 Connect to Database Figure 3-21 Model view Figure 3-22 Daily Loan Balacing report 8 TABLE OF CONTENTS INTRODUCTION.
11 CHAPTER 1 - THEORETICAL BASIS. Overview of Data Warehouse. Comparisions of Datawarehouse and Database. Architecture of Data Warehouse.
Logical model of Data Warehouse. Fact constellations schema (Galaxy schema). Financial Service Data Model. Extract – Transform - Load process in Data Warehouse.
Data storage mechanism in Data Warehouse. Introduction of the tools and technology used in the project. Oracle Database Management System. IBM Infosphere DataStage and QualityStage.
Structured Query Language – Procedural Language .33 CHAPTER 2 - DESIGN AND BUILD DATA WAREHOUSE. Design of Data Warehouse Model. Building Database for DataWarehouse. Building Database for Data Mart.
Structure of tables in Data Mart Area. Database for Data Mart .44 CHAPTER 3 - DEPLOYMENT OF LOAN DATA INTO THE DATA WAREHOUSE MODEL FOR DATA ANALYSIS. ETL data from Data Source to Staging. ETL data from Staging to Atomic.
ETL data from Atomic to Data Mart. Run Data Flow. Create report by Power BI. 61 10 INTRODUCTION Data management is one of the most critical factors for companies, especially in the financial sector, for automated reporting systems, as they need to be generated promptly and ensure data accuracy.
Information processing and data systems should be tightly integrated and managed to ensure the efficiency and accuracy of reporting. This becomes even more crucial for automated reporting systems, as they need to be generated promptly and ensure data accuracy. At a financial company, data collection and analysis are extremely important activities. However, financial companies are facing large volumes of data collected from various sources, resulting in time-consuming data queries.
This complicates the management, retrieval, and analysis of reports. Furthermore, data from these sources often lack standardization, have different formats, and contain missing or inaccurate values. This makes data-driven analysis ambiguous and unreliable, posing risks to business decision-making. To ensure the most effective data collection and integration, a Data Warehouse is indispensable.
The Data Warehouse consolidates, integrates, and analyzes various data sources on a single platform, while providing regular and automated reports for users. To achieve this goal, the project "Building and deploying a Data Warehouse model for a financial company" aims to optimize the process of data collection, processing, and analysis. This project has proposed a solution for building a Data Warehouse model to address challenges encountered by financial institutions. This enables the company to make accurate and timely decisions.
Specifically, key issues to address include: • Centralized storage of data files from data sources, • Standardization of data sources into a structured format, • Development of data flows segmented into clear areas, • Establishment of a dedicated database for analytical reporting, 11 • Ability to generate analytical reports. In the context of data warehousing, the top-down and bottom-up approaches offer distinct methodologies, each with its own set of benefits and challenges. The top-down approach, developed by Bill Inmon, emphasizes a centralized data warehouse with comprehensive integration from various data sources, supporting complex queries and detailed analysis. However, this approach often requires a substantial investment of time and resources for implementation and can be complex to manage, potentially delaying the realization of benefits (Inmon, 2005).
On the other hand, the bottom-up approach, advocated by Ralph Kimball, focuses on building data marts first, which are then integrated to form a comprehensive data warehouse. This approach is more user-centric, allowing for faster deployment and immediate business value, as it is designed to meet the specific needs of end-users (Kimball & Ross, 2013). While the bottom-up approach may face integration challenges and scalability issues, it offers greater flexibility and faster returns on investment. Given the needs of financial institutions for timely, accurate, and standardized reporting, Kimball's bottom-up approach is more suitable.
Flexible data warehousing solutions allow organizations to integrate disparate data sources quickly and efficiently, enabling timely and informed decision-making (Watson, 2009). Furthermore, user-centric approaches prioritize the needs of end-users, ensuring that the data warehouse is designed to provide relevant, accessible, and actionable insights. This focus on usability enhances the overall effectiveness of data analysis and reporting, facilitating better strategic planning and operational efficiency (Kimball & Ross, 2013). For financial institutions, where accurate and timely data is paramount, adopting a flexible and user-centric data warehousing approach is particularly advantageous.
It supports the development of robust reporting systems that can adapt to regulatory changes, market fluctuations, and internal performance metrics, thereby improving the organization's ability to 12 respond swiftly and effectively to new challenges and opportunities (Saxena & Srinivasan, 2019). Proposed Data Warehouse model The data warehouse model demonstrates notable effectiveness in facilitating efficient data processing and integration. This structured flow begins with the collection of raw data in the staging area, where it undergoes initial cleaning and transformation. The subsequent atomic layer stores data at its most granular level, providing a robust foundation for detailed analysis and ensuring data integrity (Kimball & Ross, 2013).
The data mart layer then organizes data into specific business areas, enhancing accessibility and relevance for end-users, which supports quicker and more accurate reporting. This systematic approach enables comprehensive and user-centric data analysis, making it a valuable tool for financial institutions where timely and precise reporting is crucial (Watson, 2009). In the project "Building and Implementing a Data Warehouse Model for a Financial Company," the objective is to understand the Data Warehouse system model for data storage, then ingest data from sources, and design the data flow to create a dedicated storage area for analytical reporting. With the project's requirements and goals in mind, the project will be presented across the following two chapters: Chapter 1: Theoretical Basis – This chapter provides an overview of the concepts, theoretical foundations, and technologies applied to build the Data Warehouse model.
Chapter 2: Designing and Building the Data Warehouse System Model - This chapter covers the design of the system model, the structure of the storage tables, and the process of building the system model. 13 Chapter 3: Implementing Loan Data on the Data Warehouse Model for Data Analysis – This part describes the process of deploying a test dataset during the UAT phase of bank based on the designed model to generate analytical reports. Next, I will present each chapter and its corresponding content sequentially. 14 CHAPTER 1 - THEORETICAL BASIS This chapter will present an overview of the fundamental concepts in Data Warehousing, along with the technologies and tools used for data storage and processing to support financial reporting.
Overview of Data Warehouse According to Inmon, a data warehouse is a subject-oriented, integrated, time- variant, and non-volatile collection of data in support of management’s decision- making process (Inmon, 2005).