Course Syllabus

1. Module 1: Introduction to BI and Pentaho (4 hours)
2. Overview: Introduce business intelligence concepts and Pentaho’s architecture. Cover data warehousing basics (facts, dimensions, star/snowflake schemas, SCD) and show how Pentaho integrates ETL with analytics. Demonstrate the Pentaho toolset: Pentaho Data Integration (Spoon UI) and the Pentaho BA Server (Pentaho User Console).
3. Learning Objectives: Understand BI fundamentals (data warehousing, star schemas, OLAP) and Pentaho’s role in BI. Learn Pentaho installation and UI navigation (PDI Spoon, BA server console).
4. Key Topics: Pentaho overview (open-source BI/ETL platform); Data warehousing concepts (dimensional model, star vs. snowflake schema, fact and dimension tables, slowly changing dimensions); Pentaho architecture (PDI/Kettle, BA Server, Report Designer, Mondrian).
5. Tools: Install Pentaho CE (Community Edition) for Data Integration and BI. Use MySQL or PostgreSQL as a sample data warehouse.
6. Hands-on Labs: Browse Pentaho documentation; ETL Demo: Run a built-in sample transformation (e.g. Kitchen job) to see data flow.
7. Use Case: Example scenario of loading retail sales data: identify fact (sales) and dimension (product, date, store) tables.
8. Best Practices: Introduce modular ETL design, importance of version control, and consistent naming conventions. Emphasize verifying data models with stakeholders before ETL.
9. Module 2: Pentaho Data Integration (PDI) Fundamentals (8 hours)
10. Overview: Deep dive into PDI’s core concepts. Learn to create Transformations and Jobs in the Spoon interface. Demonstrate basic ETL patterns: reading from sources, applying simple transformations, and writing to targets. Pentaho documentation notes that “transformations describe the data flows for ETL such as reading from a source, transforming data and loading it” while “jobs coordinate ETL activities… defining the flow and dependencies”. This module covers those concepts end-to-end.
11. Learning Objectives: Build simple PDI transformations and jobs. Understand source/target steps, filters, and basic data flows. Learn error handling and logging in Spoon.
12. Key Topics: PDI Interface: Spoon perspective; Transformation vs Job design; Input steps: CSV file input, Table input (JDBC); Output steps: Table output, Text file output; Basic Steps: Select Values, Filter Rows, Calculator, Sort Rows; Preview & Debug: Running transformation preview, checking row counts. Use the ETL example scenario from Pentaho’s tutorial (load a CSV of sales data, clean missing postal codes).
13. Tools: Pentaho Spoon (data integration UI), any RDBMS (MySQL/SQL Server) for target.
14. Hands-on Labs: Lab 1: Build a transformation to extract a sample CSV (e.g. sales or customer data), filter records, and load into a database table. Use “Preview rows” to validate. Lab 2: Create a Job that runs the transformation only if the source file exists (using a File exists job entry).
15. Use Case: Sample dataset (e.g. a small sales ledger CSV). Scenario: Clean and load daily sales into a data warehouse.
16. Best Practices: Demonstrate good design (use of sub-transformations, annotations). Emphasize checking data quality in preview and logging successes/errors.
17. Module 3: Advanced PDI Transformations (6 hours)
18. Overview: Build on fundamentals with complex transformations. Cover data cleansing, enrichment, and dimension loading techniques. Pentaho provides many specialized steps (e.g. stream lookup, merge join, Group By) for ETL logic. Key areas: joining and splitting data streams, handling missing or duplicate data, and implementing Slowly Changing Dimensions (SCD). This module also introduces parameters, variables, and metadata injection for dynamic ETL.
19. Learning Objectives: Use advanced PDI steps to clean and transform data, perform lookups and joins, and implement SCD techniques. Learn to externalize variables and parameters.
20. Key Topics: Data Cleansing: Data Validator, Regex Evaluation/Output, Null if step; Joins & Lookups: Stream Lookup, Database join, Merge Join; Aggregations: Group By, Analytic Query; Splitting/Merging: Filter Rows, Switch/Case, Merge Rows; Slowly Changing Dimensions: using UPDATE/INSERT logic, versioning, and the PDI SCD step or techniques (Type 1 vs Type 2). Dynamic ETL: Using variables in transformations/jobs, parameterization, metadata injection.
21. Tools: Spoon; sample dimensional schema (star schema tables) in the database.
22. Hands-on Labs: Lab 3: Clean a “dirty” dataset (e.g. name normalization, date format). Lab 4: Implement an SCD: Given dimension table (e.g. Customer) with Type 2 logic, write a transformation that updates historical rows and inserts new versions. Lab 5: Create a parameterized transformation (e.g. database connection defined by variable).
23. Use Case: Merging incremental sales data: demonstrate merging daily sales with existing data warehouse tables.
24. Best Practices: Stress reusability (sub-transformation templates), error handling (error hops, logging error files), and performance (using “Bulk loader” steps for large loads). Advise on cataloging transformations in a repository for team use.
25. Module 4: PDI Integration with Big Data (4 hours)
26. Overview: Show how PDI connects to Big Data sources. Pentaho supports Hadoop, Spark, NoSQL, and cloud data stores. Discuss connecting to HDFS, Hive, HBase, and generating Hadoop (MapReduce or Spark) transformations. Describe Pentaho’s “multi-cluster” capability: a single PDI instance can pipe data across multiple Hadoop clusters (on-premise and cloud) without reconfiguration.
27. Learning Objectives: Learn to configure Hadoop/HDFS connections in PDI. Perform ETL using big data steps, and understand Pentaho’s multi-cluster features.
28. Key Topics: Hadoop Connectivity: HDFS file input/output, Hive query steps, Kafka/HBase connection; Pentaho MapReduce (PDI for Hadoop): designing a transformation to run on a Hadoop cluster; Cloud Data: connecting to Amazon S3/Azure Blob (if time permits); Multi-Cluster Support: connecting to multiple Hadoop clusters in one pipeline.
29. Tools: PDI with Pentaho Hadoop plugins (or bundled Big Data plugin). Use a sample HDFS environment or local distro.
30. Hands-on Labs: Lab 6: Configure a Hadoop HDFS connection and load a CSV from HDFS to a database. Lab 7: Demonstrate multi-cluster pipeline: e.g. read from one Hadoop Hive table and write to another Hadoop cluster’s HDFS (simulated).
31. Use Case: Processing web log data stored in HDFS, then merging results into a relational data warehouse.
32. Best Practices: Emphasize security (Kerberos for Hadoop), data format choice (Parquet/Avro), and using caching or staging when handling very large data volumes.
33. Module 5: Pentaho BA Server & Administration (4 hours)
34. Overview: Cover the Pentaho Business Analytics (BA) server – the central platform for reports, dashboards and OLAP. Discuss installation, architecture (repository, plugin architecture), and basic administration. Show how to organize content, manage users/roles, and schedule jobs on the BA server.
35. Learning Objectives: Understand Pentaho Server components (DI server vs BA server), security model, and repository management. Learn to publish content to the server and set up data sources.
36. Key Topics: BA Platform Overview: Pentaho Server (Community vs Enterprise), PUC (Pentaho User Console) Repository & Kettle DB: storing jobs and transformations; User Roles & Security: creating roles, permissions for access; Content Management: folders, reports, dashboards, metadata; Scheduling: using Pentaho scheduler to run ETL jobs and reports at intervals.
37. Tools: Pentaho BA Server (setup on Tomcat or JBoss), Pentaho User Console.
38. Hands-on Labs: Lab 8: Install Pentaho BA Server and log in. Create a new user and role, and assign privileges. Lab 9: Publish a simple report (from Module 7) to the server and schedule it to run daily.
39. Use Case: Publish a sales report dashboard on the BA server and schedule nightly ETL.
40. Best Practices: Emphasize backing up repository/database, separating environments (dev/test/prod), and securing sensitive data sources with proper credentials.
41. Module 6: Reporting with Pentaho (6 hours)
42. Overview: Teach report building with Pentaho Report Designer (PRD) and publishing to the BA server. Pentaho calls this “pixel-perfect” reporting. Students learn to design table and chart reports, bind data sources, add parameters, and format output (PDF, Excel, HTML). Emphasize interactive reporting features and report bursting.
43. Learning Objectives: Create paginated reports using PRD. Use SQL queries and metadata. Add prompts/parameters for dynamic filtering. Publish and schedule reports on the server
44. Key Topics: Pentaho Report Designer: interface, creating new report; Data Sources: JDBC connections, metadata models (XMI), CSV inputs; Report Elements: tables, cross-tabs, charts, subreports; Parameters & Prompts: building a parameterized query, creating input controls; Formatting: grouping, sorting, conditional styling; Output: export formats, mail distribution; Interactive Reporting (PIR): overview of ad-hoc reporting (if time).
45. Tools: Pentaho Report Designer, BA Server.
46. Hands-on Labs: Lab 10: Build a sales report: connect to the data warehouse, write a SQL query to fetch year-to-date sales, and display in a table with a chart. Add a date-range parameter for filtering. Lab 11: Publish the report to BA Server, create a schedule, and generate sample PDF/Excel outputs.
47. Use Case: Monthly finance report (sales vs targets) with drill-down capability.
48. Best Practices: Stress consistent templates (logos/headers), use of subreports for complex layouts, and testing output formats. Encourage separating data queries (metadata layer) from presentation for reusability.
49. Module 7: OLAP and Data Analysis (4 hours)
50. Overview: Introduce multidimensional analysis with Pentaho Mondrian and Analyzer. Teach how to design OLAP cubes to enable fast slicing/dicing of data. Show how to use Pentaho Schema Workbench to define a Mondrian XML schema (connecting facts and dimensions) and deploy it. Demonstrate the Pentaho Analyzer (web-based pivot table) for ad-hoc analysis of the cube.
51. Learning Objectives: Build an OLAP cube in Pentaho. Understand MDX basics and interactive analysis.
52. Key Topics: Dimensional Modeling Recap: verify fact tables and dimension hierarchies; Mondrian (Pentaho Analysis Services): overview of the OLAP engine; Schema Workbench: define a cube XML (measures, dimension levels); Data Access: MDX introduction; Pentaho Analyzer: creating an ad-hoc query by dragging measures and dimensions, filtering and drilling down.
53. Tools: Pentaho Schema Workbench, Pentaho Analyzer (web).
54. Hands-on Labs: Lab 12: Using the same sales dataset, create a simple cube schema (e.g., Sales Amount by Time and Product). Deploy it on BA Server. Use Analyzer to create a report (e.g., yearly vs monthly sales by region).
55. Use Case: Sales dashboard: allow users to pivot on product lines, time periods, and geography without writing SQL.
56. Best Practices: Emphasize clean cube design (avoid redundant hierarchies), good naming for members, and using aggregates to speed up queries. Discuss balancing cube depth vs performance.
57. Module 8: Dashboards and Data Visualization (4 hours)
58. Overview: Train on building dashboards that combine reports, charts, and input controls. Pentaho offers a Dashboard Designer (CDE for Community) or interactive dashboards in the User Console. This module covers best practices for layout, interactivity, and KPI displays. Students will learn to pull published reports and charts into a dashboard framework and configure parameter linking.
59. Learning Objectives: Create interactive dashboards using Pentaho tools. Present KPIs and visualizations for decision-makers.
60. Key Topics: Dashboard Tools: Pentaho Dashboard Designer or CTools (CDE) overview; Components: adding charts, gauges, tables to a dashboard; Data Sources: reuse PRD reports or use Pentaho Metadata/SQL for charts; Interactivity: linking controls (filters, prompts) across components; UI Layout: best practices for navigation and design.
61. Tools: Pentaho Dashboard Designer (or Community Dashboard Editor), Pentaho BA Server.
62. Hands-on Labs: Lab 13: Build a dashboard for executive view: include top 5 products chart, time-series sales graph, and a filter for region/date. Ensure components update together when filters change.
63. Use Case: Marketing dashboard with campaign performance metrics and filters for product lines.
64. Best Practices: Stress usability: clear labels, color consistency, avoiding clutter. Encourage mobile-friendly design if tools allow. Use Scriptable components (JavaScript) sparingly for custom logic.
65. Module 9: Capstone Project & Best Practices Review (6 hours)
66. Overview: Apply all learned skills in a comprehensive project. Students work (individually or in teams) on a case study that simulates a real BI initiative: e.g., build an end-to-end solution for a fictional company. They will design a data warehouse schema, implement ETL with PDI, and create reports/dashboards on the BA server. This module also recaps best practices and addresses common pitfalls.
67. Capstone Project: Project Example: Retail Analytics Solution – Load sample retail sales and inventory data into a star-schema warehouse; implement transformations for daily updates (including SCD handling); build a suite of reports (sales summary, inventory levels) and a dashboard for management.
68. Tasks: Define fact/dimension tables; develop ETL transformations and Jobs; create at least two published reports and one interactive dashboard; document the solution.
69. Support: TAs/instructor will guide project scope; recommended datasets or templates provided (e.g., hypothetical sales CSVs or Pentaho’s Steel Wheels demo data).
70. Certification Prep (Optional): Review key topics for Pentaho certification (such as repository use, complex transformations, scheduling, and analytics). Provide resources or practice exam questions for self-study.
71. Final Best Practices: Summarize lessons on performance tuning (partitioning, indexing), code modularity, version control and deployment strategies. Discuss team collaboration (using Git, documentation standards) and continuous improvement (iterative development with stakeholder feedback).