What is our primary use case?
The use case is for a General Insurance company headquartered in Japan. Data from legacy and active systems are integrated, and a standardized and clean copy of data is delivered for tracking KPIs, Loss Ratios, Claim Process Quality Review, and Customer 360 Portal using Pentaho, Tableau, R, and Qlikview.
Pentaho was used to build ETL pipelines for data warehouses (SSOT - Single source of Truth) and to prepare/distribute data for Tableau and Excel users.
Pentaho also had the ability to create Tableau data extracts for the purpose of developing report prototypes.
Currently I use this at Kaizentric Technologies to build data warehouse on Snowflake.
How has it helped my organization?
It automated several data preparation tasks, enabled cleaner/standardized data for Tableau reports, and was used for integrating data from the current operating system with 3 other legacy system data.
Pentaho was used to migrate data from legacy systems such as Mumps, AS400, AeGIS, to MS SQL Data Warehouse.
ETL jobs helped to move data from Premia (Oracle) systems and OGG Replicas (Oracle Golden Gate) to MS SQL.
We used Pentaho to perform data quality analysis and for the development of data marts/DWH solutions and dashboards adhering to time requirements.
What is most valuable?
The fuzzy logic match component came in handy to identify AML suspects (anti-money laundering). The SHA key generation component helped in generating a unique identifier for our customers, without revealing personal data; especially when we send data to the AWS cloud.
Regularly, we had to send the new policy data files to medical centers over email. Using Pentaho, we were able to compress the files in zip format with a password and email them to unlimited partners.
We could confidently use Pentaho for sharing data with internal and external customers in mediums such as FTP servers, LAN folders (Local Area Network), email, etc.
Connectivity to platforms such as snowflake is great.
What needs improvement?
To implement a logic we may need to use several components. In other tools such as Informatica, equivalent logic can be implemented with lesser components. To set the Java memory size, the configuration has to be changed outside the tool; it will be easier if everything can be handled within the tool.
There are no easy guides to implementing metadata injection. Templates and sample jobs could be provided for customers who need to leverage metadata for standard processing across multiple databases and tables.
For how long have I used the solution?
I've used the solution for five years. I am also using the kettle as ETL for any personal projects, along with other ETL tools such as Fivtran & DBT.
What do I think about the stability of the solution?
The solution is stable enough.
What do I think about the scalability of the solution?
We ran into insufficient memory issues due to the limits specified in Java config files. When processing larger data files, the UI may hang. But this issue may not appear when the programs run on the server.
How are customer service and support?
As we opted for an OEM solution. We had to go through the vendor. It was not a great experience with most of the tickets.
How would you rate customer service and support?
Which solution did I use previously and why did I switch?
Stored procedures were used to generate data files. UI-based ETL pipelines are easier to maintain.
How was the initial setup?
The initial setup was simple to medium. We were able to install it without any assistance from tech support.
What about the implementation team?
We handled the implementation in-house. Our staff was given official Pentaho training, which helped in implementing the data pipelines.
What was our ROI?
We did not achieve the ROI. The work delivered to users had lesser value than the subscription cost.
What's my experience with pricing, setup cost, and licensing?
We were lucky enough to find a Pentaho OEM partner who offered a data warehouse model and the ETL software for about 60K SGD per year.
Which other solutions did I evaluate?
We considered Informatica, Talend, and Lavastorm Analytics. We dropped Informatica as it was an expensive alternative. The staff was not comfortable when reading Java code generated by Talend. Pentaho is more popular than Lavastorm. For these reasons (and a few others), we chose Pentaho.
What other advice do I have?
Pentaho seems to be popular in Europe and Canada. Talend is popular in APAC region. Informatica is popular in the USA. You may want to consider the availability of technical staff and support in your region before choosing ETL software.
Which deployment model are you using for this solution?
On-premises