Business Intelligence: Tools used and skill sets required

In my previous post I had described what business intelligence is and how BI analysis is different from normal IT business analysis.  In this article I will try to explain the skillsets which are required for being a successful BI analyst.

Technical Skills

BI analysis requires knowledge of data warehousing and using analytical tools to analyze data. There are many BI tools available in the market and major ones are IBM Cognos, SAP BusinessObjects, Oracle Hyperion, Microsoft SSAS and SSIS. However I believe if you gain command over SQL and Excel (VBA) for BI reporting, then you can use any of the above tools quite easily and your learning curve will be very smooth. You can easily download trial version of SQL Server 2008 onwards and Microsoft excel to work on them. There are myriads of tutorials available on internet to learn sql and excel, which can be used. On top of that, I will give you practical BI cases which use SQL and Excel to analyze a business problem in future posts.

 More sophisticated BI employs data mining, business modeling and pattern recognition which use statistical modeling softwares such as SAS, SPSS and R. Knowledge of statistics and solving practical problems using any one of the tools can help you learn it . However it is better to join professional classes to venture in this field.

Data Extraction and Manipulation

For analyzing the data we need to extract it first. The data lies in the database from where it has to be retrieved. Knowledge of SQL is very important as it is used to extract the data. Generally the information required to do the analysis is present over 2-3 tables. Hence, knowledge of SQL joins (inner join, left join, right join) is a must.  The extracted raw data which is used for analysis is called as ‘dataset’. E.g. sales data of 100 shampoo bottles, containing product number, cost, margin, geography, can be termed as a dataset.

Business Intelligence without statistics

Normally BI reporting uses SQL or SAS to transform the retrieved data.  Transformation such as cleaning, sorting, merging tables, aggregating, grouping and transposing are done on the data to obtain required results. Then final output is represented in graphical format using excel charts and graphs. They are made dynamic by using pivot tables to represent the information.  Pivot filters can be checked and unchecked to roll up or drill down information. E.g. sales of the shampoo have been aggregated on city level (e.g. Mumbai, Delhi and Bangalore) and for 3 months.  You can manipulate filters to gain insights on the sale of the shampoo geographically and monthly.

Business Intelligence with statistics

 Statistics is used in BI for creation of business model, so that ‘what if analysis’ (running the simulation for various conditions to reach optimized results) can be performed and forecasting can be done.  Data mining is also very commonly used term in the field of BI, which uses statistics to recognize patterns from large datasets. While using statistics in Business intelligence, following topics come into use regularly: regression analysis, correlation, factor analysis, business modeling, forecasting, hypothesis testing, clustering etc. Hence, working knowledge of statistics is very important.

Generally SAS, SPSS and R are the common statistical tools, which are used to do statistical modeling. These are very powerful analysis tools which can handle large amount of data, running into millions of rows. Sometimes Excel is also used to do statistical analysis when dataset is relatively small.

Soft skills

Most sought after soft skills for BI analysts are good communication skills, personable and presence of mind. You have to Interact with business managers for gathering data, discuss analysis to be done and what are the required results management is looking at. There is lot of ad-hoc analysis too. Your analysis will influence their decisions; hence good rapport and communication skills are very important. These traits help you to understand correct requirement and successful synergy between you and the business managers.

 BI analysts need to have an eye for detail. There have been lots of times when a wrong decimal point in numbers can make your reports go haywire.Hence, Quality Check (QC) is very important aspect of BI reporting.

 One small example, let’s take 3 shampoos A,B and C. We have to present their sales in form of percentages to total sales  of the 3 shampoos. During QC, we should check that sum of these percentage add up to 100. Similarly, you should always have checking points in the report which ensure sanity of the reports.

Domain Knowledge

Last but not the least, domain knowledge or in other words, knowledge of the industry for which you are working is very important. It can be banking, finance, supply chain, sales, marketing, HR, I mean anything. 

I believe anyone can learn the tools and statistics for generating insights from the data. But the insights given by a person who has experience in that industry and has knowledge of these analytics tools will deliver even more matured insights. Obviously, a person with more information and experience, can connect the dots in a better way and give valuable insights. Hence, always try to know about the business of your company in depth, latest news, future strategy which is very important for a business intelligence analyst.