Learn Pentaho - Analytics & Machine Learning Workflow

#Introduction

Data isn't only for historical reports — it can answer "what will happen?". Episode 18 covers the bridge between ETL and machine learning. You'll learn to prepare data for analytics, integrate R, Python, and Weka, build predictive flows, and bring the results to dashboards.

The key point: a machine learning model is just a data consumer. Pentaho's job is to prepare the quality data that model eats — work that's often overlooked even though it determines model quality.

#Preparing Data for Analytics and ML

Before a model sees data, the pipeline must prepare it. Data quality is 80% of successful machine learning work. The core steps:

• Combine sources: merge data from several tables and systems into one analytical dataset.
• Clean and impute: handle missing values (episode 6) with deliberate decisions — don't let a model learn from holes.
• Feature engineering: create meaningful derived columns — aggregates, ratios, categorizations, or lag values.
• Split data: separate the dataset into training and testing so model evaluation is honest.

Pentaho excels at this part: transformation steps like Group by, Calculator, Join, and Stream lookup are feature engineering tools you've already mastered.

#An Example of Feature Engineering in PDI

Feature engineering is usually a combination of steps you already know. For example, to build a "total spend in the last 90 days" feature per customer: Sort rows by customer and date, then Group by with a time window already filtered in the source SQL. The goal isn't writing formulas, but assembling a flow that can be explained, tested, and repeated — exactly like regular ETL.

A rule of thumb that applies: the more complex the feature engineering, the more important it is to record the steps. If a feature can't be explained, the model born from it can't be held accountable either.

#Integration with Python, R, and Weka

PDI doesn't replace ML toolkits — it works alongside them. Several integration paths:

• Weka: Pentaho was born with Weka integration (the Java data mining toolkit). Weka steps can train and evaluate models directly in a transformation.
• Python: via Execute Process or plugins, you can call Python scripts from a job. Suitable for leveraging modern ML ecosystems like scikit-learn.
• R: with R Output or R Script, you can run R statistical analysis within a pipeline.

The most flexible pattern: PDI prepares the data, then calls an external script, then reads the results back for processing or reporting. An example of a simple Python script that takes data and produces predictions:

Example Python script for simple scoring

import sys
lines = sys.stdin.read().splitlines()
for line in lines:
    jumlah = float(line)
    risiko = "Tinggi" if jumlah > 1000000 else "Rendah"
    print(risiko)

Note the pattern above: the script reads from stdin and writes to stdout — a contract easily connected from the Execute Process step in PDI. Before integrating it, test the script standalone from the terminal with python3 script.py — if the output is correct there, integration problems later are almost certainly in the PDI configuration, not the script.

#Building a Predictive Data Flow

A complete predictive flow consists of three phases that can be built as a job:

1. Training: the historical dataset is prepared, the model is trained (Weka/Python/R), and the model artifact is saved.
2. Scoring: new incoming data is scored with the trained model.
3. Result distribution: score results are merged with business data, written to a table, and presented in a dashboard.

The training phase usually runs periodically (for example weekly) because the model needs to learn from the latest data; the scoring phase can run far more often. This orchestration fits PDI jobs well: the train_model job collects data and trains the model, then the score_data job uses the model to score incoming data.

#An Example of Training and Scoring Orchestration

The job below shows a simple sequence separating the training and scoring phases:

START -> siapkan_dataset.ktr -> train_model.kjb -> simpan_artefak.kjb
                                     |
                                     -> score_harian.kjb -> tabel_skor.ktr

The train_model job runs weekly and saves the model artifact; the score_harian job runs every night using the latest artifact to score new data. These two paths are deliberately separated so a scoring failure doesn't disrupt the training process — a separation that keeps operations stable.

#Visualizing Model Results and Outputs

Prediction results are useless if they don't reach decision-makers. Ways to present model output:

• Results table: store scores in a database table, then display them in a Pentaho report (episode 11).
• Score distribution: create a distribution chart to see the proportion of prediction results.
• Score dashboard: combine model metrics — accuracy, coverage, number of scored rows — in one operational dashboard.

A common pattern: the scoring job writes the results table every night, and the dashboard updates automatically in the morning. Management doesn't have to ask "how many high-risk customers are there?" — the answer is already on screen.

#Healthy Analytics Pipeline Patterns

To close this episode, the healthy patterns that tie everything together:

• Separate data preparation from modeling: analytical datasets are prepared by separate, reusable, validated transformations.
• Version datasets: store snapshots of the data used for training so experiments can be compared fairly.
• Record model decisions: save the model version, training date, and evaluation metrics in a history table.
• Monitor drift: watch the scoring data distribution against training data — if it changes dramatically, the model needs retraining.

These patterns make your ML flow not just an experiment, but accountable operations — in line with the operational readiness spirit of the next episode.

#Conclusion

In episode 18 you connected ETL with analytics and ML: preparing data for models, integrating Python, R, and Weka, building training-scoring-distribution flows, and visualizing model outputs in dashboards.

The key takeaways:

• The quality of the data prepared by a pipeline determines model quality — not the sophistication of the algorithm.
• PDI is strongest as a preparation layer; model training is left to ML toolkits.
• The stdin-stdout contract keeps cross-language script integration simple.
• Healthy predictive flows separate training, scoring, and distribution, plus monitor drift.

In episode 19, we prepare for the worst while doing our best: operational readiness & runbooks — writing runbooks for deployment, failures, and recovery, backing up repositories and configuration, incident procedures, and routine maintenance and cleanup.