About the Customer
Industry: Health Insurance
The client is one of the leading organizations in the health insurance domain, who has implemented a novel service – whenever people are unable to get health insurance, the company would provide an alternative of the insurance, wherein they can manage an initial conversation with the customer just in case any health issue arrives and provide proper guidance along with managing doctor’s visit also. Their powerful network and customer service empowers straight-forward access to reasonably priced healthcare for those not insured.
Key Challenges
The main key challenges are:
- Cater heterogenous data into a unified format and make dashboards or data visualizations depending on the data.
- Setup the entire BI stack starting from analysis till implementation, since the CRM system is already in place.
- The database design is in open source vtiger CRM, of which, we had little knowledge and hence a challenge to us.
- Development to be done in local environment, so rather than setting up the vtiger CRM, we had to replicate their test environment in SPEC INDIA premises.
- Once project development is done, deployment of the BI app on his server, with https certification in Pentaho.
- Adhoc analysis, for certain fact member and invoice which required separate cubes with row level security.
- Data Cleansing and Profiling, as vtiger was a third-party system and the entire data was fed by the data operator.
Data Source:
Data source is the vtiger CRM system, based on the MySQL database.
Our Solution
SPEC INDIA developed the solution using Pentaho Community stack, for this client requirement, along with the following features:
- Discuss, understand and analyze current requirements of the healthcare domain and transform them into a detailed SRS document, along with client approval. The SRS contained the technical scope of data warehouse as well as the 6-7 Dashboards and CUBE design.
- Provide Transformation to populate the data from vtiger CRM to data warehouse designed into Mysql, set into schedule for daily frequency in night batch cycle.
- Dashboard css and design approved by client and incorporate company standards into BI App.
- All dashboards are tablet friendly (responsiveness) and all designs are boot-strap friendly.
- Customized the Pentaho community Edition, with “White Labeling”, where we masked everything which was named Pentaho.
- Deploy requisite information on their server and provide the mock test on the server, provide the user guidance document for the CUBE usage for adhoc analysis.
- Provide two necessary CUBEs for the adhoc analysis, which would make decision making an easier task as well as make them less dependent on IT department.
Functionality Details:
- Ticket Analysis Dashboard
- Total open tickets by category, to identify the category which received most tickets.
- Total closed tickets by category, to identify the response time and current closing ratio.
- Ticket Category wise created vs. closed vs. open to map the ratio of ticket and track where we lack.
- Open vs. closed ratio, over the period span of year, monthly basis to check the trend.
- Revenue Analysis Dashboard
- Total Revenue by product type, to check which product is doing the good revenue and which are not.
- Different filters based on revenue data to analyze in the particular category.
- Top month, Top Product Analysis.
- Trend showing different Product types – monthly basis, which produce comparable views of different product types.
Tools & Technologies
Business Benefits
This BI Analytics Solution is yet another successful rollout by SPEC INDIA, assisting the client in enhanced with the following benefits:
- Centralized Data Warehouse, which interacts with vtiger CRM and user can view the dashboard and reports without impacting the vtiger CRM database.
- High performance and high flexibility in deployment.
- Better Security implementation with https certification implementation in Pentaho.
- Monitoring of the report access/Time/Frequency to help the IT team identify the usefulness of report.
- Analytics and Self-service Dashboard creation help users to create the desired dashboard and reports.