Business Intelligence

What exactly does data, information and insight mean in the context of BI? Data : The raw form from which information and insight can be derived. Usually any recorded values, numbers, text, audio, video, stored in any form, any size and any location that gets generated in any event or transaction or just based on the current state or status.  It could be internal data like employee related data (name, address, phone number, gender, etc.) or products, services related data or customer related data or server logs, web clicks, call center data , product reviews, ratings, etc. Anything and everything that can be used to derive information is data. The much hyped big data is also data. It could be stored as files or in a database or just as logs. Burger Chain Example   For example, the event of a customer buying a burger from a fast food restaurant generates lot of data. Time of purchase, terminal used for payment, employee who served the customer, amount and curre...

I wanted to profile data,  CSV files with 60+ columns and 1 million plus rows. I started searching for a easy to use tool that I could use for data profiling of these files.  That's when I noticed that data profiling is not clearly explained anywhere.  So here is my attempt to cover this important topic, and I will also introduce the tool, that I found out as part of my search, which helped me a lot to get to know the data. What is data profiling?  In simple words data profiling is a process in which we try to understand the characteristics of the data without associating it with a business process. So basically anyone can carry out data profiling for any data. You don't have to know who generates the data, where and how the data is generated, what is the context of that data. What are the answers we are looking for?  Some are listed below to give you an idea How many columns are actually there in the file? Does it match specification/documentati...

In the context of data analysis/BI  when is something called as a prediction and when is something called as a forecast? Quite a lot of people use these terms interchangeably. Dictionary also can't help here, see below. Source :  https://en.oxforddictionaries.com/definition/prediction So in the context of data analysis/BI I would say forecast is based on past trends.  Time series  is involved. Based on previous behavior future behavior is forecasted for a specific time period.  On the other hand prediction may or may not be based on past trends. So all predictions are not forecasts, but all forecasts are predictions. In this way forecast is like a subset of prediction. Example of a prediction which is a forecast - No of books that will be sold each month in the next 6 months. Example of a prediction which is not a forecast - Country X will win the world cup because they are a good team and in the best form compared to other teams. What do ...

Public data is all the data that is publicly available for everyone to make use for any purposes they wish to use it. And Open data is the subset of Public data. Open data is well-defined, maintained, generally more reliable, and there is some sense of assurance that there will be continuous availability of data as the data and the related documentations, APIs, access points, portals, etc., are  made available by the generator (source institution) or by authorized data aggregator organization. In this sense, from my point of view open data is the low hanging fruit within Public data. Open data is a subset of public data.  

As part of the welcome note during the recently organized PublicBI BIKON event I have clarified what exactly is Business Intelligence and also cleared the confusion around the definition of BI.  Watch this short video to get an understanding.

There is lot of confusion about terminologies. Have a look at the definition of Business Intelligence in Wikipedia (Business intelligence - Wikipedia). Pasted below for quick read. “Business Intelligence (BI) comprises the strategies and technologies used by enterprises for the data analysis of business information. BI technologies provide historical, current and predictive views of business operations. Common functions of business intelligence technologies include reporting, online analytical processing, analytics, data mining, process mining, complex event processing, business performance management, benchmarking, text mining, predictive analytics and prescriptive analytics. BI technologies can handle large amounts of structured and sometimes unstructured data to help identify, develop and otherwise create new strategic business opportunities.” Some of them wrongly assume that reporting and visualization tools as BI solutions (frontend) because that is the only part they see...

People become BI architects in different ways in different companies. A BI developer working in an in-house BI team over several years may get promoted to BI architect and then starts to perform the role of BI architect. Or a BI developer / analyst / tech lead working for an IT company with multiple client projects experience in various BI tools and technologies could become a BI architect with around 10 years of experience. Or a person switches to BI architect role from a non technical BI roles(BI analyst or BI Business Analyst) out of interest or better opportunities. A general set of tasks carried out by BI architect is given below, may not be true for all BI architects. A BI architect creates BI solution architecture diagrams and BI solution documents based on the high level program/project requirements. Creates technical roadmap. Is involved in providing response to RFPs/RFIs. Involved in BI tools selection, setting up framework (ETL framework, reporting templates, pro...

MOLAP (Multi-dimensional online analytical processing) is a type of OLAP. The other type is ROLAP (relational online analytical processing). BI reporting and analysis tools in the old days implemented either MOLAP feature or ROLAP feature. The tools were also described as MOLAP tool or ROLAP tool. But now the BI reporting and analysis tools are equipped with both MOLAP and ROLAP features and hence they are also referred to as HOLAP (Hybrid OLAP) tool. As these concepts are already extensively written about in various other places I will just highlight the major differences MOLAP - All the possible combinations of calculations are pre-calculated and the calculated data is stored as cubes to enable faster/shorter refresh time at the time of query. ROLAP - Data is stored in relational format (RDBMS) but using dimensional modeling technique to make it look like a cube without a physical cube, no pre-calculations, this is to avoid unnecessary storage of calculated data th...

A data warehouse is usually part of business intelligence solution. You can also have a data warehouse for regulatory reporting purposes or legal requirements. Data warehouse is where the data is historized (versioned), centrally stored after cleansing, transforming and unifying of the data from one or more data sources. Data warehouse is designed in a such a way that it makes it easy for reporting and data analysis on large amount of data. Without data warehouse it would be very difficult and time consuming to create consolidated reports based on data from various sources and also to do a time trend report as most of the source system store only current snapshot of the data for operational reasons. Data warehouse (DWH) in its simplest form is a data repository/store specifically modeled/designed for high performance and efficient reporting and analysis of historic, current and calculated data. Usually a good business intelligence solution is backed by a data warehouse. In a da...

ETL stands for Extract, Transform and Load. ETL is one of the main components in a BI solution backed by a data warehouse. ETL is also used in other projects such as data migration and data integration projects. ETL flows/jobs can be built by scripting or by using ETL tools. Most of the companies currently use one of the existing ETL tools to build ETL flows/jobs. In short, ETL tools abstract the technical complexity and thereby enables developers to focus on ”what needs to be done” than on “how it needs to be done”, For example a developer doesn't have to bother about developing a connector to a database, developers design the ETL flow/job using drag and drop, click and configure GUI of the ETL tool and also run, test, debug, schedule the ETL jobs using GUI of the ETL tool. ETL tools provide a visual framework for ETL developers to design the ETL jobs. It provides a level of abstraction of the code/script. There are many features that ETL tool provides but I won’t go in t...

In data mining, you are looking for hidden information but without any idea about what type of information you want to find and what you plan to use it for once you find it. As and when you dig into data and discover interesting information you start thinking how to make use of it to improve business. Example - A data miner starts digging into call records of a mobile network operator without any specific targets from his boss. Boss probably gives him a quantitative target to find at least 2 new patterns in a month. As he starts digging into the data he finds a pattern that there are less international calls on Tuesday (remember it is an example) compared to all other days. Now he shares this information with management and they come up with a plan to reduce international call rates on Tuesdays and start a campaign. Call rates go high, customers are happy with low call rates, more customers sign up, company makes more money as utilization % has increased. Watch out for these ...

Usually in data warehousing/business intelligence projects, ETL (Extract, Transform, Load) tools such as Informatica, Datastage, Talend, etc., are used to design ETL Jobs. Some companies still use scripting to develop ETL jobs. ETL testing can be as simple as Testing an ETL job manually by running it from a GUI verifying that the job runs, and validating the data loaded against expected data. or can be complex like Automating test data creation using scripting, SQL and tools like SOAP UI, using tools like Jenkins or ETL tools itself to trigger automated test data creation scripts and then automatically run the actual ETL job and then automated comparison of the loaded data against baselined data and highlight anomalies (if any) and auto check data against expected results for every single test case that was created earlier. Integrate the test scripts and test data creation scripts with the full set of ETL jobs so that it can be run during regression testing of the ETL ...

In the context of business intelligence, data wrangling is converting raw data into a form useful for aggregation/consolidation during data analysis. Before data is analyzed/visualized we need to ensure that we have unified the data. Simple example, if you want to visualize number of customers by city, then you need to ensure that there is only one row per city before data visualization. If you have two rows like Muenchen and Munich representing the same city this could lead to wrong results. One of the rows has to be changed manually by the data analyst/user and this is done by creating a mapping on the fly in the visualization tool and applied to every row of data to detect for more such issues and the process is repeated for other cities. In a BI solution backed by a data warehouse all of these data transformation, cleaning, mapping, etc., is dealt by the ETL/ELT before data is presented to the user and hence the end user doesn’t have to bother about these data preparatory ...

Datainformability   - The ease with which information can be derived from data should go into the list of important non-functional requirements (NFR) along with other NFRs' like availability, maintainability, security, etc. Goal should be to reduce the time taken to derive information from data to the minimum possible, of course conforming to all functional and other NFRs, while designing any application. Data is an important asset because we can derive information from it. So datainformability should be given its due importance i.e., every application should store data in a way that it is easy to derive information, instead of passing the responsibility to downstream applications. Some of the points (non-exhaustive) to be considered are Are we storing data including metadata? Storing enough and correct data? Is it easy to extract data? Easy to detect changes and extract only incremental data? Easy to analyze data? Are we standardizing data within the applic...

This article aims to clarify some of the topics related to business intelligence. Starting with the definition of business intelligence. Business Intelligence There is no one single agreed definition for business intelligence. Below are the ones that I believe are the widely used definition. Gartner - BI is an umbrella term that includes the applications, infrastructure and tools, and best practices that enable access to and analysis of information to improve and optimize decisions and  performance. Wikipedia - BI can be described as "a set of techniques and tools for the acquisition and transformation of raw data into meaningful and useful information for business analysis purposes". Forrester - A set of methodologies, processes, architectures, and technologies that leverage the output of information management processes for analysis, reporting, performance management, and  information delivery. My Version - BI is the process* of deriving information fro...