book

Pentaho® Solutions: Business Intelligence and Data Warehousing with Pentaho and MySQL®

Name: Pentaho® Solutions: Business Intelligence and Data Warehousing with Pentaho and MySQL®
ISBN: 9780470484326

by Roland Bouman, Jos van Dongen

August 2009

Beginner to intermediate

648 pages

15h 15m

English

Wiley

Read now

Unlock full access

Copyright
About the Author
Credits
Acknowledgments
Introduction
About This BookWho Should Read This BookWhat You Will Need to Use This BookWhat You Will Learn from This BookHow This Book Is OrganizedPart I: Getting Started with PentahoChapter 1: Quick Start: Pentaho ExamplesChapter 2: PrerequisitesChapter 3: Server Installation and ConfigurationChapter 4: The Pentaho BI StackPart II: Dimensional Modeling and Data Warehouse DesignChapter 5: Example Business Case: World Class MoviesChapter 6: Data Warehouse PrimerChapter 7: Modeling the Business Using Star SchemasChapter 8: The Data Mart Design ProcessPart III: ETL and Data IntegrationChapter 9: Pentaho Data Integration PrimerChapter 10: Designing Pentaho Data Integration SolutionsChapter 11: Deploying Pentaho Data Integration SolutionsPart IV: Business Intelligence ApplicationsChapter 12: The Metadata LayerChapter 13: Using the Pentaho Reporting ToolsChapter 14: Scheduling, Subscription, and BurstingChapter 15: OLAP Solutions Using Pentaho Analysis ServicesChapter 16: Data Mining with WekaChapter 17: Building DashboardsOn the WebsiteFurther Resources
I. Getting Started with Pentaho
1. Quick Start: Pentaho Examples
1.1. Getting Started with Pentaho1.1.1. Downloading and Installing the Software1.1.2. Running the Software1.1.2.1. Starting the Pentaho BI Server1.1.2.2. Logging in1.1.2.3. Mantle, the Pentaho User Console1.2. Working with the Examples1.2.1. Using the Repository Browser1.2.2. Understanding the Examples1.3. Running the Examples1.3.1. Reporting Examples1.3.1.1. BI Developer Examples: Regional Sales - HTML1.3.1.2. Steel Wheels: Income Statement1.3.1.3. Steel Wheels: Top 10 Customers1.3.1.4. BI Developer Examples: button-single-parameter.prpt1.3.2. Charting Examples1.3.2.1. Steel Wheels: Chart Pick List1.3.2.2. Steel Wheels: Flash Chart List1.3.2.3. BI Developer Examples: Regional Sales - Line/Bar Chart1.3.3. Analysis Examples1.3.3.1. BI Developer Examples: Slice and Dice1.3.3.2. Steel Wheels Analysis Examples1.3.4. Dashboarding Examples1.3.5. Other Examples1.4. Summary
2. Prerequisites
2.1. Basic System Setup2.1.1. Installing Ubuntu2.1.1.1. Using Ubuntu in Native Mode2.1.1.2. Using a Virtual Machine2.1.2. Working with the Terminal2.1.2.1. Directory Navigation2.1.2.2. Command History2.2. Using Symbolic Links2.2.1. Creating Symbolic Links in Ubuntu2.2.2. Creating Symlinks in Windows Vista2.3. Java Installation and Configuration2.3.1. Installing Java on Ubuntu Linux2.3.2. Installing Java on Windows2.4. MySQL Installation2.4.1. Installing MySQL Server and Client on Ubuntu2.4.2. Installing MySQL Server and Client on Windows2.4.3. MySQL GUI Tools2.4.3.1. Ubuntu Install2.4.3.2. Windows Install2.5. Database Tools2.5.1. Power*Architect and Other Design Tools2.5.2. Squirrel SQL Client2.5.2.1. Ubuntu Install2.5.2.2. Windows Install2.5.3. SQLeonardo2.6. Summary
3. Server Installation and Configuration
3.1. Server Configuration3.1.1. Installation3.1.1.1. Installation Directory3.1.1.2. User Account3.1.1.3. Configuring Tomcat3.1.1.4. Automatic Startup3.1.1.4.1. Automatic Startup in UNIX/Linux Systems3.1.1.4.2. Automatic Startup in Windows Systems3.1.2. Managing Database Drivers3.1.2.1. Driver Location for the Server3.1.2.2. Driver Location for the Administration Console3.1.2.3. Managing JDBC Drivers on UNIX-Based Systems3.1.3. System Databases3.1.3.1. Setting Up the MySQL Schemas3.1.3.2. Configuring Quartz and Hibernate3.1.3.2.1. Quartz3.1.3.2.2. Hibernate3.1.3.3. Configuring JDBC Security3.1.3.4. Sample Data3.1.3.5. Modify the Pentaho Startup Scripts3.1.4. E-mail3.1.4.1. Basic SMTP Configuration3.1.4.2. Secure SMTP Configuration3.1.4.3. Testing E-mail Configuration3.1.5. Publisher Password3.2. Administrative Tasks3.2.1. The Pentaho Administration Console3.2.1.1. Basic PAC Configuration3.2.1.2. Starting and Stopping PAC3.2.1.3. The PAC Front End3.2.1.4. Configuring PAC Security and Credentials3.2.2. User Management3.2.3. Data Sources3.2.4. Other Administrative Tasks3.3. Summary
4. The Pentaho BI Stack
4.1. Pentaho BI Stack Perspectives4.1.1. Functionality4.1.2. Server, Web Client, and Desktop Programs4.1.3. Front-Ends and Back-Ends4.1.4. Underlying Technology4.2. The Pentaho Business Intelligence Server4.2.1. The Platform4.2.1.1. The Solution Repository and the Solution Engine4.2.1.2. Database Connection Pool Management4.2.1.3. User Authentication and Authorization4.2.1.4. Task Scheduling4.2.1.5. E-mail Services4.2.2. BI Components4.2.2.1. The Metadata Layer4.2.2.2. Ad hoc Reporting Service4.2.2.3. The ETL Engine4.2.2.4. Reporting Engines4.2.2.5. The OLAP Engine4.2.2.6. The Data Mining Engine4.2.3. The Presentation Layer4.2.4. Underlying Java Servlet Technology4.3. Desktop Programs4.4. Pentaho Enterprise Edition and Community Edition4.5. Creating Action Sequences with Pentaho Design Studio4.5.1. Pentaho Design Studio (Eclipse) Primer4.5.2. The Action Sequence Editor4.5.3. Anatomy of an Action Sequence4.5.3.1. Inputs4.5.3.2. Outputs4.5.3.3. Actions4.6. Summary

II. Dimensional Modeling and Data Warehouse Design
5. Example Business Case: World Class Movies
5.1. World Class Movies: The Basics5.2. The WCM Data5.2.1. Obtaining and Generating Data5.2.2. WCM Database: The Big Picture5.2.3. DVD Catalog5.2.4. Customers5.2.5. Employees5.2.6. Purchase Orders5.2.7. Customer Orders and Promotions5.2.8. Inventory Management5.3. Managing the Business: The Purpose of Business Intelligence5.3.1. Typical Business Intelligence Questions for WCM5.3.2. Data Is Key5.4. Summary
6. Data Warehouse Primer
6.1. Why Do You Need a Data Warehouse?6.2. The Big Debate: Inmon Versus Kimball6.3. Data Warehouse Architecture6.3.1. The Staging Area6.3.2. The Central Data Warehouse6.3.3. Data Marts6.3.3.1. OLAP Cubes6.3.3.2. Storage Formats and MDX6.4. Data Warehouse Challenges6.4.1. Data Quality6.4.1.1. Data Vault and Data Quality6.4.1.2. Using Reference and Master Data6.4.2. Data Volume and Performance6.4.3. Changed Data Capture6.4.3.1. Source Data-Based CDC6.4.3.2. Trigger-Based CDC6.4.3.3. Snapshot-Based CDC6.4.3.4. Log-Based CDC6.4.3.5. Which CDC Alternative Should You Choose?6.4.4. Changing User Requirements6.5. Data Warehouse Trends6.5.1. Virtual Data Warehousing6.5.2. Real-Time Data Warehousing6.5.3. Analytical Databases6.5.4. Data Warehouse Appliances6.5.5. On Demand Data Warehousing6.6. Summary
7. Modeling the Business Using Star Schemas
7.1. What Is a Star Schema?7.1.1. Dimension Tables and Fact Tables7.1.1.1. Fact Table Types7.2. Querying Star Schemas7.2.1. Join Types7.2.2. Applying Restrictions in a Query7.2.2.1. Combining Multiple Restrictions7.2.2.2. Restricting Aggregate Results7.2.2.3. Ordering Data7.3. The Bus Architecture7.4. Design Principles7.4.1. Using Surrogate Keys7.4.2. Naming and Type Conventions7.4.3. Granularity and Aggregation7.4.4. Audit Columns7.4.5. Modeling Date and Time7.4.5.1. Time Dimension Granularity7.4.5.2. Local Versus UTC Time7.4.5.3. Smart Date Keys7.4.5.4. Handling Relative Time7.4.6. Unknown Dimension Keys7.5. Handling Dimension Changes7.5.1. SCD Type 1: Overwrite7.5.2. SCD Type 2: Add Row7.5.3. SCD Type 3: Add Column7.5.4. SCD Type 4: Mini-Dimensions7.5.5. SCD Type 5: Separate History Table7.5.6. SCD Type 6: Hybrid Strategies7.6. Advanced Dimensional Model Concepts7.6.1. Monster Dimensions7.6.2. Junk, Heterogeneous, and Degenerate Dimensions7.6.3. Role-Playing Dimensions7.6.4. Multi-Valued Dimensions and Bridge Tables7.6.5. Building Hierarchies7.6.6. Snowflakes and Clustering Dimensions7.6.7. Outriggers7.6.8. Consolidating Multi-Grain Tables7.7. Summary
8. The Data Mart Design Process
8.1. Requirements Analysis8.1.1. Getting the Right Users Involved8.1.2. Collecting Requirements8.2. Data Analysis8.2.1. Data Profiling8.2.2. Using eobjects.org DataCleaner8.2.2.1. Adding Profile Tasks8.2.2.2. Adding Database Connections8.2.2.3. Doing an Initial Profile8.2.2.4. Working with Regular Expressions8.2.2.5. Profiling and Exploring Results8.2.2.6. Validating and Comparing Data8.2.2.7. Using a Dictionary for Column Dependency Checks8.2.2.8. Alternative Solutions8.3. Developing the Model8.4. Data Modeling with Power*Architect8.5. Building the WCM Data Marts8.5.1. Generating the Database8.5.1.1. Generating Static Dimensions8.5.1.2. Special Date Fields and Calculations8.5.2. Source to Target Mapping8.6. Summary
III. ETL and Data Integration
9. Pentaho Data Integration Primer
9.1. Data Integration Overview9.1.1. Data Integration Activities9.1.1.1. Extraction9.1.1.2. Change Data Capture9.1.1.3. Data Staging9.1.1.4. Data Validation9.1.1.5. Data Cleansing9.1.1.6. Decoding and Renaming9.1.1.7. Key Management9.1.1.8. Aggregation9.1.1.9. Dimension and Bridge Table Maintenance9.1.1.10. Loading Fact Tables9.1.2. Pentaho Data Integration Concepts and Components9.1.2.1. Tools and Utilities9.1.2.2. The Data Integration Engine9.1.2.3. Repository9.1.2.4. Jobs and Transformations9.1.2.4.1. Transformations9.1.2.4.2. Jobs9.1.2.5. Plug-in Architecture9.2. Getting Started with Spoon9.2.1. Launching the Spoon Application9.2.2. A Simple "Hello, World!" Example9.2.2.1. Building the Transformation9.2.2.2. Running the Transformation9.2.2.3. The Execution Results Pane9.2.2.4. The Output9.2.3. Checking Consistency and Dependencies9.2.3.1. Logical Consistency9.2.3.2. Resource Dependencies9.2.3.3. Verifying the Transformation9.2.4. Working with Database Connections9.2.4.1. JDBC and ODBC Connectivity9.2.4.2. Creating a Database Connection9.2.4.3. Testing Database Connections9.2.4.4. How Database Connections Are Used9.2.4.5. A Database-Enabled "Hello, World!" Example9.2.4.6. Database Connection Configuration Management9.2.4.7. Generic Database Connections9.3. Summary
10. Designing Pentaho Data Integration Solutions
10.1. Generating Dimension Table Data10.1.1. Using Stored Procedures10.1.2. Loading a Simple Date Dimension10.1.2.1. CREATE TABLE dim_date: Using the Execute SQL Script Step10.1.2.2. Missing Date and Generate Rows with Initial Date: The Generate Rows Step10.1.2.3. Days Sequence: The Add Sequence Step10.1.2.4. Calculate and Format Dates: The Calculator Step10.1.2.5. The Value Mapper Step10.1.2.6. Load dim_date: The Table Output Step10.1.3. More Advanced Date Dimension Features10.1.3.1. ISO Week and Year10.1.3.2. Current and Last Year Indicators10.1.3.3. Internationalization and Locale Support10.1.4. Loading a Simple Time Dimension10.1.4.1. Combine: The Join Rows (Cartesian product) Step10.1.4.2. Calculate Time: Again, the Calculator Step10.1.5. Loading the Demography Dimension10.1.5.1. Understanding the stage_demography and dim_demography Tables10.1.5.2. Generating Age and Income Groups10.1.5.3. Multiple Incoming and Outgoing Streams10.2. Loading Data from Source Systems10.2.1. Staging Lookup Values10.2.1.1. The stage_lookup_data Job10.2.1.2. The START Job Entry10.2.1.3. Transformation Job Entries10.2.1.4. Mail Success and Mail Failure10.2.1.5. The extract_lookup_type and extract_lookup_value Transformations10.2.1.6. The stage_lookup_data Transformation10.2.1.7. Check If Staging Table Exists: The Table Exists Step10.2.1.8. The Filter rows Step10.2.1.9. Create Staging Table: Executing Dynamic SQL10.2.1.10. The Dummy Step10.2.1.11. The Stream Lookup Step10.2.1.12. Sort on Lookup Type: The Sort Rows Step10.2.1.13. Store to Staging Table: Using a Table Output Step to Load Multiple Tables10.2.2. The Promotion Dimension10.2.2.1. Promotion Mappings10.2.2.2. Promotion Data Changes10.2.2.3. Synchronization Frequency10.2.2.4. The load_dim_promotion Job10.2.2.5. The extract_promotion Transformation10.2.2.6. Determining Promotion Data Changes10.2.2.7. Saving the Extract and Passing on the File Name10.2.2.8. Picking Up the File and Loading the Extract10.3. Summary
11. Deploying Pentaho Data Integration Solutions
11.1. Configuration Management11.1.1. Using Variables11.1.1.1. Variables in Configuration Properties11.1.1.2. User-Defined Variables11.1.1.3. Built-in Variables11.1.1.4. Variables Example: Dynamic Database Connections11.1.1.5. More About the Set Variables Step11.1.1.6. Set Variables Step Gotchas11.1.2. Using JNDI Connections11.1.2.1. What Is JNDI?11.1.2.2. Creating a JNDI Connection11.1.2.3. JNDI Connections and Deployment11.1.3. Working with the PDI Repository11.1.3.1. Creating a PDI Repository11.1.3.2. Connecting to the Repository11.1.3.3. Automatically Connecting to a Default Repository11.1.3.4. The Repository Explorer11.1.3.5. Managing Repository User Accounts11.1.3.6. How PDI Keeps Track of Repositories11.1.3.7. Upgrading an Existing Repository11.2. Running in the Deployment Environment11.2.1. Running from the Command Line11.2.1.1. Command-Line Parameters11.2.1.2. Running Jobs with Kitchen11.2.1.3. Running Transformations with Pan11.2.1.4. Using Custom Command-line Parameters11.2.1.5. Using Obfuscated Database Passwords11.2.2. Running Inside the Pentaho BI Server11.2.2.1. Transformations in Action Sequences11.2.2.2. Jobs in Action Sequences11.2.2.3. The Pentaho BI Server and the PDI Repository11.2.3. Remote Execution with Carte11.2.3.1. Why Remote Execution?11.2.3.1.1. Scalability11.2.3.1.2. Availability11.2.3.1.3. Reduction of Network Traffic11.2.3.1.4. Reduction of Latency11.2.3.2. Running Carte11.2.3.3. Creating Slave Servers11.2.3.4. Remotely Executing a Transformation or Job11.2.3.5. Clustering11.3. Summary
IV. Business Intelligence Applications
12. The Metadata Layer
12.1. Metadata Overview12.1.1. What Is Metadata?12.1.2. The Advantages of the Metadata Layer12.1.2.1. Using Metadata to Make a More User-Friendly Interface12.1.2.2. Adding Flexibility and Schema Independence12.1.2.3. Refining Access Privileges12.1.2.4. Handling Localization12.1.2.5. Enforcing Consistent Formatting and Behavior12.1.3. Scope and Usage of the Metadata Layer12.2. Pentaho Metadata Features12.2.1. Database and Query Abstraction12.2.1.1. Report Definition: A Business User's Point of View12.2.1.2. Report Implementation: A SQL Developer's Point of View12.2.1.3. Mechanics of Abstraction: The Metadata Layer12.2.2. Properties, Concepts, and Inheritance in the Metadata Layer12.2.2.1. Properties12.2.2.2. Concepts12.2.2.3. Inheritance12.2.2.4. Localization of Properties12.3. Creation and Maintenance of Metadata12.3.1. The Pentaho Metadata Editor12.3.2. The Metadata Repository12.3.3. Metadata Domains12.3.4. The Sublayers of the Metadata Layer12.3.4.1. The Physical Layer12.3.4.1.1. Connections12.3.4.1.2. Physical Tables and Physical Columns12.3.4.2. The Logical Layer12.3.4.2.1. Business Models12.3.4.2.2. Business Tables and Business Columns12.3.4.2.3. Relationships12.3.4.3. The Delivery Layer12.3.4.3.1. Business Views12.3.4.3.2. Business Categories12.3.5. Deploying and Using Metadata12.3.5.1. Exporting and Importing XMI files12.3.5.2. Publishing the Metadata to the Server12.3.5.3. Refreshing the Metadata12.4. Summary
13. Using the Pentaho Reporting Tools
13.1. Reporting Architecture13.2. Web-Based Reporting13.3. Practical Uses of WAQR13.4. Pentaho Report Designer13.4.1. The PRD Screen13.4.2. Report Structure13.4.3. Report Elements13.4.4. Creating Data Sets13.4.4.1. Creating SQL Queries Using JDBC13.4.4.2. Creating Metadata Queries13.4.4.3. Example Data Set13.4.5. Adding and Using Parameters13.4.6. Layout and Formatting13.4.7. Alternate Row Colors: Row Banding13.4.8. Grouping and Summarizing Data13.4.8.1. Adding and Modifying Groups13.4.8.2. Using Functions13.4.8.3. Using Formulas13.4.9. Adding Charts and Graphs13.4.9.1. Adding a Bar Chart13.4.9.2. Pie Charts13.4.9.3. Working with Images13.4.10. Working with Subreports13.4.10.1. Passing Parameter Values to Subreports13.4.11. Publishing and Exporting Reports13.4.11.1. Refreshing the Metadata13.4.11.2. Exporting Reports13.5. Summary
14. Scheduling, Subscription, and Bursting
14.1. Scheduling14.1.1. Scheduler Concepts14.1.1.1. Public and Private Schedules14.1.1.2. Content Repository14.1.2. Creating and Maintaining Schedules with the Pentaho Administration Console14.1.2.1. Creating a New Schedule14.1.2.2. Running Schedules14.1.2.3. Suspending and Resuming Schedules14.1.2.4. Deleting Schedules14.1.3. Programming the Scheduler with Action Sequences14.1.3.1. Add Job14.1.3.2. Suspend Job, Resume Job, and Delete Job14.1.3.3. Other Scheduler Process Actions14.1.4. Scheduler Alternatives14.1.4.1. UNIX-Based Systems: Cron14.1.4.2. Windows: The at Utility and the Task Scheduler14.2. Background Execution and Subscription14.2.1. How Background Execution Works14.2.2. How Subscription Works14.2.2.1. Allowing Users to Subscribe14.2.2.2. Granting Execute and Schedule Privileges14.2.2.3. The Actual Subscription14.2.3. The User's Workspace14.2.3.1. Viewing the Contents of the Workspace14.2.3.2. The Waiting, Complete, and My Schedules Panes14.2.3.3. The Public Schedules Pane14.2.3.4. The Server Administrator's Workspace14.2.3.5. Cleaning Out the Workspace14.3. Bursting14.3.1. Implementation of Bursting in Pentaho14.3.2. Bursting Example: Rental Reminder E-mails14.3.2.1. Step 1: Finding Customers with DVDs That Are Due This Week14.3.2.2. Step 2: Looping Through the Customers14.3.2.3. Step 3: Getting DVDs That Are Due to Be Returned14.3.2.4. Step 4: Running the Reminder Report14.3.2.5. Step 5: Sending the Report via E-mail14.3.3. Other Bursting Implementations14.4. Summary
15. OLAP Solutions Using Pentaho Analysis Services
15.1. Overview of Pentaho Analysis Services15.1.1. Architecture15.1.2. Schema15.1.3. Schema Design Tools15.1.4. Aggregate Tables15.2. MDX Primer15.2.1. Cubes, Dimensions, and Measures15.2.1.1. The Cube Concept15.2.1.2. Star Schema Analogy15.2.1.3. Cube Visualization15.2.2. Hierarchies, Levels, and Members15.2.2.1. Hierarchies15.2.2.2. Levels and Members15.2.2.3. The All Level, All Member, and Default Member15.2.2.4. Member Sets15.2.2.5. Multiple Hierarchies15.2.3. Cube Family Relationships15.2.3.1. Relative Time Relationships15.2.4. MDX Query Syntax15.2.4.1. Basic MDX Query15.2.4.2. Axes: ON ROWS and ON COLUMNS15.2.4.3. Looking at a Part of the Data15.2.4.4. Dimension on Only One Axis15.2.4.5. More MDX Examples: a Simple Cube15.2.4.6. The FILTER Function15.2.4.7. The ORDER Function15.2.4.8. Using TOPCOUNT and BOTTOMCOUNT15.2.4.9. Combining Dimensions: The CROSSJOIN Function15.2.4.10. Using NON EMPTY15.2.4.11. Working with Sets and the WITH Clause15.2.4.12. Using Calculated Members15.3. Creating Mondrian Schemas15.3.1. Getting Started with Pentaho Schema Workbench15.3.1.1. Downloading Mondrian15.3.1.2. Installing Pentaho Schema Workbench15.3.1.3. Starting Pentaho Schema Workbench15.3.1.4. Establishing a Connection15.3.1.5. JDBC Explorer15.3.2. Using the Schema Editor15.3.2.1. Creating a New Schema15.3.2.2. Saving the Schema on Disk15.3.2.3. Editing Object Attributes15.3.2.4. Changing Edit Mode15.3.3. Creating and Editing a Basic Schema15.3.3.1. Basic Schema Editing Tasks15.3.3.2. Creating a Cube15.3.3.3. Choosing a Fact Table15.3.3.4. Adding Measures15.3.3.5. Adding Dimensions15.3.3.6. Adding and Editing Hierarchies and Choosing Dimension Tables15.3.3.7. Adding Hierarchy Levels15.3.3.8. Associating Cubes with Shared Dimensions15.3.3.9. Adding the DVD and Customer Dimensions15.3.3.10. XML Listing15.3.4. Testing and Deployment15.3.4.1. Using the MDX Query Tool15.3.4.2. Publishing the Cube15.3.5. Schema Design Topics We Didn't Cover15.4. Visualizing Mondrian Cubes with JPivot15.4.1. Getting Started with the Analysis View15.4.1.1. Using the JPivot Toolbar15.4.2. Drilling15.4.2.1. Drilling Flavors15.4.2.2. Drill Member and Drill Position15.4.2.3. Drill Replace15.4.2.4. Drill Through15.4.3. The OLAP Navigator15.4.3.1. Controlling Placement of Dimensions on Axes15.4.3.2. Slicing with the OLAP Navigator15.4.3.3. Specifying Member Sets with the OLAP Navigator15.4.3.4. Displaying Multiple Measures15.4.4. Miscellaneous Features15.4.4.1. MDX Query Pane15.4.4.2. PDF and Excel Export15.4.4.3. Chart15.5. Enhancing Performance Using the Pentaho Aggregate Designer15.5.1. Aggregation Benefits15.5.2. Extending Mondrian with Aggregate Tables15.5.3. Pentaho Aggregate Designer15.5.4. Alternative Solutions15.6. Summary
16. Data Mining with Weka
16.1. Data Mining Primer16.1.1. Data Mining Process16.1.2. Data Mining Toolset16.1.2.1. Classification16.1.2.2. Clustering16.1.2.3. Association16.1.2.4. Numeric Prediction (Regression)16.1.2.5. Data Mining Algorithms16.1.3. Training and Testing16.1.4. Stratified Cross-Validation16.2. The Weka Workbench16.2.1. Weka Input Formats16.2.2. Setting up Weka Database Connections16.2.3. Starting Weka16.2.4. The Weka Explorer16.2.5. The Weka Experimenter16.2.6. Weka KnowledgeFlow16.3. Using Weka with Pentaho16.3.1. Adding PDI Weka Plugins16.3.2. Getting Started with Weka and PDI16.3.2.1. Data Acquisition and Preparation16.3.2.2. Creating and Saving the Model16.3.2.3. Using the Weka Scoring Plugin16.4. Further Reading16.5. Summary
17. Building Dashboards
17.1. The Community Dashboard Framework17.1.1. CDF, the Community, and the Pentaho Corporation17.1.1.1. CDF Project History and Who's Who17.1.1.2. Issue Management, Documentation, and Support17.1.2. Skills and Technologies for CDF Dashboards17.2. CDF Concepts and Architecture17.2.1. The CDF Plugin17.2.1.1. The CDF Home Directory17.2.1.2. The plugin.xml File17.2.1.3. CDF JavaScript and CSS Resources17.2.2. The .xcdf File17.2.3. Templates17.2.3.1. Document Template (a.k.a. Outer Template)17.2.3.2. Content Template17.3. Example: Customers and Websites Dashboard17.3.1. Setup17.3.1.1. Creating the .xcdf File17.3.1.2. Creating the Dashboard HTML File17.3.1.3. Boilerplate Code: Getting the Solution and Path17.3.1.4. Boilerplate Code: Dashboard Parameters17.3.1.5. Boilerplate Code: Dashboard Components17.3.1.6. Testing17.3.2. Customers per Website Pie Chart17.3.2.1. Customers per Website: Pie Chart Action Sequence17.3.2.2. Customers per Website: XactionComponent17.3.3. Dynamically Changing the Dashboard Title17.3.3.1. Adding the website_name Dashboard Parameter17.3.3.2. Reacting to Mouse Clicks on the Pie Chart17.3.3.3. Adding a TextComponent17.3.4. Showing Customer Locations17.3.4.1. CDF MapComponent Data Format17.3.4.2. Adding a Geography Dimension17.3.4.3. Location Data Action Sequence17.3.4.4. Putting It on the Map17.3.4.5. Using Different Markers Depending on Data17.3.5. Styling and Customization17.3.5.1. Styling the Dashboard17.3.5.2. Creating a Custom Document Template17.4. Summary

Content preview from Pentaho® Solutions: Business Intelligence and Data Warehousing with Pentaho and MySQL®

Chapter 10. Designing Pentaho Data Integration Solutions

In the previous chapter, we introduced a set of tools and utilities that make up Pentaho Data Integration (PDI). We gave some extra attention to the data integration design tool Spoon, and provided two walkthroughs to familiarize the reader with its user interface. In this chapter, you learn how to put these skills to use to build the transformations and jobs that are used to fill the World Class Movies data warehouse.

This chapter offers a hands-on approach, focusing on common problems and practical solutions. Although we use the World Class Movies data warehouse as an example, we believe most of the problems encountered are common to most data warehouses. The methods used in this chapter are by no means the only possible solution. Rather we have chosen a number of approaches to acquaint you with the most often needed features of Pentaho Data Integration.

The best way to read this chapter is to download all PDI transformations and jobs from this book's website at www.wiley.com/go/pentahosolutions so you can easily open them, check any details, run a preview, or more.

Note

In addition to the samples and exercises discussed in this chapter, you are encouraged to take a look at the samples included with PDI. You can find them in the samples directory which is located in the PDI home directory. The samples directory itself contains a jobs and a transformations subdirectory containing sample jobs and transformations. The samples

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Pentaho 3.2 Data Integration: Beginner's Guide

María Carina Roldán

Cloud Computing Using Oracle Application Express: Develop Internet-Facing Business Applications Accessible Anywhere and Anytime

Riaz Ahmed

Learning Pentaho CTools

Miguel Gaspar

Pentaho Data Integration Beginner's Guide - Second Edition

María Carina Roldán

Publisher Resources

ISBN: 9780470484326Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Pentaho® Solutions: Business Intelligence and Data Warehousing with Pentaho and MySQL®

by Roland Bouman, Jos van Dongen

Chapter 10. Designing Pentaho Data Integration Solutions

Note

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.