CONTACT

The Client

Our client is a medical institution who is using CDR to record the data from telephone exchange and other telecommunication equipment.

Service Offerings

Technology Stack

Talend

MySQL

PostgreSQL

Tableau

Business Scenario

The data from different sources is maintained in MySQL database. The data recorded contains various attributes of the call, such as time, duration, completion status, source number, and destination number and also automated data. The call detail record contains data fields that describe a specific instance of a telecommunication transaction, but does not include the content of that transaction. The client wanted to integrate the data from different sources and form a single data source using ETL tool and load it in to PostgreSQL and connect it with tableau to create dashboards.

Business Requirements

  • Identify the different data sources where the data has been recorded Integrate data which is recorded in different sources for analysis using ETL tool.
  • Creating a new database from all the disintegrated databases.
  • The new live data to be extracted, transformed and loaded into one data source using Talend data studio.
  • To generate log files for the Talend job deployed in the cron-job.
  • Synchronize data with the source database by checking upon the last run date of the Talend job.
  • To build an interactive customized dashboard with various business critical parameters.

Our Solution

The tool identified for extracting, transforming and loading (ETL) data from different data sources is Talend data studio. The data from MYSQL were extracted using Talend data studio. The data from multiple MySQL database tables were joined and transformed so that no additional calculations were to be made to generate the Tableau reports. Log files were generated by the Talend job to view the last run date, number of data rows processed and status message. Capability of synchronization of the data with the source database was made by checking upon the last run date from the destination table. The data has been loaded to PostgreSQL with the required calculated fields and Tableau dashboard were created.

Challenges

  • Identify the data sources to be integrated.
  • Arriving at the data fields to be created on the data source as it is the one point for storing all data from different data sources.
  • Arriving at the calculation that needs to be displayed in the reports.
  • Identifying and designing the dashboards.
  • Synchronizing to get the live data. To process large data.

Architecture

Results

The reports are extracted every day at midnight in the selected time zone, and is loaded in PostgreSQL with the help of the ETL tool Talend data studio.Data from PostgreSQL is connected with Tableau to create required dashboards.

How can we help you?

Get in touch with us to schedule a consultation