- July 28, 2023
Telangana Rainfall & Humidity BI-Report Using GCP Cloud SQL Server & SSIS Package
Problem Statement
- We wanted to integrate day and monthly excel sheets which have rainfall & humidity statistics at it’s mandal and district level of
Telangana state into GCP SQL Server - Looking to have BI report to take insights of the statistics
Goals
- Minimize manual interventions
- To build multi-dimensional view of the data in GCP Cloud SQL (SQL Server)
- Store the data in Datawarehouse
- Build BI layer for BI reports
- To build BI reports
System Design Considerations
- Excel has source or transactional data
- GCP Cloud SQL (SQL Server) as Datawarehouse solution (To store periodic data)
- Microsoft SQL Server Integration Services (SSIS) as data cleansing, integration, transformation, and loading tool
- Microsoft Power BI as visualization tool to build reports
Project Architecture
Our Approach
Excel data
Source data which is coming in the form of CSV files, when there are multiple files, need to merge or integrate them using SSIS package tool
GCP Cloud SQL(SQL Server)
Destination or GCP Cloud SQL to store the Excel data in the form of tables such as Dimensions and Fact tables where each dimension table is getting connected to the fact table using primary – foreign key relationship
SQL Server Integration Services(SSIS) Package
Data integration, cleansing, and data loading tool from excel or CSV files.
Power BI
Visualization tool to build BI reports.