About the Customer
Enterprise messaging solutions for SMS and multimedia campaigns and communication to target customers and clients.
There is a large amount of data, with single tables having large records. There is high expectation of quick performance from predefined dashboards and reports and the telecommunication functional terms need to be understood.
They have their own ETL (Extract Transform Load) tool, through which they will populate the data into data warehouse database.
Following are the key Challenges.
- Selecting the open-source, columnar database (providing the read/write benchmark for the BI application)
- In memory solution for caching the result of dashboard and report, so default quick output will be obtained (Combination of the filter parameter needs to be defined, based on use).
- Database Insert/Load should work very efficiently as there is a large amount of data getting inserted over a period of time.
SPEC INDIA developed the solution using Pentaho stack with the below features:
- Designed a Star schema based Data Warehouse after discussion, understanding and analysis of Telecommunication terms, current database structure and required dashboards and reports
- Provided the suggestion of “InfiniDB” as a columnar database with distribution of data on three machines, finally selected it as the data warehouse DB, after providing the benchmark result to client.
- Provided suggestion of advanced visualization tools, like “Heat MAP”, “Tree MAP “etc., for inclusion into dashboards.
- Customized the Pentaho Community Edition, with “White Labeling”, where we masked everything which was named Pentaho and provided a look and feel of the desired application maintaining company standards.
- For faster access of the dashboard and reports within no time, we cached the result into memory with Hazelcast in Pentaho. We identified the combination of filters which were frequently used, from logs and their inputs. In the night batch cycle, we generated the result set and put into cache for faster access.
- CDR Network Traffic Analysis
- Communicate MOU, CALL QTY, OCN inclusion of different dimension and measures.
- Filter based on all dimensions.
- Highest aggregation is month, (Start with Year-Quarter-Month).
- Compare the Year on Year, as well as month on month also.
- Drillable down on all Hierarchy dimensions as well as Time dimension.
- Show the average and median line on the chart, so easily identify the anomaly.