Table Of Contentwww.it-ebooks.info
Pentaho Analytics for
MongoDB Cookbook
Over 50 recipes to learn how to use Pentaho Analytics
and MongoDB to create powerful analysis and reporting
solutions
Joel Latino
Harris Ward
BIRMINGHAM - MUMBAI
www.it-ebooks.info
Pentaho Analytics for MongoDB Cookbook
Copyright © 2015 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system,
or transmitted in any form or by any means, without the prior written permission of the
publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the
information presented. However, the information contained in this book is sold without
warranty, either express or implied. Neither the authors, nor Packt Publishing, and its
dealers and distributors will be held liable for any damages caused or alleged to be
caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the
companies and products mentioned in this book by the appropriate use of capitals.
However, Packt Publishing cannot guarantee the accuracy of this information.
First published: December 2015
Production reference: 1181215
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham B3 2PB, UK.
ISBN 978-1-78355-327-3
www.packtpub.com
www.it-ebooks.info
Credits
Authors Copy Editor
Joel Latino Vikrant Phadke
Harris Ward
Project Coordinator
Reviewers Bijal Patel
Rio Bastian
Proofreader
Mark Kromer
Safis Editing
Commissioning Editor
Indexer
Usha Iyer
Rekha Nair
Acquisition Editor
Production Coordinator
Nikhil Karkal
Manu Joseph
Content Development Editor
Cover Work
Anish Dhurat
Manu Joseph
Technical Editor
Menza Mathew
www.it-ebooks.info
About the Authors
Joel Latino was born in Ponte de Lima, Portugal, in 1989. He has been working in the IT
industry since 2010, mostly as a software developer and BI developer.
He started his career at a Portuguese company and specialized in strategic planning,
consulting, implementation, and maintenance of enterprise software that is fully adapted
to its customers' needs.
He earned his graduate degree in informatics engineering from the School of Technology
and Management of Viana do Castelo Polytechnic Institute.
In 2014, he moved to Edinburgh, Scotland, to work for Ivy Information Systems, a highly
specialized open source BI company in the United Kingdom.
Joel mainly focuses on open source web technology, databases, and business intelligence,
and is fascinated by mobile technologies. He is responsible for developing some plugins for
Pentaho, such as Android and Apple push notification steps, and lot of other plugins under
Ivy Information Systems.
I would like to thank my family for supporting me throughout my career
and endeavors.
Harris Ward has been working in the IT sector since 2004, initially developing websites
using LAMP and moving on to business intelligence in 2006. His first role was based in
Germany on a product called InfoZoom, where he was introduced to the world of business
intelligence. He later discovered open source business intelligence tools and dedicated the
last 9 years to not only working on developing solutions, but also working to expand the
Pentaho community with the help of other committed members.
Harris has worked as a Pentaho consultant over the past 7 years under Ambient BI. Later,
he decided to form Ivy Information Systems Scotland, a company focused on delivering more
advanced Pentaho solutions as well as developing a wide range of Pentaho plugins that you
can find in the marketplace today.
www.it-ebooks.info
About the Reviewers
Rio Bastian is a happy software engineer. He has worked on various IT projects. He is
interested in business intelligence, data integration, web services (using WSO2 API or ESB),
and tuning SQL and Java code. He has also been a Pentaho business intelligence trainer
for several companies in Indonesia and Malaysia. Currently, Rio is working on developing
one of Garuda Indonesia airline's e-commerce channel web service systems in PT. Aero
Systems Indonesia.
In his spare time, he tries to share his experience in software development through his
personal blog at altanovela.wordpress.com. You can reach him on Skype at rio.
bastian or e-mail him at [email protected].
Mark Kromer has been working in the database, analytics, and business intelligence industry
for 20 years, with a focus on big data and NoSQL since 2011. As a product manager, he has
been responsible for the Pentaho MongoDB Analytics product road map for Pentaho, the graph
database strategy for DataStax, and the business intelligence road map for Microsoft's vertical
solutions. Mark is currently a big data cloud architect and is a frequent contributor to the TDWI
BI magazine, MSDN Magazine, and SQL Server Magazine. You can keep up with his speaking
and writing schedule at http://www.kromerbigdata.com.
www.it-ebooks.info
www.PacktPub.com
Support files, eBooks, discount offers, and more
For support files and downloads related to your book, please visit www.PacktPub.com.
Did you know that Packt offers eBook versions of every book published, with PDF and ePub
files available? You can upgrade to the eBook version at www.PacktPub.com and as a print
book customer, you are entitled to a discount on the eBook copy. Get in touch with us at
[email protected] for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up
for a range of free newsletters and receive exclusive discounts and offers on Packt books
and eBooks.
TM
https://www2.packtpub.com/books/subscription/packtlib
Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book
library. Here, you can search, access, and read Packt's entire library of books.
Why Subscribe?
f Fully searchable across every book published by Packt
f Copy and paste, print, and bookmark content
f On demand and accessible via a web browser
Free Access for Packt account holders
If you have an account with Packt at www.PacktPub.com, you can use this to access PacktLib
today and view 9 entirely free books. Simply use your login credentials for immediate access.
www.it-ebooks.info
Table of Contents
Preface v
Chapter 1: PDI and MongoDB 1
Introduction 1
Learning basic operations with Pentaho Data Integration 2
Migrating data from the RDBMS to MongoDB 4
Loading data from MongoDB to MySQL 11
Migrating data from files to MongoDB 14
Exporting MongoDB data using the aggregation framework 18
MongoDB Map/Reduce using the User Defined Java Class step
and MongoDB Java Driver 20
Working with jobs and filtering MongoDB data using parameters
and variables 25
Chapter 2: The Thin Kettle JDBC Driver 29
Introduction 29
Using a transformation as a data service 30
Running the Carte server in a single instance 32
Running the Pentaho Data Integration server in a single instance 35
Define a connection using a SQL Client (SQuirreL SQL) 39
Chapter 3: Pentaho Instaview 45
Introduction 45
Creating an analysis view 45
Modifying Instaview transformations 48
Modifying the Instaview model 50
Exploring, saving, deleting, and opening analysis reports 55
i
www.it-ebooks.info
Table of Contents
Chapter 4: A MongoDB OLAP Schema 59
Introduction 59
Creating a date dimension 60
Creating an Orders cube 67
Creating the customer and product dimensions 72
Saving and publishing a Mondrian schema 78
Creating a Mondrian 4 physical schema 83
Creating a Mondrian 4 cube 86
Publishing a Mondrian 4 schema 88
Chapter 5: Pentaho Reporting 91
Introduction 91
Copying the MongoDB JDBC library 92
Connecting to MongoDB using Reporting Wizard 92
Connecting to MongoDB via PDI 98
Adding a chart to a report 101
Adding parameters to a report 104
Adding a formula to a report 111
Grouping data in reports 114
Creating subreports 118
Creating a report with MongoDB via Java 122
Publishing a report to the Pentaho server 125
Running a report in the Pentaho server 128
Chapter 6: The Pentaho BI Server 131
Introduction 131
Importing Foodmart MongoDB sample data 131
Creating a new analysis view using Pentaho Analyzer 134
Creating a dashboard using Pentaho Dashboard Designer 140
Chapter 7: Pentaho Dashboards 145
Introduction 145
Copying the MongoDB JDBC library 146
Importing a sample repository 147
Using a transformation data source 147
Using a BeanShell data source 152
Using Pentaho Analyzer for MongoDB data source 155
Using a Thin Kettle data source 161
Defining dashboard layouts 164
Creating a Dashboard Table component 171
Creating a Dashboard line chart component 174
ii
www.it-ebooks.info
Table of Contents
Chapter 8: Pentaho Community Contributions 179
Introduction 179
The PDI MongoDB Delete Step 180
The PDI MongoDB GridFS Output Step 183
The PDI MongoDB Map/Reduce Output step 186
The PDI MongoDB Lookup step 189
Index 193
iii
www.it-ebooks.info
