Data Model

Creating a model from various Data Sources ..

Workshop - Data Model

Data modeling is the foundation of effective business intelligence and analytics. In this comprehensive workshop, you'll master the Data Source Wizard, learning how to transform raw data from CSV files and relational databases into well-structured, analysis-ready data models that power Pentaho's reporting and analysis tools. Whether you're working with simple spreadsheet data or complex star schema databases, you'll gain the skills to create data sources that enable meaningful business insights through Analyzer, Interactive Reports, and Dashboard reports.

In this hands-on workshop, you'll experience the complete data modeling lifecycle, starting with simple CSV file imports and progressing to sophisticated multidimensional models built on relational database tables. You'll learn how to configure data source properties, define staging settings, and design effective data models for both reporting and analysis purposes. As you work through the exercises, you'll master critical concepts including star schema design, dimension hierarchies, measure aggregations, and time dimension properties. You'll also develop the expertise to optimize your data models for performance and usability, ensuring that business users can easily explore and analyze their data with confidence.

What You'll Accomplish:

• Launch and navigate the Data Source Wizard interface in Pentaho User Console

• Create data sources from CSV files with proper encoding and delimiter configuration

• Import and configure database tables with appropriate joins and relationships

• Define fact tables and dimension tables for multidimensional analysis

• Configure measures with appropriate aggregations and formatting

• Build dimension hierarchies with geographic and temporal properties

• Create drill-down paths for Territory, Customer, Product, and Time dimensions

• Apply member properties to enrich dimension levels with descriptive attributes

• Configure time dimension properties with proper format patterns for filtering

• Customize data models for both Interactive Reporting and Analyzer tools

• Export and enhance models for advanced business analytics requirements

By the end of this workshop, you'll have created both a simple CSV-based reporting model and a sophisticated star schema analytical model that demonstrates enterprise-grade data modeling best practices. You'll understand how to structure data for optimal query performance, provide intuitive navigation through dimension hierarchies, and deliver the rich analytical capabilities that business users expect from modern business intelligence tools.

Prerequisites: Pentaho Business Analytics Server with sample database connection (hsqldb_sampledata) configured Estimated Time: 25 minutes

Data Source Wizard

When you first open the model editor, the centre pane of the dialog box displays the active model. This model is organized into categories and fields that represent the tables and columns in your data.

• Creating a data source from a CSV file treats all columns as if they are in a single table, with no limitations on where measures come from or which fields can be grouped in a hierarchy.

• Creating a data source from a database table for Reporting and Analysis requires you to specify a fact table. Only numeric columns from the fact table can be used as measures. Also, all levels in a single hierarchy must come from the same dimension table.

• If you add or edit annotations on the data model in Analyzer, these annotations will not be visible when viewing the model in the Data Source Wizard. For example, if you add a calculated measure to the model in Analyzer, it will not display in the list of measures when viewing the model in DSW. Saving the model in DSW will overwrite any annotations which were added to the model in Analyzer.

---

Launch the Data Source Wizard

1. Log into the Pentaho User Console:

User: Admin

Password: password

1. Click Create New > Data Source or access from Manage Data Sources.

New Data Source

---

Select the model Data Source:

CSV

CSV File

Model from CSV file

1. Download the Checkbook.csv file:

18KB

Checkbook_data.csv

Open

1. Enter Data Source Name: Checkbook

Provide a descriptive name (avoid special characters: / \ : * ? " < > |)

1. Select CSV File as Source Type.

2. Click Import and browse to select your CSV file.

DSW - Import Checkbook_data.csv

1. Configure the following CSV properties:

Property	Value
Data Source Name	Checkbook
Source Type	CSV File
File	/Downloads/Checkbook_data.csv
Encoding	ISO-8859-1
Delimiter	Comma
Enclosure	Double Quote

DSW - Configure Data Source

1. Preview and Verify.

2. Click Next to Continue.

The Staging Settings screen displays a list of columns from your CSV source file. All columns are enabled.

DSW - Staging

1. Edit the following properties:

Name	Type	Source Format	Length	Precision
Date	STRING		15	0
Description	STRING		50	0
Category Desc	STRING		50	0
Category	STRING		5	0
Amount	NUMERIC	#,##0.00	10	2

1. Click: Show File Contents.

File Preview

1. Click Finish.

2. Choose Model Option: Customize model now

Keep default model - Uses the data source as-is

Customize model now - Opens Data Source Model Editor for refinement

---

Select a reporting option:

Configure Model for Interactive & Analyzer reports

Interactive Reporting

Interactive Reporting

Notice that the Wizard puts all the columns in one category. You can use the toolbar buttons to easily create additional categories and move the columns up and down to organize the columns into categories.

1. On the Reporting tab, click the Check book category.

2. In the Properties pane, click in the Category Name field.

3. Rename: Checkbook Category.

DSW - Edit Display Name

1. Format the Amount: #.#

1. Click OK.

---

1. From the User Console Home Perspective, click Create New > Interactive Report.

2. In the Select Data Source window, click Checkbook, and then click OK.

Data Source - Checkbook

1. On the Data tab, notice the Checkbook Category name.

Checkbook Report

If this is the first time you are using Interactive Reporting, you may need to click the Get Started button, and then click to Hide Tips.

1. Add Category Desc, on the Data tab, double-click Category Desc.

2. Add Date, on the Data tab, double-click Date.

3. Add Amount, on the Data tab, double-click Amount.

Analyzer

SQL Queries

Database Table(s)