Amplifying Performance with Analytics and Intelligence
THE NEED
Sigma had to provide an ETL, DW & BI solution for a leading global MVN Operator and Enabler.
The need was to create a comprehensive BI solution for analyzing CDR data – Voice, SMS, GPRS and Top-up. The project needed Sigma to understand Master data like Country, Call type, Service types, Subscribers, etc. It also required an understanding of ETL mapping specs, ETL Design & Development, DW Modeling.
ENGAGEMENT
Performance test for ETL scripts and Reports Automation of test cases and alerts if there are any errors/exceptions.
Validation for the following:
- DW Model – surrogate keys, dimensions and fact
- CDR and file naming conventions as per UK standards
- CDR files frequency checks, sequence numbers and count checks
- Null values and inconsistency checks between source
- CDR file record and DW tables
- Dimension and fact tables data as per source data received
- Reports for- Empty report, Data correctness, Current date report creation,
- Missing numeric value, Filter conditions, Exclusions, Report dependencies
- Delivery of the reports to end users as per the frequency
Validation of ETL Scripts
- # of ETL Jobs /Transformationswritten : 300+
- # of Data sources : 3
- Data extracted from: CDR files, CSV and Excel
THE SIGMA BENEFIT
With Sigma’s BI solutions, the client gained following benefits:
- Identify Fraud events
- Customer expired and activation status
- Analytics about SIM stocks and voucher stocks by resellers
- Network Utilization across all subscribers and country.
- Reconciliation of raw data, billing data and reporting data
- Automation of report generation
TECHNOLOGY
Sigma employed some of the best technologies for taking this project to its desired destination:
- Pentaho BI Suite
- PDI
- Oracle
- Solaris