pentaho工具使用手册教学文稿.docx

Pentaho工具使用手册作者：马腾，李洪宇版本：1.0目录BI 介绍2Pentaho产品介绍3Pentaho产品线设计4Pentaho BI Platform安装4Pentaho Data Integration-Kettle8Pentaho Report Designer12Saiku23Schema Workbench27附件32BI 介绍1. BI基础介绍BI（BusinessIntelligence）即商务智能，它是一套完整的解决方案，利用数据仓库、数据挖掘技术对客户数据进行系统地储存和管理，并通过各种数据统计分析工具对客户数据进行分析，提供各种分析报告，为企业的各种经营活动提供决策信息。其中的关键点是数据管理，数据分析， 支持决策。根据要解决问题的不同，BI系统的产出一般包括以下三种：2. BI系统的产出2.1 固定格式报表固定格式报表是BI最基本的一种应用，其目的是展示当前业务系统的运行状态。固定格式报表一旦建立，用户就不可以更改报表的结构，只能依据数据库的数据不断刷新报表，以便取得较新的数据。在pentaho产品线中，我们使用pentaho report designer来实现固定格式报表的需求。2.2 OLAP分析OLAP分析是指创建一种动态的报表展示结构，用户可以在一个IT预定义的数据集中自由选择自己感兴趣的特性和指标，运用钻取，行列转换等分析手段实现得到知识，或者验证假设的目的。在pentaho产品线中，我们使用Saiku来实现OLAP分析的需求。2.3 数据挖掘数据挖掘是BI的一种高级应用。数据挖掘是指从海量数据中通过数据挖掘技术得到有用的知识，并且以通俗易懂的方式表达知识，以便支持业务决策。在pentaho产品线中，我们使用weka来实现数据挖掘的需求。Pentaho产品介绍1. 产品介绍Pentaho是世界上最流行的开源商业智能软件，以工作流为核心的、强调面向解决方案而非工具组件的BI套件，整合了多个开源项目，目标是和商业BI相抗衡。它是一个基于java平台的商业智能套件，之所以说是套件是因为它包括一个web server平台和多个工具软件：报表，分析，图表，数据集成，数据挖掘等，可以说包括了商业智能的方方面面。2. Pentaho架构图Pentaho的架构图如下，简要解释如下：3rd party applications指交易系统，也就是数据仓库的原系统。Data & Application Integration主要指定义数据仓库的元数据，在数据仓库结构设计完毕后，通过ETL过程将原系统数据送入数据仓库。Business Intelligence Platform指pentaho提供的BI平台，在这个平台上可以进行平台安全设置，平台管理之类的工作，这个平台也是BI服务的基础。Reporting，Analysis，Dashboards，Process Management是基于BI平台上Pentaho可以实现的服务，比如报表，分析，仪表盘，服务自动控制等。Presentation Layer指展示层，在这一层，我们可以把其下层做好的报表等分析结果通过门户网站，Email等各种方式展示给用户。Pentaho产品线设计1. 产品线设计Pentaho作为一个开源的BI套件，商业版与社区版加起来共有几十种产品。考虑到恒信实际业务开展的情况，以及未来可能的需求，确定产品线如下。BI Function ProductETLKettleMetadata ManagementPentaho Metadata Editor (PME)OLAPSaiku + Schema WorkbenchReport toolsFixed report：Pentaho report designerAd-hoc report：Saiku Dashboard：CDE Data MiningWeka BI platformPentaho BI PlatformR languageRBig DataPentaho for Big Data产品线的设计并非一成不变，随着需求的增加，当某些需求无法利用现有的产品线实现时，可以继续添加组件，以便形成更为完善的BI体系。Pentaho BI Platform安装1. 安装步骤将下载下来的biserver-ce-X.X.X-stable.zip文件解压到D:下，将会产生administration-console和biserver-ce两个文件夹，前者是pentaho控制台，后者是pentaho BI服务器。默认时，Pentaho BI 平台会使用内置的JRE，它位于 D:biserver-cejre 位置。如果用户机器上安装了 JDK，并设置了 JAVA_HOME，则 Pentaho BI 平台会使用用户指定的 JDK。运行D:biserver-ce下的“start-pentaho.bat”批处理脚本能够启动 Pentaho BI 服务器，它运行在 Apache Tomcat容器中，并采纳了 HSQLDB 数据库（http:/hsqldb.org/）。2. 启动/停止BI server现在，打开浏览器，并访问http:/localhost:8080/pentaho，则将看到登录界面，当 joe/password 用户登录后，BI 服务器的主界面将呈现在眼前。如果需要停止 Pentaho BI 服务器，则于 D:biserver-ce 目录下运行“stop-pentaho.bat”批处理脚本即可。它将同时停止 Pentaho BI 服务器和 HSQLDB 数据库。3. 启用/停止Pentaho管理控制台于 D:administration-console 目录运行如下“start-pac.bat”批处理脚本能够启动 Pentaho管理控制台。默认时，它宿主在 Jetty Web 容器中。将浏览器定位到http:/localhost:8099/网址后，并输入默认的admin/password用户，即可登录到Pentaho管理控制台中。Pentaho 管理控制台是整个 BI 平台的重要后端软件，系统管理员通过它能够完成各类操作，比如维护用户及角色信息、注册新的业务库（数据库连接）、控制 BI 服务器中的各种敏感信息、使用调度服务等。如果要停止 Pentaho 管理控制台，则于 D:administration-console 目录下运行“stop-pac.bat”批处理脚本即可。4. HSQLDB迁移到MySQL DB4.1 迁移原因Pentaho BI 服务器的很多重要信息存储在数据库中，其默认使用 HSQLDB 数据库，即借助它存储自身的资料库，比如 Quartz 调度信息、业务资料库连接信息（数据源）等。HSQLDB 是不能够支撑真实的企业应用的，生产环境必须替换它，因此我们需要将HSQLDB迁移至MySQL。4.2 创建MySQL数据库分别执行下面加粗的sql脚本。先后顺序不限。运行方法是多种的，可以通过MySQL Workbench导入工具实现。我们设定导入的MySQL数据库地址为jdbc:mysql:/localhost:3307，用户名root，密码root。biserver-cedatamysql5create_quartz_mysql.sqlbiserver-cedatamysql5create_repository_mysql.sqlbiserver-cedatamysql5create_sample_datasource_mysql.sql其中1. create_repository_mysql.sql 创建hibernate 数据库，用于存储用户授权认证，solution repository以及数据源。2. create_sample_datasource.sql 为sample数据添加pentaho所有基本的实例数据源。3. create_quartz_mysql.sql 为Quartz计划任务器创建资源库。4.3配置Pentaho1 给pentaho添加JDBC文件下载MySQL的JDBC驱动：MySQLmysql-connector-java-x.x.x.jar将其拷贝至biserver-cetomcatlib和administration-consolejdbc下，以便BI service和administration console访问MySQL数据库。2 修改以下文件biserver-cepentaho-solutionssystemapplicationContext-spring-security-jdbc.xmlbiserver-cepentaho-solutionssystemapplicationContext-spring-security-hibernate.propertiesbiserver-cepentaho-solutionssystemhibernatehibernate-settings.xmlbiserver-cepentaho-solutionssystemhibernatemysql5.hibernate.cfg.xmlbiserver-cetomcatwebappspentahoMETA-INFcontext.xml以上文件主要是替换SQL驱动，SQL用户名与密码等信息。修改详情如下，红色部分代表文件名，黑体代表更改点。applicationContext-spring-security-jdbc.xml applicationContext-spring-security-hibernate.propertiesjdbc.driver=com.mysql.jdbc.Driverjdbc.url=jdbc:mysql:/localhost:3307/hibernatejdbc.username=rootjdbc.password=roothibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialecthibernate-settings.xmlsystem/hibernate/mysql5.hibernate.cfg.xmlmysql5.hibernate.cfg.xmlcom.mysql.jdbc.Driverjdbc:mysql:/localhost:3307/hibernate org.hibernate.dialect.MySQL5InnoDBDialect rootrootcontext.xml 现在可以启动pentaho服务了。可以看到BI环境准备就绪。Pentaho Data Integration-Kettle1. Kettle安装要运行此工具你必须安装 Sun 公司的 JAVA 运行环境 1.4 或者更高版本。Kettle 的下载可通过 pdi-ce-4.4.0-stable.zip 解压到想要放置的路径，并执行这一目录中Spoon.bat文件，Kettle的主界面将呈现在我们面前。左侧部分是对转换和作业进行数据库等相关配置的设置区域。Design是对应的组件明细。右边部分是ETL的主界面，我们需要把Design页面中相关组件在上面设计展示。Kettle 中有两种脚本文件，transformation和job，transformation完成针对数据的基础转换，job 则完成整个工作流的控制。Kettle的体系结构：2. Kettle使用2.1 数据库连接使用 kettle 进行数据抽取和转换之前必须连接数据库，你可以同时创建几种不同的数据库连接，如：Oracle、sql server、MySQL 等。下图是对本地mysql数据库建立连接。点击test按钮进行数据库连接测试2.2 新建一个转换Transformation(Ctrl+N)eg要求：将数据库中交易表的数据按时间增量抽取并过滤输出到目标数据库中的另一张表中。第一步要先创建一个新 transformation,再创建数据库连接；第二步从输入中找到【表输入】，拖到主窗口释放鼠标。接下来双击表输入写查询语句；【获取目标表中对应字段的最新时间，没有就给个初始时间。】第三步从输入中找到【表输入】，拖到主窗口释放鼠标。按住 shift 键，用鼠标点中第二步的表输入与第三步的表输入进行连接；在sql中从点击获取sql查询语句中选择需要进行增量操作的表，然后确认需要显示列名，出现没有where条件的sql语句，然后自己在sql中增加where 条件用？代表从上一个步骤中传过来的变量，在下面替换sql语句里的变量，打勾，确保到时问号符号能用被替换，从步骤插入数据的下拉菜单中选择上一步操作，在执行每一行上打勾。第四步从输入中找到【字段选择】，拖到主窗口释放鼠标。按住 shift 键，用鼠标点中表输入与字段选择进行连接；第五步双击【字段选择】，可在根据需求选择保留字段；第六步是找到【过滤记录】 连接方法同上，双击过滤记录，在里面进行字段选择，并在中键入值。确定保存。最后找到【表输出】并对其连接，设置好输出表名等信息后。点击保存，然后点击运行可进行转换。整个结构图如下：2.3 新建一个作业Jobs(Ctrl+ALT+N)eg要求：将多个转换按照处理顺序保存到执行计划中。并可以对其进行定时执行。第一步要先创建一个新 transformation,再创建数据库连接；第二步从通用组件中找到【开始】，拖到主窗口释放鼠标。接下来双击可以进行定时设置.第三步从通用组件中找到【转换】，拖到主窗口释放鼠标，按住 shift 键，用鼠标点中开始与转换进行连接；最后从脚本中找到【SQL】并对其连接，设置执行的SQL后。点击保存，然后点击运行可按照start定义的方式进行执行。整个结构图如下：由于组件种类繁多，关于Design中其他组件的使用方法可以参考附件中的官方文档 Pentaho_Data_Integration_4_Cookbook.pdfPentaho Report Designer1. Pentaho Report Designer安装开发者可通过 prd-ce-3.9.1-GA.zip 解压到想要放置的路径，并执行这一目录中的 report-designer.bat 批处理文件，PRD 的主界面将呈现在我们面前。2. PRD使用例子2.1 新建一个报表(Ctrl+N)对报表界面做简要介绍：左侧的竖条展示了我们在设计报表时可能用到的工具。中间的部分是报表的主界面，我们需要把报表结果在主界面上排版展示右边的标签Structure可以看到报表的结构，Data标签里有所有要展示的数据。包括报表query的结果，以及各种函数。2.2 创建query在Data标签下右击data sets，选择JDBC连接，以下界面将会跳出。左侧的框格显示了所有已存在的数据库连接，我们可以点击框格上方的按钮来增删改数据库连接。右上侧的框格展示了我们对应于某个数据库连接有哪些已存在的query。同样可以通过右上角的按钮来增删query。右下侧的框格是query的主题部分，可以点击铅笔图标进入图形化SQL编辑器，也可以直接在显示的query语句中编辑SQL语句。2.3 设计query字段的展示。在右侧data标签中找到query的查询结果字段，按住左键将其拖入到报表设计主界面。切换右上角的标签页到Structure标签，单击报表设计主界面上的对象，在右下角的Style和Attributes标签上可以看到这个对象的风格和属性。Style标签定义了这个对象的展示风格，比如字体颜色，背景色等内容。Attributes标签定义了对象的属性，比如对象的类型，名字等内容。可以看到报表设计主界面被分为了不同的区域，他们表示了报表的结构。Page Header与Page Footer中的对象会在报表的每页都显示。Report Header中的对象只在报表开头时展示一次，Detail中的对象会完全罗列query中的结果，Report Footer中的对象会只在report的结尾显示一次。在右上角的Structure标签中也可以明显看出报表的结构，甚至可以看到在主界面上不显示的结构，比如Details Header。同时，在各个报表功能区域下，我们还可以看到主页面上的对象，比如下图中，我们可以通过Structure标签就可以知道在主界面的Details区域内我们放置了两个字段，sourceName和sourceID。Structure标签中的报表结构是最为完整的报表结构，我们在主界面上看不到某些区域是因为软件默认在主界面上隐藏这些区域。我们可以在Structure标签中点击相应对象解除隐藏。比如在Structure标签中单击Details Header，在Attributes标签中把hide-on-canvas改为False。这样就可以在主界面上显示Details Header区域。2.4 对数据做图PRD中提供作图功能，我们可以对数据表进行作图以便更好地显示。从左侧工具条中找到“chart”对象，将其拖到主界面上想要放置的位置。在主界面上双击图片区域，可以修改图的种类以及选项，我们以饼图为例，解释如下图。饼图效果如下:2.5 报表参数设计PRD支持报表参数设计，我们可以利用参数来实现报表的filter。具体做法如下，右击Data标签中的parameter点击新增或者修改Data Sources新加一个query，取名Query company nameSQL代码为接下来我们做如下配置：为parameter取名为f，指定query为Query company name。这样我们就产生了一个名为f的参数。但是这个参数现在并不能起作用。我们需要在报表查询的主query中把参数加入才行。在主query中需要把参数f写为$f格式才能起作用。报表将会显示当company name=参数f的值时，Select语句所选择的字段。以下的例子中，我们创建了两个参数，f代表company name和f1代表日期。当我们运行报表时，对参数“公司”和“日期”选择具体的值，就可以看到报表查询主query根据指定公司和指定日期查询得到的结果。2.6 公式PRD提供了强大的公式功能，不仅内建了很多实用的公式，比如“当前页数/总页数”，“总行数”，还支持自定义公式。公式的入口在Data标签下的Functions上，右击增加公式，我们可以选取PRD内建好的公式，也可以自定义公式。公式按照功能已经分类，除了内建公式之外，我们点击Open Formula可以自定义公式。2.7 子报表，SubreportPRD在软件设计时限制一个报表只能选定一个query在主界面上放置字段，也就是说一个报表同一时刻只支持从一个query查询到数据并且显示出来。那么当我们需要在一个报表上同时显示多个数据集，多个query查询的结果时该怎么办？办法就是添加一个subreport，在主报表从左侧的工具栏上拖入sub-report到想要的位置，双击进入subreport，会发现subreport和主报表设计器几乎一模一样，可以定义query，可以设置公式，唯一的不同是subreport不能新建参数，只能从主报表传递参数过来。Saiku1. Pentaho Saiku安装Saiku主要提供两种安装方式。独立运行和集成在Pentaho BI平台上，我们这里采用集成在Pentaho BI平台这种方式运行。点击http:/analytical- 下载到集成版的saiku并将其内saiku文件夹解压到biserver-cepentaho-solutionssystem下，并重启BI平台的服务。登录BI平台并点击saiku按钮就可以看到Saiku的真面目了。2. Saiku使用例子使用saiku分析之前要准备好cube。通常用Schema Workbench建立。关于Schema Workbench的使用，下节有专门的介绍。2.1 新建一个分析报表点File选择new Saiku Analytics进入到分析设计画面，点击左边cube面板上的下拉按钮，选择一个cube。选择steelwheelSales后，下面的维度和度量值就会显示出来拖拽Demensions和Measures中的内容根据需要放到右面的设计面板上。默认的情况，结果面板会立即对设计的结果予以预览展示。点击saiku工具条的保存按钮，将设计好的OLAP分析结果发布到PentahoBI平台上。Ps.暂时不支持中文保存2.2 查看一个分析报表点击BI平台的按钮将左边画面展开，安装刚保存的路径逐一点击后显示下面画面Files中的testSaiku就是刚刚保存好的。双击即可应用。Schema Workbench1. Schema Workbench安装1，需要jdk1.5+，下载psw-ce-3.4.1.1并解压缩到自定义的路径如D:/。2，修改workbench.bat文件,找到“rem you should also put mondrian.properties on the calsspath for it to be picked up”这一行并在下方添加 set CP=%CP%;./mondrian.properties；找到“rem add all needed JDBC drivers to the classpath”这一行并在下方添加set CP=%CP%;lib/jdbc.jar（如果有多个驱动JAR文件，就添加多行）3，重新启动workbench.bat4，点击tools-connection配置数据库，在弹出的对话框处填写JDBC连接信息。注意：要把相应的JDBC驱动包拷贝到schema-workbenchlib下面。5，file-new-schema出来的界面如下图2. 制作CUBE数据立方体1.创建或编辑元素，workbench会自动根据你的改动获取和校验相关表和字段。2.一旦出错，会在软件下方区域提示错误信息关于书写细节参考附件的官方文档官方CUBE编写指南3确定无错误并保存4. 点击file-new-MDX Query 输入语句，点击Execute即可校验结果5.发布到pentahoBI平台发布成功后到saiku设计画面就可以看到新建的CUBE附件A. Pentaho_Data_Integration_4_Cookbook.pdfB. Kettle 3.2 使用说明书.pdfC. 官方CUBE编写指南.pdf