ETL Project

Authored by Ameex Technologies on 04 Sep 2019

ETL system built using SSIS to integrate data from 6 sources to create Business Intelligence Dashboards.

A US based Risk Retention Group wanted to track various metrics and get actionable insights on daily basis using Tableau & SQL Server.

Current State

  • Client is liability insurance and risk management service provider to schools and colleges
  • Data from multiple different data sources(Salesforce, Google analytics, Silverpop, Hootsuite, Techcanary, eTouches) are extracted in flat files and reports are generated manually
  • Client wants to generate integrated dashboards that measure efforts to realize the business objectives and provide a holistic view

Complications

  • A holistic view is not presented as the reports and data storage are currently fragmented
  • Fragmentation is creating a lot of manual process

Future state

  • Data extraction, data profiling, data transformations are done in the ETL process (Extract, Transform and Load)
  • Automated ETL jobs to extract data from various data sources
  • Scheduled data transformations and process before loading into data warehouse/data mart
  • Integrated dashboards that measure efforts to realize the business objectives and provide a holistic view

Architecture

dataETLr

 

sim

 

Case Studies