Business Intelligence Tools for Small Companies A Guide to Free and Low-Cost Solutions — Albert Nogués Juan Valladares Business Intelligence Tools for Small Companies A Guide to Free and Low-Cost Solutions Albert Nogués Juan Valladares Business Intelligence Tools for Small Companies: A Guide to Free and Low-Cost Solutions Albert Nogués Juan Valladares Barcelona, Spain Barcelona, Spain ISBN-13 (pbk): 978-1-4842-2567-7 ISBN-13 (electronic): 978-1-4842-2568-4 DOI 10.1007/978-1-4842-2568-4 Library of Congress Control Number: 2017943521 Copyright © 2017 by Albert Nogués and Juan Valladares This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. While the advice and information in this book are believed to be true and accurate at the date of publication, neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made. The publisher makes no warranty, express or implied, with respect to the material contained herein. Managing Director: Welmoed Spahr Editorial Director: Todd Green Acquisitions Editor: Susan McDermott Development Editor: Laura Berendson Technical Reviewer: Marcos Muñoz Coordinating Editor: Rita Fernando Copy Editor: Karen Jameson Compositor: SPi Global Indexer: SPi Global Cover image designed by FreePik Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail [email protected], or visit www.springeronline.com. Apress Media, LLC is a California LLC and the sole member (owner) is Springer Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation. For information on translations, please e-mail [email protected], or visit http://www.apress.com/rights- permissions. Apress titles may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Print and eBook Bulk Sales web page at http://www.apress.com/bulk-sales. Any source code or other supplementary material referenced by the author in this book is available to readers on GitHub via the book’s product page, located at www.apress.com/9781484225677. For more detailed information, please visit http://www.apress.com/source-code. Printed on acid-free paper I want to thank all my family and friends for the support they always shown me, including when writing this book. I want to especially dedicate it to my two grandmas, which recently passed away, as they have always been an inspiration to me. —Albert To my wife for all those times she has been waiting for me to go to sleep. To my kid for all those times he has been waiting for me to help him with his homework. To my parents that always have supported me in all my initiatives. To my whole family, friends, and colleagues just to be there. —Juan Contents at a Glance About the Authors ��������������������������������������������������������������������������������������������������xvii About the Technical Reviewer ��������������������������������������������������������������������������������xix Acknowledgments ��������������������������������������������������������������������������������������������������xxi Introduction ����������������������������������������������������������������������������������������������������������xxiii ■ Chapter 1: Business Intelligence for Everybody ����������������������������������������������������1 ■ Chapter 2: Agile Methodologies for BI Projects ���������������������������������������������������29 ■ Chapter 3: SQL Basics �����������������������������������������������������������������������������������������57 ■ Chapter 4: Project Initialization – Database and Source ERP Installation �����������93 ■ Chapter 5: Data Modeling for BI Solutions ��������������������������������������������������������117 ■ Chapter 6: ETL Basics ����������������������������������������������������������������������������������������147 ■ Chapter 7: Performance Improvements �������������������������������������������������������������185 ■ Chapter 8: The BI Reporting Interface ���������������������������������������������������������������207 ■ Chapter 9: MOLAP Tools for Budgeting ��������������������������������������������������������������235 ■ Chapter 10: BI Process Scheduling: How to Orchestrate and Update Running Processes ��������������������������������������������������������������������������������255 ■ Chapter 11: Moving to a Production Environment ���������������������������������������������273 ■ Chapter 12: Moving BI Processes to the Cloud �������������������������������������������������295 ■ Chapter 13: Conclusions and Next Steps �����������������������������������������������������������315 Index ���������������������������������������������������������������������������������������������������������������������319 v Contents About the Authors ��������������������������������������������������������������������������������������������������xvii About the Technical Reviewer ��������������������������������������������������������������������������������xix Acknowledgments ��������������������������������������������������������������������������������������������������xxi Introduction ����������������������������������������������������������������������������������������������������������xxiii ■ Chapter 1: Business Intelligence for Everybody ����������������������������������������������������1 What Is Business Intelligence? ����������������������������������������������������������������������������������������2 BI Evolution ���������������������������������������������������������������������������������������������������������������������������������������������3 BI Characteristics �������������������������������������������������������������������������������������������������������������5 Hidden Relevant Information ������������������������������������������������������������������������������������������������������������������5 Accuracy �������������������������������������������������������������������������������������������������������������������������������������������������6 Significant KPIs ���������������������������������������������������������������������������������������������������������������������������������������6 On Time ���������������������������������������������������������������������������������������������������������������������������������������������������6 Company Analytics Life Cycle and Continuous Improvement �����������������������������������������������������������������7 Benefits of BI ��������������������������������������������������������������������������������������������������������������������8 Direct Benefits ����������������������������������������������������������������������������������������������������������������������������������������8 Indirect Benefits �������������������������������������������������������������������������������������������������������������������������������������9 Objectives ���������������������������������������������������������������������������������������������������������������������������������������������10 Who Can Get Benefit from BI? ��������������������������������������������������������������������������������������������������������������11 BI Platform Components ������������������������������������������������������������������������������������������������11 Source ERP �������������������������������������������������������������������������������������������������������������������������������������������12 Database �����������������������������������������������������������������������������������������������������������������������������������������������12 ETL ��������������������������������������������������������������������������������������������������������������������������������������������������������13 Front-End Tool ��������������������������������������������������������������������������������������������������������������������������������������13 Budgeting Tool ��������������������������������������������������������������������������������������������������������������������������������������13 vii ■ Contents BI Platform Location �������������������������������������������������������������������������������������������������������13 BI Concepts ��������������������������������������������������������������������������������������������������������������������14 Datawarehouse �������������������������������������������������������������������������������������������������������������������������������������14 DataMart �����������������������������������������������������������������������������������������������������������������������������������������������15 Logical Model ���������������������������������������������������������������������������������������������������������������������������������������16 Physical Model��������������������������������������������������������������������������������������������������������������������������������������19 BI Main Objects �������������������������������������������������������������������������������������������������������������������������������������20 BI Approaches ����������������������������������������������������������������������������������������������������������������21 Query and Reporting �����������������������������������������������������������������������������������������������������������������������������21 Information Sharing ������������������������������������������������������������������������������������������������������������������������������22 Dashboarding ���������������������������������������������������������������������������������������������������������������������������������������22 Data Import �������������������������������������������������������������������������������������������������������������������������������������������22 Data Discovery ��������������������������������������������������������������������������������������������������������������������������������������22 MOLAP ��������������������������������������������������������������������������������������������������������������������������������������������������23 Data Mining ������������������������������������������������������������������������������������������������������������������������������������������23 Incoming Approaches ���������������������������������������������������������������������������������������������������������������������������24 BI Capabilities ����������������������������������������������������������������������������������������������������������������24 Drilling ��������������������������������������������������������������������������������������������������������������������������������������������������24 Pivoting �������������������������������������������������������������������������������������������������������������������������������������������������25 Visualizations ����������������������������������������������������������������������������������������������������������������������������������������25 Sorting ��������������������������������������������������������������������������������������������������������������������������������������������������26 Grouping �����������������������������������������������������������������������������������������������������������������������������������������������26 Filtering ������������������������������������������������������������������������������������������������������������������������������������������������26 Conditional Expressions �����������������������������������������������������������������������������������������������������������������������27 Subtotals ����������������������������������������������������������������������������������������������������������������������������������������������27 Administration ��������������������������������������������������������������������������������������������������������������������������������������28 User Interface ���������������������������������������������������������������������������������������������������������������������������������������28 Conclusion ����������������������������������������������������������������������������������������������������������������������28 viii ■ Contents ■ Chapter 2: Agile Methodologies for BI Projects ���������������������������������������������������29 Introduction to Agile Methodologies �������������������������������������������������������������������������������30 Agile Approaches ������������������������������������������������������������������������������������������������������������31 Our Recommended Mix between Scrum and Kanban ��������������������������������������������������������������������������31 Developing Projects with Scrum ����������������������������������������������������������������������������������������������������������32 Maintenance with Kanban ��������������������������������������������������������������������������������������������������������������������43 Mix of Both Methodologies, Scrumban �������������������������������������������������������������������������������������������������45 Peculiarities of Scrum for BI�������������������������������������������������������������������������������������������46 Sprint 0 - Longer Initial Analysis �����������������������������������������������������������������������������������������������������������46 BI Project Segmentation �����������������������������������������������������������������������������������������������������������������������48 Front-End vs� Back-End Activities ��������������������������������������������������������������������������������������������������������49 Role Isolation - Specific Knowledge �����������������������������������������������������������������������������������������������������50 Developer Story Types in BI ������������������������������������������������������������������������������������������������������������������51 Agile Management Tools ������������������������������������������������������������������������������������������������52 Trello �����������������������������������������������������������������������������������������������������������������������������������������������������53 JIRA Software ���������������������������������������������������������������������������������������������������������������������������������������54 Conclusion ����������������������������������������������������������������������������������������������������������������������56 ■ Chapter 3: SQL Basics �����������������������������������������������������������������������������������������57 What Is SQL? ������������������������������������������������������������������������������������������������������������������57 The Relational Model ������������������������������������������������������������������������������������������������������58 Databases and Database Vendors ����������������������������������������������������������������������������������60 ACID Compliance ����������������������������������������������������������������������������������������������������������������������������������61 Types of SQL Statements ����������������������������������������������������������������������������������������������������������������������62 SQL Datatypes ��������������������������������������������������������������������������������������������������������������������������������������63 Retrieving Data from a Table ����������������������������������������������������������������������������������������������������������������63 Sorting ��������������������������������������������������������������������������������������������������������������������������������������������������67 Filtering ������������������������������������������������������������������������������������������������������������������������������������������������69 Grouping Data ���������������������������������������������������������������������������������������������������������������������������������������71 Using Subqueries ����������������������������������������������������������������������������������������������������������������������������������74 Joining Tables ���������������������������������������������������������������������������������������������������������������������������������������77 ix ■ Contents Set Operations ��������������������������������������������������������������������������������������������������������������������������������������85 Working with Dates ������������������������������������������������������������������������������������������������������������������������������89 Conditional Expressions �����������������������������������������������������������������������������������������������������������������������91 Conclusion ����������������������������������������������������������������������������������������������������������������������92 ■ Chapter 4: Project Initialization – Database and Source ERP Installation �����������93 The Need for Data ����������������������������������������������������������������������������������������������������������93 Setting the System Up with Odoo ERP ���������������������������������������������������������������������������93 The Bitnami Odoo Package �������������������������������������������������������������������������������������������������������������������94 Downloading and Installing Odoo ���������������������������������������������������������������������������������������������������������95 Bitnami and Odoo Configuration Files ��������������������������������������������������������������������������������������������������96 Installing psql and Checking the Database Connection ������������������������������������������������������������������������97 Accessing the Application���������������������������������������������������������������������������������������������������������������������98 Configuring and Installing Modules ������������������������������������������������������������������������������������������������������99 Selecting our Datawarehouse Database���������������������������������������������������������������������������������������������101 Sourcing and Installing MariaDB ��������������������������������������������������������������������������������������������������������102 Creating Our First Database ���������������������������������������������������������������������������������������������������������������107 Analyzing the Data Source �������������������������������������������������������������������������������������������111 Inspecting our Model ��������������������������������������������������������������������������������������������������������������������������111 Setting up a PostgreSQL Connection to the Server ����������������������������������������������������������������������������113 Conclusion ��������������������������������������������������������������������������������������������������������������������116 ■ Chapter 5: Data Modeling for BI Solutions ��������������������������������������������������������117 Naming Convention and Nomenclature ������������������������������������������������������������������������117 Modeling Steps ������������������������������������������������������������������������������������������������������������119 Business Model ����������������������������������������������������������������������������������������������������������������������������������119 Logical Model �������������������������������������������������������������������������������������������������������������������������������������120 Dimensional Model �����������������������������������������������������������������������������������������������������������������������������121 Physical Model������������������������������������������������������������������������������������������������������������������������������������122 Defining our Model �������������������������������������������������������������������������������������������������������124 Sales Dimension ���������������������������������������������������������������������������������������������������������������������������������125 Status Table ����������������������������������������������������������������������������������������������������������������������������������������127 x ■ Contents Currency Dimension ���������������������������������������������������������������������������������������������������������������������������127 Customer Dimension ��������������������������������������������������������������������������������������������������������������������������127 Employee Dimension ��������������������������������������������������������������������������������������������������������������������������127 Product Dimension �����������������������������������������������������������������������������������������������������������������������������128 Time Dimension ����������������������������������������������������������������������������������������������������������������������������������128 Exploring Data Modeling Possibilities ��������������������������������������������������������������������������128 View Layer ������������������������������������������������������������������������������������������������������������������������������������������129 Data Split ��������������������������������������������������������������������������������������������������������������������������������������������130 Fact Normalization and Denormalization��������������������������������������������������������������������������������������������131 Time Transformations �������������������������������������������������������������������������������������������������������������������������132 Fact Phases ����������������������������������������������������������������������������������������������������������������������������������������133 Real vs� Potential ��������������������������������������������������������������������������������������������������������������������������������133 Historical Hierarchies �������������������������������������������������������������������������������������������������������������������������134 Multiple Views Using a Table, Entity Isolation �������������������������������������������������������������������������������������134 Modifying Existing Structures, Adding More Sales Document Status ������������������������������������������������135 Currency Transformation ��������������������������������������������������������������������������������������������������������������������135 Geographical Information �������������������������������������������������������������������������������������������������������������������137 Data Modeling Tools �����������������������������������������������������������������������������������������������������137 Erwin DataModeler �����������������������������������������������������������������������������������������������������������������������������137 MySQL Workbench ������������������������������������������������������������������������������������������������������������������������������142 Preparing the ETL ���������������������������������������������������������������������������������������������������������144 Source Systems ����������������������������������������������������������������������������������������������������������������������������������144 Source Tables �������������������������������������������������������������������������������������������������������������������������������������145 Source Fields ��������������������������������������������������������������������������������������������������������������������������������������145 Conclusion ��������������������������������������������������������������������������������������������������������������������146 ■ Chapter 6: ETL Basics ����������������������������������������������������������������������������������������147 Why Do We Need an ETL Process? �������������������������������������������������������������������������������147 Details of the Solution ��������������������������������������������������������������������������������������������������147 Open Source ETL Suites �����������������������������������������������������������������������������������������������148 Downloading and Installing Pentaho Data Integration �������������������������������������������������148 xi