Business Intelligence: SQL Tutorial

If you are looking for free and complete guide to SQL tutorial, you have landed to the right place. As I had promised that I will guide you through acquiring skillsets required to become a successful business intelligence analyst, I am starting my tutorial with SQL.  Knowledge of SQL is very important if you are aspiring for BI analyst. SQL (Structured Query Language) is used to create and modify databases along with retrieving, modifying and deleting the business related data residing in the databases.

To start with, you should install Microsoft SQL Server 2008 Express in your computer. Following link will guide you step by step to install SQL on your system which is FREE!

http://www.functionx.com/sqlserver/Lesson02.htm

Data which has to be analyzed for understanding and predicting the business is stored in the databases. Understanding RDBMS is very important. Please find following link which explains databases from a beginner’s perspective. Even if you already know, brush up little bit.

http://www.quackit.com/database/tutorial/what_is_a_database.cfm

When database is created, it is used to store data on daily business e.g. sales data for a retail store,bank transactions, phone calls by a caller for mobile companies etc. We need to extract this data and analyze it. Hence, knowledge of SQL is very important for analysis.

Case Study:

I will start with a very simple example. You are given a Database DB_PhoneSales which contains table, 'Phone' and the data in the table is based on sales of 3 types of Phones. The sales data contains phone short name, city, month and quantity sold.  

Name	City	Month	Quantity
A	New york	Jan	169
A	Seattle	Feb	399
B	Florida	Jan	172
B	New york	Jan	185
B	Seattle	March	322
C	Seattle	Jan	294
C	New york	March	145
A	Florida	Feb	393
A	Florida	March	155
B	New york	Jan	135
B	Florida	March	151
C	New york	March	224
B	Seattle	Feb	228
A	Florida	Feb	499

You are required to find out the following:

a..       Which phone is sold the most?

b.       Which city has minimum number of phones sold?

How sql will be used to solve the above case study? These are the topics which you need to know:

1.      Data Type:

Business data can be in form of string(names, address), integer(age, phone number), decimals(interest rate e.g. 1.2, price$2.5), date (25/10/1994), etc.. You should understand this very carefully because choosing right data type in the beginning saves you from lot of problems and reworks which can arise later.

Case study

In the above case study you can observe that Name, City and Month are strings whereas Quantity is an integer.

Following link will give you all the data types used.

http://www.techonthenet.com/sql/datatypes.php

2.       DML statements: Data Manipulation Language (DML) statements are used to retrieve, update, insert and delete the data.  W3S School is a very good website for learning SQL. You can brush up, every time, very quickly for your interview by going through this website. Please go to the link and explore DML, SQL functions, joins etc.

http://www.w3schools.com/sql/sql_select.asp

Case Study

Now I assume that till now you have installed SQL in your computer. If not please do it, what is stopping you? After that you have gone through the data types and DML statement tutorials. You can easily tell that following statement will be used to retrieve data from database:

Select name,city,month,quantity from Phone

3.     Aggregating Statements:

In business intelligence you will be grouping and ordering data most of the time. Generally, the data which you receive is at a very detailed level. You need to group it so that you can see the overall picture of your business.

http://www.w3schools.com/sql/sql_groupby.asp

Case Study:

In our case study we can see that quantity of phones sold is at monthly and city level. But you want to know overall which phone is being sold the most. So you sum the quantity for the phone and see which is the highest.  Please find the SQL statements used to arrive at required result.

SELECT NAME, SUM(QUANTITY) AS TOTAL_QUANTITY FROM PHONE ORDER BY TOTAL_QUANTITY DESC

Name	Total_Quantity
A	2004
B	1839
C	973

SELECT CITY, SUM(QUANTITY) AS TOTAL_QUANTITY FROM PHONE ORDER BY TOTAL_QUANTITY

City	Total_Quantity
Seattle	1382
New york	1702
Florida	1732

Now the top row will give you the required result.

4.    Joining tables

In databases you must have read about relational database. You will have a database with tables which are related rationally. RDMS breaks the data and stores them in a very meaningful manner but most of the times, for reporting purpose, you need to combine the broken pieces of data. Here joining tables come into picture. Please complete the below tutorial and practice to get hold of SQL joins.
Tutorial:

http://www.w3schools.com/sql/sql_join.asp
Practice:

http://sqlzoo.net/3b.htm

Case Study

 We already know that the database DB_PhoneSales has a table phone. Now you to know the corresponding name of the phones. The second table PhoneName contains corresponding phonename.
   

Name	City	Month	Quantity
A	New york	Jan	169
A	Seattle	Feb	399
B	Florida	Jan	172
B	New york	Jan	185
B	Seattle	March	322
C	Seattle	Jan	294
C	New york	March	145
A	Florida	Feb	393
A	Florida	March	155
B	New york	Jan	135
B	Florida	March	151
C	New york	March	224
B	Seattle	Feb	228
A	Florida	Feb	499

Code	PhoneName
A	AlphaPhone
B	BetaPhone
C	CellPhone

The SQL query will be:

SELECT B.PHONENAME, SUM(A.QUANTITY) AS TOTAL_QUANTITY FROM PHONE AS A INNER JOIN PHONENAME B ON A.NAME=B.CODE ORDER BY TOTAL_QUANTITY DESC

Name	Total_Quantity
AlphaPhone	2004
BetaPhone	1839
CellPhone	973

5.       Nulls

Understanding of Nulls takes you one step higher than a beginner. It is very common to mistake null with zero. However null means nothing. you cannot add 5 and null. However you can add 5 and 0, which will give you 5.
There will be many instances when the dataset which you will receive will have null values. They create problems when you try to sum the values, join the tables, etc. Please go through the following link to gain thorough understanding on NULL values.

http://www.w3schools.com/sql/sql_null_values.asp

http://sqlzoo.net/4a.htm

6.       Index

The data which we generally get in the industry is very huge. It can have thousands to           millions rows. Hence when you are searching data, it can be a very slow process.  Indexing the database can makes the search process faster. However it has side effects also. For full information please go to the following links:

http://www.tutorialspoint.com/sql/sql-indexes.htm

http://odetocode.com/articles/70.aspx

7.       Analytics functions

Now days you can get inbuilt analytical functions which will help you a lot in analysis processes. These have become very famous in Business Intelligence Interviews lately. Please go to the following links to learn them.

http://msdn.microsoft.com/en-us/library/hh213234(v=sql.110).aspx

http://www.orafaq.com/node/55

I guess If you properly go through these links, study them and assimilate properly, you are good to go with SQL skills for business analysis.

So remember these websites which are very helpful in learning SQL. Ihave already mentioned them above but would like to summarize.

http://www.w3schools.com/sql/

http://sqlzoo.net/

http://www.functionx.com/sqlserver/

And ofcourse always take help of MSDN. It is a huge repository, filled with knowledge on Microsoft technologies. For further practice you can install AdventureWorks database, which is a fictitious company database provided by Microsoft, which is very helpful in practicing and learning SQL. Please find the link:
http://msdn.microsoft.com/en-us/library/ms124501(v=sql.90).aspx
Please give me your review comments so that I can make this blog better.

Happy Studying J


Next is MS excel. So easy but such a powerful tool for analysis.

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.

Business Intelligence: What is it and for whom?

What is Business Intelligence?

Have you heard following news recently:

Target (retail giant) figured out a teen girl was pregnant before her  father did!

Credit Card companies have fraud detection systems which can find fraudulent transactions!

Surprised? Do you know what is behind these amazing, futuristic sounding news? It is data Analytics and Business Intelligence(BI). It is possible to analyze huge data using analytic tools to bring out useful information and  find patterns. Currently there is a deluge of data generated by  customer relationship management,marketing, finance, supply chain management and  human resource management systems .Unorganized, this data is nothing but a clutter, however when it is transformed and analyzed, it can give a very clear picture of business. BI is becoming necessary for understanding business, finding business trends, patterns and setting standardized indicators (KPI) to determine health of any organization.

For whom this blog is meant for?

This blog will help students, working professionals in IT, who have passion for analyzing data, generating business insights and basically who want to understand the meaning of data which is being created by the IT applications. It will be especially helpful to my IT friends who are interested in business side of the industry.

I have spent considerable amount of time working for IT companies, working as business analyst. However I was always more interested to go towards the business side without losing my technical knowledge which I had gained so far. Hence I decided to go towards business intelligence (BI) and it has been a satisfying experience. This blog will help aspirational BI business analysts through tutorials, discussing practical examples of using BI in solving business problems, current scenario of BI in the industry and future trend. Your one good insight can lead to profit or savings in tune of million dollars for a big organization.

How BI Business Analysis is different from conventional BA?

The concept of Business Intelligence Business Analysis is still new. Generally people confuse it with Business analysis for IT which is involved in requirement gathering from client to convert business requirement to technical requirements. IT Business Analysis is generally involved in creating Business requirement documents (BRD), Functional requirement documents (FRD) for developers(coders) to create IT applications. It is bent more towards IT side of the industry, helping in development of applications, which requires extensive technical knowledge but relatively less understanding of business.

However, BI business analyst comes into picture when these applications are successfully put into place, generating day to day data. This data contains lot of hidden information, which can be used by the companies to understand how their business is doing. They can analyze the data to understand opportunities, strength, weakness, and future trends for the companies. This is where business intelligence comes into play.

Importance of BI

In the business industry I have seen managers struggling with data analysis tools because they are technologically challenged. They are handicapped because of limited knowledge in SQL, Excel, SAS etc. IT associates can easily learn these BI tools (which I will discuss in my future blogs), perform the analysis and derive insights which are very important for the business. 

BI talent pool is in high demand and according to  McKinsey, there will be a shortfall of 1.5 million BI analysts by 2018. BI calling you.

Resources:
http://www.forbes.com/sites/kashmirhill/2012/02/16/how-target-figured-out-a-teen-girl-was-pregnant-before-her-father-did/

http://www.businessweek.com/articles/2012-04-23/why-b-schools-should-teach-business-intelligence