Ubuntu Pentaho Lab

Setup Pentaho Server + Plugins on Ubuntu ..

Pentaho Lab

Pentaho Data Integration is a client-based tool commonly installed and configured to run on Windows 11.

There are several licensing options, for these workshops we will be installing a Enterprise Edition. This will give you the opportunity to try out building a complete solution - automated data pipelines + analytics ..

The following steps are intended for setting up a Pentaho Lab environment and need to be completed in order to complete the Workshops.

Ensure you have downloaded the Workshop--Installation:

cd
git clone https://github.com/jporeilly/Workshop--Installation

To install git:

sudo apt install git

Prerequisites

Ubuntu 24.04 LTS system (physical or virtual machine)
User account with sudo privileges
Internet connection
Basic familiarity with Linux command line

Docker

Docker is a platform that enables developers to package applications and their dependencies into lightweight, portable containers. Containers ensure that applications run consistently across different computing environments, from development laptops to production servers. This workshop will guide you through the complete process of installing Docker Engine on Ubuntu 24.04 LTS (Noble Numbat).

Before installing Docker, update your existing package list.

sudo apt update && sudo apt upgrade

Install packages that allow apt to use repositories over HTTPS.

sudo apt install -y ca-certificates curl gnupg lsb-release

Create a directory for keyrings and add Docker's GPG key.

sudo install -m 0755 -d /etc/apt/keyrings

curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo gpg --dearmor -o /etc/apt/keyrings/docker.gpg

sudo chmod a+r /etc/apt/keyrings/docker.gpg

Add the Docker repository to your apt sources.

echo \
  "deb [arch=$(dpkg --print-architecture) signed-by=/etc/apt/keyrings/docker.gpg] https://download.docker.com/linux/ubuntu \
  $(. /etc/os-release && echo "$VERSION_CODENAME") stable" | \
  sudo tee /etc/apt/sources.list.d/docker.list > /dev/null

Now that the Docker repository is added, update the package index.

sudo apt update && sudo apt upgrade

Install Docker Engine, containerd, and Docker Compose.

sudo apt install -y docker-ce docker-ce-cli containerd.io docker-buildx-plugin docker-compose-plugin

Check that Docker is installed correctly by checking the version.

docker --version

You should see output similar to (Nov 2025):

Docker version 29.0.2, build 810xxxx

Verify that Docker Engine is running.

sudo systemctl status docker

The service should show as "active (running)".

Quit.

Test your Docker installation by running the hello-world container.

sudo docker run hello-world

This command downloads a test image and runs it in a container. If successful, you'll see a message confirming that Docker is working correctly.

Without Sudo

By default, Docker requires sudo privileges. To run Docker commands without sudo.

Add your user to the docker group.

sudo usermod -aG docker $USER

Apply the new group membership (or log out and back in).

newgrp docker

Verify you can run Docker without sudo.

docker run hello-world

Ensure Docker starts automatically when the system boots.

sudo systemctl enable docker.service
sudo systemctl enable containerd.service

Verification & Testing

To confirm everything is working properly, run the following commands:

Check Docker version:

docker version

View Docker system information:

docker info

List running containers:

docker ps

List all containers (including stopped ones):

docker ps -a

List downloaded images:

docker images

Common Commands

Here are essential Docker commands you'll use regularly:

docker pull <image> - Download an image from Docker Hub
docker images - List all local images
docker run <image> - Create and start a container from an image
docker ps - List running containers
docker ps -a - List all containers
docker stop <container> - Stop a running container
docker rm <container> - Remove a stopped container
docker rmi <image> - Remove an image
docker logs <container> - View container logs
docker exec -it <container> bash - Access a running container's shell

Docker Compose - MySQL

The pentaho_admin user only has READ permission for the Steel Wheels - sampledata database. The administrator account has been removed.

As you'll be running through CRUID database operations we need to deploy a sampledata database - Docker container, granting all privileges to an admin user.

1KB

3KB

882B

Run the following script to create a MySQL folder and copy the required files.

cd
cd ~/Workshop--Installation/MySQL
# Make it executable
chmod +x copy_mysql.sh
./copy_mysql.sh

Check the Directory has been created and the files copied over.
Execute the docker-compose script to create the container.

cd
cd ~/MySQL
# Make it executable
chmod +x run_mysql_compose.sh
./run_mysql_compose.sh

Check the container is up and running in Docker.

docker ps

Sampledata

Next on the list is to create the sampledata database.

sampledata_schema.sql

This script creates a comprehensive relational database structure for a sample business application. It's designed to model a sales and order management system for a company that sells various products.

Database Setup

Creates a database named
```
sampledata
```
with UTF-8 character set
Sets up users with appropriate permissions
Configures SQL mode for better data integrity

Tables

OFFICES: Stores company office locations with address details

EMPLOYEES: Contains employee information with relationships to offices and reporting structure

CUSTOMERS: Stores customer information including contact details and credit limits

PRODUCTS: Contains product catalog with inventory and pricing information

ORDERS: Tracks customer orders with status and dates

ORDERDETAILS: Contains line items for each order with quantity and price

PAYMENTS: Records customer payments with amounts and dates

ORDERFACT: A fact table for order analytics

CUSTOMER_W_TER: Extended customer information with territory

DIM_TIME: Time dimension table for reporting

DEPARTMENT_MANAGERS: Stores department manager information

QUADRANT_ACTUALS: Contains budget vs. actual financial data with a generated VARIANCE column

TRIAL_BALANCE: Financial accounting data

Views

customer_order_summary: Summarizes orders and spending by customer

product_performance: Analyzes product sales metrics including revenue and profit

employee_sales_performance: Tracks sales performance by employee

monthly_sales_trend: Shows sales trends over time by month

product_inventory_status: Categorizes products by inventory levels

customer_payment_history: Summarizes customer payment activity and balances

Stored Procedures

GetCustomerOrders: Retrieves orders for a specific customer

UpdateProductStock: Updates product inventory levels

GetProductSalesByQuarter: Analyzes quarterly product sales

GetTopCustomersByRegion: Identifies top customers by region

GetInventoryValueByProductLine: Calculates inventory metrics by product line

Triggers

before_order_insert: Validates date constraints on orders

before_payment_insert: Ensures payment amounts are positive

Events

daily_maintenance: Scheduled task for database maintenance

Execute the following command to create the schema.

cd 
cd ~/MySQL
cat sampledata_schema.sql | docker exec -i mysql-database-1 mysql -u root -p"password" sampledata

This command is importing SQL schema data into a MySQL database running in a Docker container. Here's a breakdown:

This command reads the SQL file:

cat sampledata_schema.sql

Pipes (forwards) the file contents to the next command:

This executes a command in a running Docker container:

docker exec -i mysql-database-1 mysql -u root -p"password" sampledata

You can check the sampledata database & tables with the following commands.

Show databases:

docker exec -i mysql-database-1 mysql -u root -p"password" -e "SHOW DATABASES;"

Show tables:

docker exec -i mysql-database-1 mysql -u root -p"password" sampledata -e "SHOW TABLES;"

Show table columns:

docker exec -i mysql-database-1 mysql -u root -p"password" sampledata -e "DESCRIBE table_name;"

sampledata_data.sql

This script populates the database with sample data to demonstrate the functionality of the schema.

Reference Data

Office locations across different regions
Employee hierarchy with job titles
Product catalog organized by product lines

Transactional Data

Customer records with contact information
Order history with dates and status
Order details with quantities and prices
Payment records

Financial Data

Budget vs. actual figures in QUADRANT_ACTUALS
Trial balance accounting data

Data Characteristics

Realistic business scenarios with varied order statuses
Comprehensive product catalog with descriptions and pricing
Hierarchical employee structure with reporting relationships
Time-based data spanning multiple years for trend analysis
Financial data suitable for budgeting and variance analysis

Notable Features

Data follows referential integrity constraints
Proper handling of NULL values where appropriate
Realistic pricing and quantity values
Generated columns (like VARIANCE) are excluded from direct inserts
Orders are sequenced to satisfy foreign key constraints

Execute the following command to load the data into the sampledata tables.

cd 
cd ~/MySQL
cat sampledata_data.sql | docker exec -i mysql-database-1 mysql -u root -p"password" sampledata

You can use the following commands to check that the data has loaded.

To count the number of rows in a specific table:

docker exec -i mysql-database-1 mysql -u root -p"password" sampledata -e "SELECT COUNT(*) FROM table_name;"

To view the first few rows from a table:

docker exec -i mysql-database-1 mysql -u root -p"password" sampledata -e "SELECT * FROM table_name LIMIT 10;"

To check counts for all tables:

docker exec -i mysql-database-1 mysql -u root -p"password" sampledata -e "SELECT TABLE_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'sampledata';"

To get a summary of tables and their statuses:

docker exec -i mysql-database-1 mysql -u root -p"password" sampledata -e "SHOW TABLE STATUS;"

DBeaver

Your going to need a database management tool. DBeaver Community is a free, open-source database management tool for personal projects.

Simpliest option is to download & install from Snapstore.

cd
sudo apt update && sudo apt upgrade
sudo apt install snapd
sudo snap install dbeaver-ce

Go to the official DBeaver download page

https://dbeaver.io/files/dbeaver-ce_latest_amd64.debdbeaver.io

To install that DEB file.

wget https://dbeaver.io/debs/dbeaver.gpg.key -O /tmp/dbeaver.gpg.key
sudo gpg --dearmor -o /usr/share/keyrings/dbeaver.gpg /tmp/dbeaver.gpg.key
echo "deb [signed-by=/usr/share/keyrings/dbeaver.gpg] https://dbeaver.io/debs/dbeaver-ce /" | sudo tee /etc/apt/sources.list.d/dbeaver.list
sudo apt update
sudo apt install dbeaver-ce

Pin DBeaver to Dash - bottom toolbar.

MySQL Database

If you have completed the previous 3 requirements, then you should have a MySQL Docker container, exposed on port:3306 with sampledata databse.

Launch DBeaver and Select: MySQL.

Configure the connection with the following properties:

Username: root or pentaho_user

Password: password

You may need to download the supported version of the database driver.

Also enable: allowPublicKeyRetrieval

Test the connection.

Expand: databases > sampledata > Tables

Open a SQL window and run a test query.

select * from CUSTOMERS
where COUNTRY = 'USA' and CITY = 'NYC';

General Troubleshooting (click to expand)

Issue: "permission denied" errors

Solution: Ensure your user is in the docker group and you've logged out/in or run newgrp docker

Issue: Docker service won't start

Solution: Check logs with sudo journalctl -u docker.service

Issue: Cannot connect to Docker daemon

Solution: Ensure Docker service is running with sudo systemctl start docker

PreviousK3s NextWindows Pentaho Lab

Last updated 6 months ago

Was this helpful?