Getting Started with Pentaho BI Suite 4.

This document is copyright 2011 Pentaho Corporation. No part may be reprinted without written permission from Pentaho Corporation. All trademarks are the property of their respective owners.

Help and Support Resources

If you have questions that are not covered in this guide, or if you would like to report errors in the documentation, please contact your Pentaho technical support representative. Support-related questions should be submitted through the Pentaho Customer Support Portal at http://support.pentaho.com. For information about how to purchase support or enable an additional named support contact, please contact your sales representative, or send an email to [email protected]. For information about instructor-led training on the topics covered in this guide, visit http://www.pentaho.com/training.

Limits of Liability and Disclaimer of Warranty

The author(s) of this document have used their best efforts in preparing the content and the programs contained in it. These efforts include the development, research, and testing of the theories and programs to determine their effectiveness. The author and publisher make no warranty of any kind, express or implied, with regard to these programs or the documentation contained in this book. The author(s) and Pentaho shall not be liable in the event of incidental or consequential damages in connection with, or arising out of, the furnishing, performance, or use of the programs, associated instructions, and/or claims.

Trademarks
Pentaho (TM) and the Pentaho logo are registered trademarks of Pentaho Corporation. All other trademarks are the property of their respective owners. Trademarked names may appear throughout this document. Rather than list the names and entities that own the trademarks or insert a trademark symbol with each mention of the trademarked name, Pentaho states that it is using the names for editorial purposes only and to the benefit of the trademark owner, with no intention of infringing upon that trademark.

Company Information
Pentaho Corporation Citadel International, Suite 340 5950 Hazeltine National Drive Orlando, FL 32822 Phone: +1 407 812-OPEN (6736) Fax: +1 407 517-4575 http://www.pentaho.com E-mail: [email protected] Sales Inquiries: [email protected] Documentation Suggestions: [email protected] Sign-up for our newsletter: http://community.pentaho.com/newsletter/

Contents
Welcome....................................................................................................................................4
Finding More Resources...............................................................................................................................4

Before You Begin...................................................................................................................... 5

Components Included in this Release.......................................................................................................... 5 Thin Client Tools................................................................................................................................ 5 Design Tools...................................................................................................................................... 6 Server Applications............................................................................................................................ 7 System Requirements.................................................................................................................................. 7 Software Requirements..................................................................................................................... 7 Hardware Requirements.................................................................................................................... 8

Installing the Pentaho BI Suite...................................................................................................9

Logging into the Pentaho User Console....................................................................................................... 9 Starting and Stopping the Servers................................................................................................................9 Accessing the Client Tools...............................................................................................................10

Examining Sample Reports..................................................................................................... 11

Inventory List Report...................................................................................................................................11 Top Five Product Lines by Territory............................................................................................................12 Regional Sales Performance Dashboard................................................................................................... 14

Creating an Interactive Report.................................................................................................18

Refining Your Interactive Report.................................................................................................................18 Adding a Filter to Your Report.................................................................................................................... 19 Saving Your Interactive Report...................................................................................................................20

Creating a New Analyzer Report............................................................................................. 21 Working with Dashboards........................................................................................................24

Defining Your Dashboard Look-and-Feel................................................................................................... 24 Adding Data to Your Dashboard.................................................................................................................24 Saving Your Dashboard..............................................................................................................................26 Editing Your Dashboard..............................................................................................................................27

Defining a Database Connection............................................................................................. 29

Adding a JDBC Driver.................................................................................................................................29

Adding Data Sources...............................................................................................................31

Connecting to Your Database.....................................................................................................................31 Creating a Database Table(s) Data Source (Reporting and Analysis)....................................................... 32 Customizing the Data Source..................................................................................................................... 35

Troubleshooting Your Installation............................................................................................ 38

Verifying Your Installation........................................................................................................................... 38 Locating Your Installation Summary................................................................................................ 38 Correcting an Unable to Connect Error...................................................................................................... 39 Correcting a License Not Found Error........................................................................................................ 39 Resolving Port-Related Conflicts................................................................................................................ 39

From Evaluation to Implementation......................................................................................... 40

| TOC | 3

Welcome
This guide provides you with a brief overview of the client tools and server applications that make up the Pentaho BI Suite. Also included, are step-by-step instructions for installing the Pentaho BI Suite on Windows devices. Post installation walk-throughs include a quick introduction to Sample reports and exercises for creating a simple Interactive Report and a Pentaho Analyzer report. You will also learn how to add the reports you created to a dashboard and how to define a data source. Once you complete the installation and exercises, you will be able to start taking advantage of many of the features that are included in this release of the Pentaho BI Suite.

Important: The scope of this document is limited to the topics described in the paragraph above. If you need further information deploying the Pentaho BI Suite or have custom configuration requirements, contact your Pentaho Sales Representative or send an email to Support.

Finding More Resources

Business intelligence implementations can be complex. For this reason, we provide you with tools that support you through your evaluation period and beyond. Your trial evaluation entitles you with access to the Pentaho Knowledge Base where you can find articles, manuals, and tech tips that cover basic and advanced topics associated with implementing the Pentaho BI Suite. In addition, the Pre-Sales portal provides you with access to recorded Webcasts that give you basic understanding of the features and functionality offered by the Pentaho BI Suite. Note: Refer to your Welcome email for details about how to access the Knowledge Base and Pre-Sales portal.

Successful implementations start with training and support. Pentaho trainers are experts in the industry and offer a wide range of affordable classroom, online, and on-site training services to meet your knowledge requirements. Our support team will help you become productive quickly.

4 | Pentaho BI Suite Official Documentation | Welcome

Before You Begin

The sections that follow provide you with information about the Pentaho components included in this release and system requirements for installation.

Components Included in this Release

The diagram below provides you with a simple architectural view of the components that make up the Pentaho BI Suite. A brief description of each component is included in the section that follow.

Thin Client Tools

he Pentaho User Console is the primary web-based end user interface to view, create, and schedule reports, Interactive reports, Analyzer reports and dashboards as well as applying access permissions to them. Design tool Pentaho Interactive Reporting Description Pentaho Interactive Reporting provides a highly interactive and easy to use web-based design interface for the casual business user to create simple and ad hoc operational reports, without depending on IT or report developers. Feature highlights: Accesses Pentaho Metadata data sources. Easy and live interaction with reports, designed specifically for the most casual type of user Drag and drop designer to add, move, and delete fields directly within the report canvas Inline formatting, filtering, sorting, grouping, aggregations and summary calculations Pentaho BI Suite Official Documentation | Before You Begin | 5

Design tool

Pentaho Analyzer

Description WYSIWYG (What You See Is What You Get) report design and editing Template-based report creation for immediate productivity Pentaho Analyzer provides intuitive, interactive analytical reporting letting non-technical business users quickly understand business information against Pentaho Analysis data sources. Feature highlights: Accesses Pentaho Analysis data sources Web-based, drag-and-drop report creation Advanced sorting and filtering Customized totals and user-defined calculations Chart visualizations Dashboard Designer empowers business users to create rich, interactive dashboards with little or no training. Feature highlights: Creating a dashboard is as simple as selecting your layout, theme, and the content you want to display. Include any type of Pentaho reports, external web pages, or dashboard internal elements (charts and data grid) that access Pentaho Metadata data sources. Add dynamic filter controls making it easy for end users to filter the entire contents of their dashboard using a simple pick list Drive content within a dashboard using other dashboard content

Pentaho Dashboard Designer

Design Tools
The design tools are desktop applications that allow you to create a rich reporting environment: Design tool Report Designer Description Report Designer is a visual design environment that makes it easy for report authors to quickly create sophisticated pixel perfect reports against a wide range of data sources addressing the demands of operational, financial and production reporting. These reports can be executed as a standalone report within the User Console or used within a Pentaho Dashboard. In addition, it creates detailed charts and Pentaho Interactive Reporting and Report Wizard templates. It is geared towards experienced and power users, who are familiar with the report design concepts and the data sources used. Data Integration is an intuitive, graphical, drag and drop design environment that provides powerful Extraction, Transformation and Loading (ETL) features. In addition, Pentaho Data Integration provides you with Agile BI capabilities that collapse the multi-step and lengthy BI project cycles into a single integrated design, modeling, and visualization process. Agile BI drives close collaboration between BI application developers and endusers. Metadata Editor builds Pentaho metadata data sources, a data model representation of a relational database where business users can create queries without having to know SQL. Pentaho Dashboards and Pentaho Interactive

Pentaho Data Integration

Pentaho Metadata Editor

6 | Pentaho BI Suite Official Documentation | Before You Begin

Design tool

Pentaho Schema Workbench

Pentaho Aggregation Designer

Pentaho Design Studio

Description Reporting are primary tools to access this data source. Additionally, a data model designer can tag useful attributes to secure or format columns in a data model which can be inherited in a report (prpt) and interactive report (prpti). Schema Workbench builds Pentaho Analysis data sources, (ROLAP cubes) facilitating data exploration and analysis for business users without having to know MDX. Pentaho Aggregation Designer is a graphical environment used to increase query performance of a Pentaho Analysis (Mondrian OLAP) schema through the creation of aggregate tables. Design Studio is used to create Action Sequences (.xaction). Action Sequences define lightweight, success oriented process flows within the Pentaho BI Server. It enables full customization and integration between all components within the Pentaho BI Suite.

Server Applications
The BI Server functions as a Web-based report management system, application integration server, and lightweight workflow engine. The BI Server supports the Pentaho User Console, primarily; it serves users with reporting, analysis, and dashboard content and includes the services for security, scheduling, Pentaho Metadata and Pentaho Analysis. The Enterprise Console Server supports the Enterprise Console and includes services for security, scheduling and repository management and the configuration of the BI Server. The Data Intergration Server (DI Server) is a dedicated, enterprise class server for ETL and Data Integration. It is used to execute Data Integration jobs and transformation and provides services such as scheduling and content management (including revision history and security integration).

System Requirements
Below are the system requirements for each Pentaho component included in this release:

Software Requirements
Note: The system requirements listed below apply to the BI Suite. The BI Suite graphical installation utility, however, will only work on Windows or Linux. In terms of operating systems, Windows (XP SP2, 2008, 7) modern Linux distributions (SUSE Linux Enterprise Desktop and Server 10 and Red Hat Enterprise Linux 5 are officially supported, but most others should work), Solaris 10, and Mac OS X 10.5 (and newer) are all officially supported. The BI Suite installer will provide you with a Sun Java Runtime Environment (JRE) version 1.6 (sometimes referenced as version 6.0) installed. If you have other versions installed, they will be ignored. Workstations will need to have reasonably modern Web browsers to access Pentaho's Web interface. Internet Explorer 8 or higher; Firefox 4.1 or higher; and Safari 5 or higher will all work. Note: The Pentaho User Console may have rendering issues in Firefox 3.5 and Safari 4.x.

Your environment can be either 32-bit or 64-bit as long as it meets the above requirements. The aforementioned configurations are officially supported by Pentaho. Other operating systems such as FreeBSD and OpenBSD; other Java virtual machines like Blackdown; other application servers such as Liferay and Websphere; and other Web browsers like Opera may work without any problems. However, the Pentaho support team will probably not be able to help you if you have trouble installing or using the BI Suite under these conditions. Note: Some Pentaho client tools, such as Metadata Editor, Aggregation Designer, Pentaho Data Integration, and Design Studio, require that the Eclipse SWT JAR be in your Java classpath. This can be an issue in Pentaho BI Suite Official Documentation | Before You Begin | 7

scenarios where standalone client tools are installed onto a machine that does not also have the BI Server installed, particularly on platforms other than Windows and Linux. Note: The Pentaho Reporting engine requires a graphical environment in order to create charts. If you are installing the BI Server onto a headless Linux, BSD, or Solaris server and do not have X11R6 on it, you should install the Xvfb package on your server to satisfy the charting dependency.

Hardware Requirements
The Pentaho BI Suite does not have strict limits on computer or network hardware. As long as you meet the minimum software requirements (note that your operating system will have its own minimum hardware requirements), Pentaho is hardware agnostic. There is, however, a recommended set of minimum system specifications: Server: RAM: at least 2GB Hard drive space: at least 2GB for the software, and more for solution and content files Processor: dual-core AMD64 or Intel EM64T

Workstation: RAM: at least 1GB Hard drive space: at least 1GB for the software, and more for solution and content files Processor: dual-core AMD64 or Intel EM64T

It's possible to use less capable machines, but in most realistic scenarios, the too-limited system resources will result in an undesirable level of performance. Your environment does not have to be 64-bit, even if your processor architecture supports it; while all modern desktop, workstation, and server machines have 64-bit processors, they often ship by default with 32-bit operating systems. If you want to run the Pentaho BI Suite in a pure 64-bit environment, you will have to install a 64-bit operating system, ensure that your solution database and Java Runtime Environment are 64-bit, and install the BI Suite via the 64-bit graphical installer, or through the archive-based or manual deployment methods. Note: A 32-bit JRE has a hard memory limit of 2GB (1.5GB on Windows), so if you have 2GB or more of RAM, you must use a 64-bit JRE on a 64-bit operating system to take full advantage of it. This means that, through architectural limitations, a 32-bit environment will likely be under-powered for a production BI Server deployment.

8 | Pentaho BI Suite Official Documentation | Before You Begin

Installing the Pentaho BI Suite

It is assumed that you will follow the default installation instructions and that you are installing to a local device (localhost). If you have have custom configuration requirements, such as installing the Pentaho BI Suite on a remote server, contact your Pentaho Sales Representative or send an email to Support. Follow the instructions below as you go through each step in the installation wizard. See Troubleshooting Your Installation in the unlikely event you run into port conflict issues. Note: You must disable any anti-spyware software before installing the Pentaho BI Suite. Some types of software firewalls and anti-virus scanners may also block the BI Server or prevent it from operating normally. 1. 2. 3. 4. 5. Click Next in the Welcome page. Read and accept the License Agreement. Click Default to accept the default installation. Specify the location in which to install the Pentaho BI Suite or accept the default. Type and retype password to set the master password for required users, the repository "root" user, the BI server publish password, and the admin user for the Pentaho Enterprise console. 6. Click Next to start installing the Pentaho components. 7. Once the installation is complete, you can choose to view the installation summary and launch the Pentaho User Console. Accept the default choices. This document and Pentaho User Console home page appears. Some installations may prevent the browser from opening. In these instances, you must open the console manually. See instructions for starting the Pentaho User and Pentaho Enterprise consoles under Verifying Your Installation. Keep your console open and available as you step through the exercises in this document.

Logging into the Pentaho User Console

The Pentaho User Console opens automatically after installation. Below are instructions for logging into the Pentaho User Console if you inadvertently close the browser window.

1. Go to Start -> Programs -> Pentaho Enterprise Edition -> User Console Login, or open a browser and go to http://localhost:8080/ or or the hostname, IP address, or domain name of the machine you installed the BI Server to. 2. Enter the credentials,joe/password (all lowercase letters), to open the Pentaho User Console launch page. Joe is a sample user with administrative privileges to the Pentaho User Console. The Pentaho User Console launch page appears.

Starting and Stopping the Servers

To start or stop any of the servers in the Pentaho BI Suite, go to Start -> All Programs -> Pentaho Enterprise Edition -> Server Management; then, select the server you want to start or stop. Pentaho BI Suite Official Documentation | Installing the Pentaho BI Suite | 9

Accessing the Client Tools

To access the Design (client) tools in the Pentaho BI Suite, go to Start -> Programs -> Pentaho Enterprise Edition > Design Tools; then, select the client tool.

10 | Pentaho BI Suite Official Documentation | Installing the Pentaho BI Suite

Examining Sample Reports

The Steel Wheels sample reports included in your installation of the Pentaho BI Suite provide you with real world examples of reports you can create using the both the Web-based and client-based reporting tools. Here we will highlight a few reports and explain them.

Inventory List Report

This report was created using Report Designer; you can find it in the Pentaho User Console solution repository under Steel Wheels -> Reporting -> Inventory List. You can examine the source report in Report Designer. Go to Start -> Programs -> Pentaho Enterprise Edition > Design Tools -> Report Designer. When Report Designer Welcome page opens, go to Samples -> Operational Reports and select Inventory List. Inventory list features: Versatile layout capabilities allows the mixing of columnar and form type layouts. Flexible design capabilities where subsequent repeating rows field element can be hidden, formatted or styled and elements can have dynamically set width and position. Conditionally color elements by setting the background color based on a formula expression. Generate sophisticated bar codes for all major symbologies; EAN, UPC, ISBN, EAN13, Code39, Code128, UPCE to name a few. Produce data driven hyperlinks to access external content. Produce data driven hyperlinks to launch other Pentaho reports. Pick from an assortment of selection controls (buttons, drop down list boxes, list boxes, checkboxes, options) to pass values to report parameters to drive report data. Control when elements display on a report based on report parameters. Fine tune the presentation of report sections (Table of Contents) or elements based on the output type.

Pentaho BI Suite Official Documentation | Examining Sample Reports | 11

Top Five Product Lines by Territory

The Top 5 Product Lines by Territory report was created with Pentaho Analyzer. This report is located under Steel Wheels -> Analysis -> Top 5 Product Lines by Territory. The Top 5 Product Lines by Territory Analyzer report features: One click subtotal items for a dimension. Leverage Pentaho Analysis for fast Top/Bottom queries. Quickly visualize and compare values using the Data Bar. Add color scale range to easily determine outliers and median values. Drill on a data cell to view supporting data.

12 | Pentaho BI Suite Official Documentation | Examining Sample Reports

Pentaho BI Suite Official Documentation | Examining Sample Reports | 13

Regional Sales Performance Dashboard

Regional Sales Performance, shown below, is a dashboard which contains three filters, two charts, (created using Chart Designer Sales Mix and Recent Sales), and an Analyzer report (Product Performance). This dashboard is located under Steel Wheels -> Dashboards -> Regional Sales Performance. Dashboards feature an interactive drag-and-drop interface that allows business users to quickly assemble content into a dashboard and the ability to link content within the dashboard or outside of the dashboard. Regional Sales Performance dashboard features: Pick from an assortment of selection controls (buttons, drop down list boxes, list boxes, checkboxes, options) to pass parameters to any dashboard panel.

14 | Pentaho BI Suite Official Documentation | Examining Sample Reports

Build dashboard interactive flash-based charts (bar, line, pie, area, dial).

Pentaho BI Suite Official Documentation | Examining Sample Reports | 15

Display any Pentaho generated content.

16 | Pentaho BI Suite Official Documentation | Examining Sample Reports

Pentaho BI Suite Official Documentation | Examining Sample Reports | 17

Creating an Interactive Report

Follow the instructions below to begin creating a simple Interactive Report. 1. In the Pentaho User Console Launch page, click New Report. (Alternatively, go to File > New > Interactive Report in the Pentaho User Console Launch page or click New Interactive Report in the toolbar.) 2. Select Orders from the list under Select a Data Source. As you become more familiar with Interactive Reporting features, you can add your own relational or flat file (CSV) data sources. Note: Click Get Started in the Interactive Report overview box to close it. Click Hide Tips in the lower right corner of the report canvas for easier viewing. The categories and fields associated with the Orders data source are listed under Data right pane. 3. Click-and-drag Product Name, (under Products), from the right pane, onto the report canvas. When dragging, notice the green horizontal line that appears as you are about to drop the field into the report.

4. Double-click on Product Scale, (under Products). 5. Double-click on Quantity Ordered, (under Orders). 6. Double-click Total, (under Orders). Your report should now have four columns: Product Name, Product Scale, Quantity Ordered, and Total. 7. Click and drag Territory, (under Customers), above the column headers in the report. This will group the Products by Territory. 8. Click and drag Product Vendor, (under Products), above the column headers in the report. This will group the Products by Territory and then by Product Vendor. Your new interactive report shows sales by territory, and the volume of sales and quantities sold for each product by vendor.

Refining Your Interactive Report

Follow the instructions below to refine some of the elements in your Interactive Report. 1. Click to select the data cells of the Total column in your report. Diagonal lines appear when you have selected an element in your report. 2. In the right pane, click the Formatting tab. The formatting options appear. 3. Under Numeric Format, select $#,### from the list. The numeric format is applied to the Total column. 4. Adjust your column widths. Select the column and move your cursor up to the left of the column header until you see a short vertical line and a small arrow. Move the arrow left or right to resize the column.

18 | Pentaho BI Suite Official Documentation | Creating an Interactive Report

5. Click the Total column header and drag the entire column next to the Product Name column.

You can also move a column by clicking on the small arrow next to the column name and selecting Move -> Right (or Left) from the menu. 6. Double-click the Total column header to display an input text box. 7. Type Sales in the text box and press <Enter>. 8. Double-click the report name, (Untitled), and type Vendor Sales Report in the text box, then press <Enter>. 9. Click the small arrow next to the Sales column header to display the context menu. 10.Select Summary -> Sum so that the report displays the total sales for each vendor. 11.Click the small arrow next to the Sales column header to display the context menu. Select Sort -> Descending to display sales figures from the highest to the lowest. 12.Double-click the group footer, and rename it Total . You now have a more complex interactive report.

Adding a Filter to Your Report

Filters are used to restrict or limit the data that is presented in a report. For example, you can limit the data in a report to a certain region, such as West or South. In this example, you'll modify your report to limit the data to the North America region. Follow the instructions below to add a filter to your report: 1. Under the Data tab, right-click Territory (from the Customer category) and select Filter. The Filter (on Territory) dialog box appears. 2. In the Filter dialog box, click Select from a list (Includes, Excludes). The list of filter values, (APAC, EMEA, Japan, NA) appears. Pentaho BI Suite Official Documentation | Creating an Interactive Report | 19

3. Select NA (North America) from the list in the left pane, then click

(Add Selected) to the right pane.

A small number one (1) appears on the Filter icon in the toolbar indicating that the report contains a single filter. A filter icon also appears next to the Territory field name under Data in the right pane. 4. Save your report. You have successfully added a filter to a report.

Saving Your Interactive Report

Follow the instructions below to save your interactive report: 1. Click (Save) in the Pentaho User Console toolbar. The Save dialog box appears. 2. In the File Name text box, type Vendor Sales Report Exercise. 3. Double-click the Steel Wheels folder, then open the Reporting folder. The path to the folder which will contain your report appears in the Location text box. 4. Click Save. 5. Close the report. Note: You can compare your completed report with the sample report, Vendor Sales Report, available under Reporting in the solution browser. To learn more about Interactive Reporting features, see the Pentaho User Console Guide. Click Help -> Documentation in the Pentaho User Console.

20 | Pentaho BI Suite Official Documentation | Creating an Interactive Report

Creating a New Analyzer Report

Follow the instructions below to create an Analyzer report with the Steel Wheels sample data. 1. In the Pentaho User Console menubar, go to File -> New and select Analyzer Report. The Pentaho Analyzer design tool will start. 2. Select the Steel Wheels schema and the SteelWheelsSales cube from the proceeding lists, then click OK. A blank Analyzer report appears. 3. Click and drag the Territory and Sales fields to the workspace. 4. Click and drag the Years field, dropping it above the Territory and Sales columns in the report. A line marker appears as shown below:

5. Click and drag the Line field and drop it next to Territory. 6. Right-click the Territory column and select Show Subtotals. 7. Right-click the Sales column and select Conditional Formatting -> Data Bar - Green.

8. Right-click the Sales column and select User Defined Number -> % of Rank, Running Sum.., then in the dialog box select % of Sales. Click Next. 9. Enable Each Territory Column/Row Subtotal (Subtotal is 100%). Click Done.

Pentaho BI Suite Official Documentation | Creating a New Analyzer Report | 21

10.Click (Filter) in the toolbar to create a filter for your report. When the filter area appears at the top of the report, click and drag the Territory field (under Available Fields on the right) into the filter area. 11.In the Filter on Territory dialog box, select APAC from the list and click the small green arrow to move it to the box on the right. 12.Enable Parameter Name and type region (lowercase) as your parameter name in the text box. Click OK.

The report updates and displays sales data for APAC exclusively. Click the line that separates each column to adjust it for better viewing. Move the line right or left as needed.

13. At this point you have a functioning report and you can view your data in chart form. Click (Switch to Chart Format) to examine your report data in a chart format. The default display is a bar chart but if you click (Choose Another Chart Type) you can select a different chart type to display your data.

22 | Pentaho BI Suite Official Documentation | Creating a New Analyzer Report

Click 14.

Switch to Table Format to return to your table.

Save your report before continuing the exercise. In the Pentaho User Console, click (Save As). When the Save As dialog box appears, save your report as Territory Line - Sales under /Steel Wheels/Analysis and click Save.

You've successfully created an Analyzer report from scratch.

Pentaho BI Suite Official Documentation | Creating a New Analyzer Report | 23

Working with Dashboards

A dashboard is a management tool that helps you measure your company's "vital signs" quickly and efficiently. Instead of relying on outdated paper reports to make important business decisions, a dashboard delivers current and relevant information to employees through the use of a combination of graphs, scorecards, and report components. Because dashboards tend to be highly visual, they are easy to read and interpret providing employees quick access to information that helps them answer critical questions about the performance of their specific departments. With just a few mouse clicks, employees will know if a plan they are implementing is effective, how it compares to the competition, and any other metrics you define. Best of all, they can access this type of information easily from any location around the world over the Web. Dashboard Designer empowers business users to create rich, interactive dashboards with little or no training. Creating a dashboard is as simple as selecting your layout, theme, and the content you want to display. This content can include reports, charts, or even interactive Pentaho Analyzer reports. Dashboard Designer also provides you with the ability to add dynamic filter controls making it easy for dashboard consumers to filter the entire contents of their dashboard using a simple pick list. You can quickly access data in your own relational database or CSV flat file for evaluation or testing dashboard capabilities. To find out more, see Adding Data Sources.

Defining Your Dashboard Look-and-Feel

You must be logged on to the Pentaho User Console. Follow the instructions below to create a new dashboard: 1. In the Pentaho User Console Launch page, click New Dashboard. Alternatively, go to File > New > Dashboard in the Pentaho User Console Launch page or click New Dashboard in the toolbar. The New Dashboard page appears. 2. In the edit pane (lower portion of the page), click Properties, and type My Dashboard in the Page Title text box. This is the title for your dashboard page. The name you entered appears on the top left corner of the dashboard. This name helps you identify the page if you want to edit, copy, or delete it later. 3. Click Templates and use the scrollbar to examine the different dashboard layouts. The 2 by 2 template is selected by default and is the layout you will use for this exercise. 4. Click Theme to select a theme for your dashboard. Select the theme of your choice. The theme you selected is applied to your dashboard.

Adding Data to Your Dashboard

Follow the instructions below to add data to your dashboard. 1. Select the upper left panel in the Dashboard Designer. 2. Click Reporting in your samples repository (Steel Wheels) to display the list of available reports. 3. Click-and-drag the Vendor Sales Report into the dashboard panel.

24 | Pentaho BI Suite Official Documentation | Working with Dashboards

4. 5.

6. 7.

a) Type Product Vendor, under Title, in the lower portion of your dashboard and click Apply. Product Vendor is the title of the dashboard panel. Click Analysis in your samples repository (Steel Wheels) to display the list of available reports. Click-and-drag the Territory Line - Sales Analyzer report into the upper right dashboard panel. a) Type Territory Line Sales, under Title, in the lower portion of your dashboard and click Apply. Territory Line Sales is the title of the dashboard panel. Click General Settings in the lower portion of the page and select the 2 Column template. The content of your dashboard displays in the new template. Click General Settings and select Filter. a) Enable Show Filter Toolbar. A Filter area appears at the top of the dashboard. b) Click to add a filter.

The Filter dialog box appears. c) In the Name text box, type Region. d) Under Data Type, select Metadata List. e) Under Connections, click Select, then choose the Orders data source and click OK. The Query Editor opens. 8. In the Query Editor, expand the Customer category on the left and select Territory from the list. Click the small arrow to move Territory under Selected Columns.

Pentaho BI Suite Official Documentation | Working with Dashboards | 25

a) Click the small arrow in the center of the Query Editor to add Territory to Conditions. b) Under Comparison select is not null.

c) Click OK to exit the Query Editor. 9. Click OK to exit the Filter dialog box and return to the dashboard. 10.Select the dashboard panel that contains your Territory Line Sales Analyzer report. a) Under the Parameters tab, select Region as the a Source value for your region parameter and click Apply.

11.Select the dashboard panel that contains Product Vendor report and change its parameter APAC to Region. a) Test the filter to make sure that the content in each dashboard is being filtered on Region.

Saving Your Dashboard

Follow the instructions below to save your dashboard: 1. In the toolbar, click (Save) to open the Save dialog box.

26 | Pentaho BI Suite Official Documentation | Working with Dashboards

2. In the File Name text box, type My First Dashboard. 3. Save your file in the .../steel-wheels/dashboards directory. You can do this by double clicking the Steel Wheels folder and the Dashboards folder. 4. Click Save. Note: To learn more about Dashboard features, see the Pentaho User Console Guide. Click Help -> Documentation in the Pentaho User Console.

Editing Your Dashboard

You just created a dashboard chart that contained an Interactive Report and an Analyzer Report. In this exercise, you will edit your dashboard by adding a chart. Before you can add a chart you must select a data source that contains the data you want to use. You must then define the data that will be displayed in the chart. 1. Browse the repository to locate My First Dashboard under Dashboards. The dashboard opens. 2. Click the edit button in the Pentaho User Console toolbar. 3. Change your dashboard template to 1 and 2. 4. In the Dashboard, choose a dashboard panel and click (Insert) and select Chart. The Select a Data Source dialog box appears. 5. Select the Orders data source from the list of available choices. The Orders data source contains the content you want to display in your chart. The Query Editor opens. The Query Editor allows you to retrieve dynamic data from a database for display in a chart. Defining your query is the first step in ensuring that the correct data is selected 6. Expand the Products category and click the small arrow to place the Product Line column under Selected Columns. a) Expand the Orders category and click the small arrow to place the Total column under Selected Columns. b) Expand the Customers category and click the small arrow to place Territory column under Conditions. These are the constraints that filter what you are selecting. c) Under Comparison, select in. d) In the Value text box, type {Region}. When you enclose a parameter name with curly braces, you are creating a parameter query. e) In the Default text box, type APAC. When the chart renders, it displays data associated with APAC. APAC is the default value of the parameter. That means users can change the query dynamically by replacing the default, "APAC," with a different territory acronym, (for example, EMEA), when the query runs. You can click Preview in the Query Editor to ensure that the query you created is correct. f) Click OK to exit the Query Editor. The Chart Designer opens. 7. In the Chart Designer, under Type, select Pie Chart. 8. Under Data/Series Column, select Product Line. 9. Under Values/Column, select Total. The pie chart appears. a) Click OK to exit the Chart Designer. The pie chart appears in the dashboard panel. 10.Under Parameters in the dashboard, change the Source value of the Region parameter from APAC to Region and click Apply.

Pentaho BI Suite Official Documentation | Working with Dashboards | 27

11.

Click

in the Pentaho User Console toolbar to exit edit mode, then save your dashboard.

As you select a different territory, the slices in the pie chart update.

28 | Pentaho BI Suite Official Documentation | Working with Dashboards

Defining a Database Connection

The BI Server includes sample data and reports; however, if you are evaluating Pentaho client tools, like Report Designer, you may want to use your own data. You manage database connections in the Pentaho Enterprise Console. To define database connection you must have a JDBC class name for the database driver, a URL (server name, port number, database name), and the user ID and password. Contact your database administrator to get the specific details about your database. To find driver information, see A List of JDBC Drivers.

You must be logged on to the Pentaho Enterprise Console to follow the instructions below. Go to Start -> Programs -> Pentaho Enterprise Edition -> Enterprise Console Login. 1. 2. 3. 4. 5. In the Pentaho Enterprise Console go to Administration > Database Connections. Click the General icon to display basic configuration options. Click the plus sign (+) (add) if you cannot find your data source in the default list. The Add Database Connection dialog box appears. Type an easy to remember connection name This is the name you give the connection (sometimes referred to as a Pentaho Database Connection or named connection. It is also the name you use when creating a connection on a client tool, like Report Designer. 6. Type or select the Driver Class from the list. The database driver name you select depends on the type of database you are accessing. For example, org.hsqldb.jdbcDriver is a sample driver name for a HSQLDB (previously Hypersonic SQL or hsql) database. If your JDBC driver is not available, see Adding a JDBC Driver. 7. Type the User Name and Password required to access your database. 8. Type or select the URL from the list. This is the URL of your database; for example, jdbc:hsqldb:hsql:// localhost/sampledata. JDBC establishes a connection to a SQL-based database and sends and processes SQL statements. 9. Click Test. A success message appears if the connection is established. 10.Click OK to save your entries

Adding a JDBC Driver

Before you can connect to a data source in any Pentaho server or client tool, you must first install the appropriate database driver. Your database administrator, CIO, or IT manager should be able to provide you with the proper driver JAR. If not, you can download a JDBC driver JAR file from your database vendor or driver developer's Web site. Once Pentaho BI Suite Official Documentation | Defining a Database Connection | 29

you have the JAR, follow the instructions below to copy it to the driver directories for all of the BI Suite components that need to connect to this data source. Note: Microsoft SQL Server users frequently use an alternative, non-vendor-supported driver called JTDS. If you are adding an MSSQL data source, ensure that you are installing the correct driver. Backing up old drivers You must also ensure that there are no other versions of the same vendor's JDBC driver installed in these directories. If there are, you may have to back them up and remove them to avoid confusion and potential class loading problems. This is of particular concern when you are installing a driver JAR for a data source that is the same database type as your Pentaho solution repository. If you have any doubts as to how to proceed, contact your Pentaho support representative for guidance. Installing JDBC drivers Copy the driver JAR file to the following directories, depending on which servers and client tools you are using (Dashboard Designer, ad hoc reporting, and Analyzer are all part of the BI Server): Note: For the DI Server: before copying a new JDBC driver, ensure that there is not a different version of the same JAR in the destination directory. If there is, you must remove the old JAR to avoid version conflicts. BI Server: /pentaho/server/biserver-ee/tomcat/lib/ Enterprise Console: /pentaho/server/enterprise-console/jdbc/ Data Integration Server: /pentaho/server/data-integration-server/tomcat/webapps/pentaho-di/ WEB-INF/lib/ Data Integration client: /pentaho/design-tools/data-integration/libext/JDBC/ Report Designer: /pentaho/design-tools/report-designer/lib/jdbc/ Schema Workbench: /pentaho/design-tools/schema-workbench/drivers/ Aggregation Designer: /pentaho/design-tools/agg-designer/drivers/ Metadata Editor: /pentaho/design-tools/metadata-editor/libext/JDBC/ Note: To establish a data source in the Pentaho Enterprise Console, you must install the driver in both the Enterprise Console and the BI Server or Data Integration Server. If you are just adding a data source through the Pentaho User Console, you do not need to install the driver to Enterprise Console. Restarting Once the driver JAR is in place, you must restart the server or client tool that you added it to. Connecting to a Microsoft SQL Server using Integrated or Windows Authentication The JDBC driver supports Type 2 integrated authentication on Windows operating systems through the integratedSecurity connection string property. To use integrated authentication, copy the sqljdbc_auth.dll file to all the directories to which you copied the JDBC files. The sqljdbc_auth.dll files are installed in the following location: <installation directory>\sqljdbc_<version>\<language>\auth\ Note: Use the sqljdbc_auth.dll file, in the x86 folder, if you are running a 32-bit Java Virtual Machine (JVM) even if the operating system is version x64. Use the sqljdbc_auth.dll file in the x64 folder, if you are running a 64-bit JVM on a x64 processor. Use the sqljdbc_auth.dll file in the IA64 folder, you are running a 64-bit JVM on an Itanium processor.

30 | Pentaho BI Suite Official Documentation | Defining a Database Connection

Adding Data Sources

The Data Sources feature allows you to connect to your data so that the data can be used to create reports, (such as Interactive Reports, Analyzer Reports, and Dashboards), in the Pentaho User Console. Using the Data Source Wizard, you can quickly add, edit, and delete CSV File, SQL Query, and Database Table(s) data sources in the Pentaho User Console. Below is a quick description of each data source type: Data Source Type CSV File Description Data originating in a CSV file is extracted and staged in a database table on the Pentaho BI Server. A default Pentaho Metadata (Reporting) Model and a Mondrian (OLAP) Schema are generated for use in Interactive Report, Dashboard, and Analyzer. A SQL query written against a relational database is used to establish the context of the data source. Data available for report creation is confined to the scope of the data sources query. A default Pentaho Metadata (Reporting) Model and a Mondrian (OLAP) Schema are generated for use in Interactive Report, Dashboard, and Analyzer. Data originates in a one or more relational database tables that is often operational or transactional in nature. Using the Data Source Wizard, database tables can be selected and joined. A default Pentaho Metadata (Reporting) Model is generated for use in Interactive Report and Dashboards only. Data originates in one or more relational database tables arranged in a star schema with a single fact table. Using the Data Source Wizard, multiple dimension tables can be selected and joined to the single fact table. A single table containing both fact and dimensional information can also be used for this data source type. A default Pentaho Metadata (Reporting) Model and a Mondrian (OLAP) Schema are generated for use in Interactive Report, Dashboard, and Analyzer.

SQL Query

Database Table(s) Reporting Only

Database Table(s) Reporting and Analysis

The exercises in this section will walk you through creating a Database Table(s) Reporting and Analysis data source.

Connecting to Your Database

To create a Database Tables data source, you must first connect to the database that contains data you want to access, (if a database connection does not already exist). Existing database connections appear in a list under Connection in the Data Source Wizard. Follow the instructions below to connect to a database: 1. In the Pentaho User Console quick launch page click Create New .

The Data Source Wizard opens. 2. Under Data Source Name type, Orders Report and Analysis KT. 3. Under Source Type, select Database Table(s) as your data source type.

Pentaho BI Suite Official Documentation | Adding Data Sources | 31

4. In the lower portion of the wizard page, under Create data source for enable Reporting and Analysis (Requires Star Schema. 5. In the center of the page, under Connection:, click (Add) to define a connection to the database that contains the content you want to access. The Database Connection dialog box appears.

6. In the Connection Name text box, type SampleData. A Connection Name must be easy to remember and must identify the data you are accessing. 7. Under Database Type, select Hypersonic. Text fields for required settings associated with your connection type appear under Settings on the right. 8. Enter the appropriate connection information for your database type: Option Host Name Database Name Port Number User Name Password Description localhost sampledata 9001 (automatically available, by default) pentaho_user password

9. In the Database Connection dialog box, click Test to test your connection and click OK. A success message appears. The connection name appears in the list under Connections in the Data Source Wizard.

Creating a Database Table(s) Data Source (Reporting and Analysis)

Before proceeding make sure that you have selected the SampleData connection and that Reporting and Analysis (Requires Star Schema) option is enabled.

32 | Pentaho BI Suite Official Documentation | Adding Data Sources

To create a Database Table(s) data source for Reporting and Analysis, a star schema is required. In a database, a star schema consists of one or more fact tables that reference any number of dimension tables. A fact table contains business metrics, (numeric data that can be measured), such as Total, Quantity, Budget, and Inventory. In a star schema, the Fact table is the center of the star. A Dimension table may contain Customer Names, Product Names, Employee records, and other miscellaneous data. Dimension tables allow users to browse through specific categories, to summarize data, and to perform drill-down searches.

Follow the instructions below to create the data source. 1. In the Data Source Wizard, click Next. A list of available database tables appears. 2. Press <CTRL+CLICK> to select the ORDERFACT CUSTOMER_W_TER, DIM_TIME, and PRODUCTS tables, then click the small green arrow to move the tables under Selected Tables.

Pentaho BI Suite Official Documentation | Adding Data Sources | 33

3. Under Fact Table, select ORDERFACT and click Next.

Note: The Fact Table must be selected before you can proceed. In a production environment, a database administrator knows how to identify Fact tables. 4. Define how the tables you selected join to each other. All of your Dimension tables must link to your Fact table. You will create a join relationship with each Dimension table on the right to your Fact table (ORDERFACT) on the left. a) Select the CUSTOMERNUMBER field from and click Create Join. The join relationship between CUSTOMER_W_TER (Dimension Table) and ORDERFACT (Fact Table) is created. Both tables share the key field, CUSTOMERNUMBER. b) Select the DIM_TIME table from the list under Right Table, then select the TIME_ID field from the list of key fields on the left. Click Create Join. The join relationship between DIM_TIME (Dimension Table) and ORDERFACT (Fact Table) is created. Both tables share the key field, TIME_ID.. c) Select the PRODUCTS table from the list under Right Table, then select the PRODUCTCODE field from the list of key fields on the left. Click Create Join. The join relationship between the PRODUCTS table and ORDERFACT table is created. Both tables share the key field, PRODUCTCODE.

34 | Pentaho BI Suite Official Documentation | Adding Data Sources

5. Click Finish to create your data source. 6. In the Data Source Created dialog box, enable Customize model now. See Customizing the Data Source for more information.

Customizing the Data Source

During the data source creation process, a default Pentaho Metadata (Reporting) model and a Mondrian (OLAP) Schema (model) are generated. In most cases, you will want to make changes to the default model so that the data is more useful to report consumers; however, the default model can be used as is. The Pentaho Modeler displays two perspectives; the Reporting perspective that contains all of tables organized in categories and fields to be used for Interactive Reporting and Dashboards, and the Analysis perspective that contains all hierarchies and levels to be used for Analyzer reports and visualizations.

Pentaho BI Suite Official Documentation | Adding Data Sources | 35

The Pentaho Modeler is a metadata modeling tool that makes it possible for you to customize the models without having to understand the underlying database structure or having to understand SQL and MDX. If you are not familiar with metadata modeling, think of it this way metadata is simply "data about data." For example, a database contains fields, such as customer name, address, city, and state. Among other things, metadata provides the naming conventions for the fields, describes their size, and specifies what is allowed in the field, such as numeric values only. If you were to hand a database file to a user, without providing the associated metadata, the file would appear as a long string of characters. Metadata defines how data is extracted into fields and records. Click OK in the Data Source Model dialog box to keep your default model. The new data source is added to the list of available data sources that users select when creating a new Interactive Report, a dashboard, or Analyzer report.

36 | Pentaho BI Suite Official Documentation | Adding Data Sources

You can edit ( ) your data source by clicking Manage Existing in the Pentaho User Console launch page. For more information about customizing your data source, see Customizing a Reporting Data Source and Customizing an Analysis Data Source in the Pentaho User Console Guide.

Pentaho BI Suite Official Documentation | Adding Data Sources | 37

Troubleshooting Your Installation

The following tips may help you troubleshoot installation and other related issues.

Verifying Your Installation

After you have successfully installed the Pentaho BI Suite, this section steps you through verifying your installation. Note: As you start and stop the consoles and other Pentaho components, you may see black command prompt windows open or close. These windows display the scripts that run the BI Suite components. You can minimize the command prompt windows so they do not clutter your desktop

Making sure the BI Server and MySQL have started If you selected to launch the User Console or Enterprise Console, the installer automatically starts the BI Server, Solution Repository, and Enterprise Console. You can check to see if the Pentaho BI Server and the MySQL database that contains the Pentaho Solution Repository have started by launching Services from the Control Panel. Go to Start -> Control Panel -> Administrative Tools -> Services. Alternatively, you can type services.msc in the Run command on the Start menu. Under local Services scroll down to Pentaho BI Server. In the image below, the Pentaho BI Server is started on Tomcat. The Pentaho Solution Repository (MySQL) is also started.

Note: Right-click on the BI Server or Solution Repository in the Services window to start or stop them. Alternatively, you can start the BI Server by navigating to Start -> Programs -> Pentaho Enterprise Edition > Server Management -> Start BI Server. The MySQL database starts automatically when you log on to your device.

Locating Your Installation Summary

The installation summary automatically appears when you have completed an installation. It looks similar to the example below. The installation summary provides you with information about what is installed on your computer. In this example, all Pentaho BI Suite components have been installed. The information in the summary helps the support team know what has been installed.

38 | Pentaho BI Suite Official Documentation | Troubleshooting Your Installation

Follow the instructions below before you call support to resolve an issue. The support staff must know what components have been installed. 1. Navigate to the folder that contains Pentaho installation; for example, C:\Program Files\pentaho. 2. Locate and open the following file: installation-summary.txt. 3. Copy the contents of the file and send it, via email, to the support technician as instructed.

Correcting an Unable to Connect Error

If you followed the default installation instructions, the BI Server starts automatically at startup and remains available until you shut your computer down, or, you manually stop the server. If the BI Server is not started, an Unable to Connect error will occur when you try to log onto the Pentaho User Console. This error may also occur if the URL to the Pentaho User Console has been changed. The default URL is, http://localhost:8080/. To correct the issue start the server. Go to Start -> Programs -> Pentaho Enterprise Edition -> Server Management -> Start BI Server. If the problem persists, check the URL to make sure it has not changed. Stopping the BI Server To close the BI Server down after you log out of the Pentaho User Console, go to Start -> Programs -> Pentaho Enterprise Edition -> Server Management -> Stop BI Server

Correcting a License Not Found Error

Your Pentaho installation provides you with licenses to access all BI Suite components for 45 days. If, however, you attempt to use the software after 45 days or you uninstall and try to reinstall the BI Suite, a "License Not Found" error will occur. Contact your Pentaho Sales Representative or send an email to Support to reinstate licensing.

Resolving Port-Related Conflicts

As stated previously, you must be able to assign ports, if necessary, during installation. It is recommended that you contact your system or database administrator for help when you encounter port-related conflicts. Below is the default port information: MySQL Server Tomcat BI Server Tomcat Data Integration Server Enterprise Console Startup Port: 3306 Startup Port: 8080 Startup Port: 9080 Startup Port: 8088 Pentaho BI Suite Official Documentation | Troubleshooting Your Installation | 39

From Evaluation to Implementation

This document was designed to quickly demonstrate basic Pentaho BI Suite Enterprise Edition features and functions. To find out more about the benefits of the Pentaho Enterprise Edition product offerings, contact Pentaho Sales.

40 | Pentaho BI Suite Official Documentation | From Evaluation to Implementation