Windows 11 Pentaho Lab

Setup Windows Pentaho Pro Suite ..

Overview

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 30-day 'Pro Suite' Evaluation Edition. This will give you the opportunity to try out building a complete solution - automated data pipelines + analytics ..

Please ensure you have installed & configured the following essential apps.

Pro Suite Edition

The Pentaho Pro Suite Edition is the latest release of:

Server Components

Pentaho Server
Pentaho Analyzer, Interactive & Dashboard plugins

Client Components

Pentaho Data Integration
Metadata Editor
Schema Workbench

The following content has been automatically generated by an AI system and should be used for informational purposes only. We cannot guarantee the accuracy, completeness, or timeliness of the information provided.

Any actions taken based on this content are at your own risk. We recommend seeking qualified expertise or conducting further research to validate and supplement the information provided.

Download the Pro Suite Edition.

Fill in the form.

Once completed you will be able to download: pentaho-business-analytics-xxxx.exe

Check your email for the 30-day activation code ..

Navigate to the downloaded pentaho-business-analytics-10.X.X-XXX.exe installation file.
Double-click the pentaho-business-analytics-10.X.X-XXX.exe file to launch it.
Ignore the Antivirus warning ..

Accept the License agreement.

Keep the default installation path.

The default Pentaho Repository database is PostgreSQL.

Password: password

Decide if you wish to install everything or specific components.

With the Pentaho Installation Wizard you can choose one of two ways to install Pentaho components:

Default: Select the Keep it simple. Give me everything option in the installation wizard.
Custom: Select the Let me decide for myself option in the installation wizard.

You should have received an email with your activation code.

Enter either an Activation ID or a Licensing server URL (Production).

Click 'Next' to start the installation.

Various notifications will appear during the installation process. Allow Pentaho services to be installed.

Once completed you should have the following Pentaho directory structure.

Data Integration

Start Pentaho Data Integration.

Windows - Powershell

Set-Location C:\Pentaho\design-tools\data-integration
.\spoon.bat

Linux

cd
cd ~/Pentaho/design-tools/data-integration
./spoon.sh

Docker Desktop & Compose - Windows

Docker Desktop is an open platform for developing, shipping, and running applications.

Any actions taken based on this content are at your own risk. We recommend seeking qualified expertise or conducting further research to validate and supplement the information provided.

Download the Docker Desktop installer.

Navigate to: /Downloads
Double-click Docker Desktop Installer.exe to run the installer.

By default, Docker Desktop is installed at C:\Program Files\Docker\Docker.

When prompted, ensure the Use WSL 2 instead of Hyper-V option on the Configuration page is selected.

On systems that support only one backend, Docker Desktop automatically selects the available option.

Follow the installation wizard instructions to authorize the installer and proceed with the installation.
When the installation is successful, select: Close to complete the installation process.

Docker User

If your administrator account is different to your user account, you must add the user to the docker-users group:

Run Computer Management as an administrator.
Navigate to Local Users and Groups > Groups > docker-users.
Right-click to add the user to the group.
Sign out and sign back in for the changes to take effect.

MySQL - Docker Container

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.

Create a MySQL folder and copy the required files.

Create directory & copy - PowerShell

cd \
cd C:\Workshop--Data-Integration\Setup\MySQL
.\copy-mysql.ps1

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

MySQL Container - PowerShell

cd \
cd C:\MySQL
.\run-docker-mysql.ps1

Check the container is up and running in Desktop Docker.

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.

Create sampledata schema - PowerShell

Get-Content 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:

```
Get-Content sampledata_schema.sql
```
- This PowerShell command reads the contents of the SQL schema file
```
|
```
- Pipes (forwards) the file contents to the next command
```
docker exec -i mysql-database-1 mysql -u root -p"password" sampledata
```
- This executes a command in a running Docker container:
- docker exec -i
  - Runs a command in interactive mode in a container
- mysql-database-1
  - The name of the Docker container
- mysql -u root -p"password" sampledata
  - MySQL command to:
  - Connect as the root user (
    -u root
    )
  - Use "password" as the password (
    -p"password"
    )
  - Connect to a database called "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.

Load data in tables - PowerShell

Get-Content sampledata_data.sql | docker exec -i mysql-database-1 mysql -u root -p"password" sampledata

Reads the content of your data SQL file with
```
Get-Content
```
Pipes it to the docker container
Executes it in MySQL against the sampledata database

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;"

PreviousGenAI NextStorage

Last updated 10 days ago

Overview

Pro Suite Edition

Data Integration

Windows - Powershell

Linux

Docker Desktop & Compose - Windows

Docker User

MySQL - Docker Container

Docker Compose - MySQL

Create directory & copy - PowerShell

MySQL Container - PowerShell

sampledata

sampledata_schema.sql

Create sampledata schema - PowerShell

sampledata_data.sql

Data Characteristics:

Notable Features:

Load data in tables - PowerShell

DBeaver