Skip to main content
main-content

Über dieses Buch

Learn how to transition from Excel-based business intelligence (BI) analysis to enterprise stacks of open-source BI tools. Select and implement the best free and freemium open-source BI tools for your company’s needs and design, implement, and integrate BI automation across the full stack using agile methodologies.

Business Intelligence Tools for Small Companies provides hands-on demonstrations of open-source tools suitable for the BI requirements of small businesses. The authors draw on their deep experience as BI consultants, developers, and administrators to guide you through the extract-transform-load/data warehousing (ETL/DWH) sequence of extracting data from an enterprise resource planning (ERP) database freely available on the Internet, transforming the data, manipulating them, and loading them into a relational database.

The authors demonstrate how to extract, report, and dashboard key performance indicators (KPIs) in a visually appealing format from the relational database management system (RDBMS). They model the selection and implementation of free and freemium tools such as Pentaho Data Integrator and Talend for ELT, Oracle XE and MySQL/MariaDB for RDBMS, and Qliksense, Power BI, and MicroStrategy Desktop for reporting. This richly illustrated guide models the deployment of a small company BI stack on an inexpensive cloud platform such as AWS.

What You'll Learn

You will learn how to manage, integrate, and automate the processes of BI by selecting and implementing tools to:

Implement and manage the business intelligence/data warehousing (BI/DWH) infrastructure

Extract data from any enterprise resource planning (ERP) tool

Process and integrate BI data using open-source extract-transform-load (ETL) tools

Query, report, and analyze BI data using open-source visualization and dashboard tools

Use a MOLAP tool to define next year's budget, integrating real data with target scenarios

Deploy BI solutions and big data experiments inexpensively on cloud platforms

Who This Book Is For

Engineers, DBAs, analysts, consultants, and managers at small companies with limited resources but whose BI requirements have outgrown the limitations of Excel spreadsheets; personnel in mid-sized companies with established BI systems who are exploring technological updates and more cost-efficient solutions

Inhaltsverzeichnis

Frontmatter

Chapter 1. Business Intelligence for Everybody

Abstract
When some years ago we were offered to join to our first Business Intelligence project, we thought that something in the term was redundant because at the end of the day, doing Business requires Intelligence. This is the truth, especially if you pretend to do your business correctly, because profitable business cannot be performed without intelligence.
Albert Nogués, Juan Valladares

Chapter 2. Agile Methodologies for BI Projects

Abstract
Let's suppose that you are the leader of a BI project. Let's also suppose that you are following the typical approach to implementing a datawarehouse project with its related reporting tool, the ETL process reading from the source transactional database, and inserting data into your DWH. Following the typical approach you should gather specifications from key users, think of a very robust data model that serves to accomplish those specifications, install all components, extract all the different data that you need from different data sources, validate the integrity of all this data for all fields, define the required reporting, and then you will be able to show to the key user the result. The whole process can have taken months or maybe even years. When you are checking with the key user what has been the result, your user can have changed his mind regarding what he needs or maybe your key user has changed his mind and he has completely different ideas about what to use in his reports.
Albert Nogués, Juan Valladares

Chapter 3. SQL Basics

Abstract
Prior to getting started with relational databases and probably, even more complicated things, you need to be familiar with the standard language used to interact with them. While you can get away without knowing SQL and working with databases (think in Microsoft Access), sooner or later you will need to learn it.
Albert Nogués, Juan Valladares

Chapter 4. Project Initialization – Database and Source ERP Installation

Abstract
In the first chapters of this book we have seen many much theoretical aspects of the BI. We started with a general introduction, a chapter dedicated to project management, and also another chapter dedicated to SQL introduction. But at the end of the day, until now we haven't done anything practical so far, so it's time to start getting our hands on the development of the solution. Before starting, however, we need to install our source ERP, where the data will come from. After installing and configuring it, we will mess with it around for a while, and then we will select our database to store the datawarehouse.
Albert Nogués, Juan Valladares

Chapter 5. Data Modeling for BI Solutions

Abstract
You have received the request from your boss to implement a datawarehouse inside your database server that you have recently installed. You are the chosen person to lead and maybe develop, but this always will depend on the size and resources of your company, a solution that must allow your company the analysis of its data. So, after doing a previous analysis based on your user requirements, having the database available to go ahead and information enough to feed your system you should start with logical and physical development of the database solution that will be accessed from the BI tool.
Albert Nogués, Juan Valladares

Chapter 6. ETL Basics

Abstract
In the previous chapter, we developed our datawarehouse model. This was the first chapter that we get our hands on our solution. It is very important to have a correct understanding of that chapter and be familiar with the model in order to consolidate this knowledge that we will acquire for the next set of chapters, as we will have to build upon it. In Business Intelligence, usually we talk about three areas: Database and Datawarehouse design, ETL, and Reporting. We covered the first one, and are now we are just ahead of starting the second one. In this ETL chapter we will see how to Extract, Transform, and Load process the data into our datawarehouse. Let's start!
Albert Nogués, Juan Valladares

Chapter 7. Performance Improvements

Abstract
In a mid-sized company, it is highly possible that the volume of data we own is not that big. However, if we are a retail company, it is likely that our transactional can have a good amount of transactions, especially if we have several years of data. Whether that is the case of your business or not, it is important to have some exposure to improve performance when working with databases. This is the reason this chapter was added to the book: to provide you with some insights about how to speed up your processes. Whereas this is not a very extensive compilation, it should be enough to start.
Albert Nogués, Juan Valladares

Chapter 8. The BI Reporting Interface

Abstract
We are advancing across the book and if you have been following the installation steps and instructions of previous chapters, you are arriving to the funny part of the BI platform implementation. By the end of this chapter we will have been able to analyze information across our BI platform in a graphical and intuitive way. The BI reporting interface is considered sometimes as the BI solution, but without all the previous work we have already done we could hardly have something to analyze inside our BI tool.
Albert Nogués, Juan Valladares

Chapter 9. MOLAP Tools for Budgeting

Abstract
We are working in our operational system saving the daily activity of the company; then we extract it using an ETL tool, loading into our database, information that will be analyzed using a BI tool. In this moment we extract some conclusions from the data analyzed and we think of some actions to do in order to improve our company performance, but we want to know what each action implies in terms of net revenue improvement before applying it.
Albert Nogués, Juan Valladares

Chapter 10. BI Process Scheduling: How to Orchestrate and Update Running Processes

Abstract
So far, we have built our solution, from the database until the reporting layer and the budgeting system. Maybe you have built the best dashboard ever seen with all the KPIs that your users need to analyze but your users will want fresh data periodically. This means that we must put a system in place that runs every day; or with the frequency we need; and in case of any error or issue during the load process, we get at least an alert, so whoever is in charge can review and analyze what has happened. In this chapter, we will see first, how to finish our ETL project by designing a final job that will launch the transformation, gather information, and control the flow from its execution; and take care if something goes wrong. Then, we will move on to see what mechanisms PDI has to trigger jobs and transformations, and then we will move on to see how to schedule them.
Albert Nogués, Juan Valladares

Chapter 11. Moving to a Production Environment

Abstract
Following our recommendations and deployment steps until here, you will have an environment that will have all required elements to work, the relational database in place, an ETL system that brings information from the source ERP, a BI platform that allows you to analyze the information in an easy way, and an MOLAP platform that helps you in next year’s target definition. But we are quite sure that you won’t stop here. You will want to add new analysis, new fields, new calculations, new attributes, or new hierarchies to your system. It is possible that you want to add volume, more granularity, daily detail instead of monthly, or arrive to the same level of information than your transactional ERP system. You can require multiple modifications in your analytic system that can interfere in your database structure and your already created reports that are being used by multiple customers. In order to ensure that you have reliable data to offer to anybody analyzing it in your system, the best scenario is to have different environments for data analysis and for new developments, in order to avoid undesired affectations.
Albert Nogués, Juan Valladares

Chapter 12. Moving BI Processes to the Cloud

Abstract
Nowadays there is a trend to reduce the investment in machines, especially when it comes to small companies. Due to this, several top companies saw a business opportunity. Among them, Amazon, Google, and Microsoft. These companies realized that most companies can't/don't want to afford expensive purchases in hardware that may become obsolete just after a few years. They turned themselves into cloud services providers, which means, basically, that you can rent a part of a machine or an entire machine and run whatever you want there, without having to purchase expensive hardware.
Albert Nogués, Juan Valladares

Chapter 13. Conclusions and Next Steps

Abstract
Well we are almost finished – just a few pages more of suffering. But if you have arrived then here maybe you are not suffering so much; so we are really pleased that you are reading these pages. We haven’t done it so badly… But now that we have arrived at this point, what more can we do? This should be the question after you have completed successfully the implementation of your BI system with basic analysis of data located in your database, accessed from your BI system in a multiple environment that can be located fully or partially in the cloud. We expect you to have followed the book following also the examples, downloading and installing the proposed software (and maybe other options that you have heard about, especially for the BI front end there are multiple options with free versions of commercial tools), learning about the different tools while you play following our instructions so you can finalize this book having at least the initial Proof of Concept done. Of course we are aware that if you have read this book from end to end while testing things, you won’t have a productive multienvironment or all your servers in cloud; we understand that the first option for this kind of test is just a laptop to install all the required components to evaluate them.
Albert Nogués, Juan Valladares

Backmatter

Weitere Informationen

Premium Partner

    Bildnachweise