Juan Valladares

Data Modeling for BI Solutions

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.

Business Intelligence for Everybody

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.

Conclusions and Next Steps

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.

MOLAP Tools for Budgeting

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.

The BI Reporting Interface

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.

BI Process Scheduling: How to Orchestrate and Update Running Processes

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.

Agile Methodologies for BI Projects

Lets suppose that you are the leader of a BI project. Lets 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.

Moving to a Production Environment

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.