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

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.