Learn BI Introduction:

Business Intelligence is a process to convert data from various channels into meaningful pattern which help better understand business and help directors and senior management make better business decisions.

Learning business intelligence is about gaining technical skills with domain knowledge.This mixture can bring out critical information for business users from historic data in formatted reports which helps them better analyze the business and take proactive measures. Whereas end users can also create their own reports and analysis and easily share and collaborate with their colleagues.

Unlike other job profiles, BI professionals understand business, understand how organizations analyze data and make sense out of it to grow their business.

There are various BI software suites in the market which serve to this purpose. SQL SERVER BI is one of them which come bundled with SQL Server database software which also makes it more affordable.

In this series of posts I will take you over SQL Server BI suite with appropriate examples. It’s better to have TSQL (I want to Learn) knowledge before you begin. Please install SQL Server 2008 Enterprise edition if you don't already have and Adventure works sample database installed in it. (Click here to download sample database).

Reporting project has a better chance of being successful when the business requirements and the way it will be used are well defined and clearly communicated. As a report designer we should understand the source data. Having a better view of business requirements and the data will eventually result in a successful project, lacking either can easily spin of the project out of control.

If you are familiar with visual studio you will find great ease using BIDS (Business Intelligence Development Studio) tool used for designing /Developing BI projects.

Our BI suite comprise of three different parts which serve us with distinct Data Warehouse solutions.

•  SQL Server Analysis Services.
•  SQL Server Reporting Services.
•  SQL Server Integration Services.

   Below is the simple mechanism commonly used.

      Data is pulled from operational/transactional database using ETL/SSIS package which is a strategical design pattern to extract,transfer and load data in to data warehouse.Data is then pulled in from data warehouse using TSQL/MDX (TSQL in our case) query and then represented in formatted reports.

      You will see an updated version of this diagram soon which will also include OLAP(Online Analytical processing) data cube designed in SSAS which use MDX to pull data from data warehouse.

Retrieving Data Using T-SQL

Retrieving Data Using T-SQL
One of the primary operations you need to do using T-SQL on SQL SERVER is to retrieve data from the database. We will use AdventureWorks2008 database throughout our tutorials. To download go to http://msftdbprodsamples.codeplex.com/.

The most basic SQL statement will have a SELECT and a FROM clause as we have seen in our previous posts.

(1)USING SELECT STATEMENT
Below is the example using AdventureWorks database.

SELECT * FROM HumanResources.Employee;
To return relevant columns, replace the asterisk (*) with the
required column names, as code below.

SELECT LoginID, JobTitle, BirthDate, MaritalStatus, Gender FROM humanResources.Employee;

A where clause can also be added to select statement to limit the number of rows in the output based on a condition.

Try following code:

SELECT DISTINCT Color from Production.Product

The above select will select all the distinct colors from “Production.Product“ table.To limit the number of rows try the following code.

SELECT DISTINCT Color from Production.Product WHERE Color IS NOT NULL;

The above code will eliminate the null values from the output because we have specified a condition using WHERE clause for it.

Similarly, we try below sample code to eliminate silver color product and products ListPrice ranging below 200 and 0.

SELECT * FROM Production.Product
WHERE Color = 'Silver'AND (ListPrice > 200 OR ListPrice = 0)

We can specify various other operators in WHERE clause.
An Example of each is given below.

(i) BETWEEN OPERATOR

SELECT * FROM Production.Product WHERE ListPrice BETWEEN 0 AND 100

BETWEEN operator is used to specify a range of integer values in where condition. AND operator is compulsory while using this operator.

(ii)LIKE OPERATOR

SELECT * FROM Production.Product WHERE color LIKE ‘ %silv%’

When using LIKE operator we should use single quote to enclose a string. There are various ways you can use LIKE operator .For Example

Percent (%)-Replaces any number of characters (including 0 characters) in the
string. For Example.

‘%silv%’-will look at a color value(Data Type is String) which is having ‘silv’ in its value at any position.

Underscore(_)-Replaces only one character in the string. . For Example.

‘_silv%’ –Undescore will replace single character of a string and the word having ‘silv’ from second character and anything after that will be selected .

Square brackets ([ ])-Replaces any one character within a set or a range of characters. A set is frequently displayed as a straight list of characters, for example, [abcd]; but the characters can be separated by commas to add clarity, for example, [a,b,c,d]. A range is separated by a dash, for example,
[a–d]. Each of these three options includes all rows where the specified character is an a, b, c, or d. caret (^) Any character not within a set or range of characters.
For example, [^ad] would be equal to [e–z].

The following code sample returns all employees whose job title starts with the word
market. Because we are using a case-insensitive database, capitalization is not considered as part of the search criteria:

SELECT BusinessEntityID, JobTitle FROM HumanResources.Employee WHERE JobTitle LIKE 'Market%';

To search for all employees where the word market appears anywhere in the job title, you should change the LIKE expression to ‘%Market%’.

T-SQL (Transact SQl) Programming.

 INTRODUCTION TO T-SQL

It was developed by Microsoft research.it has various other features which we see in basic SQL.

The T-SQL Language is a superset of the ANSI specifications, it not only implements the required functionality, but it provides much more. It includes a number of programming extensions including variable handling, flow control, looping, and error handling, that allow the developer to write sophisticated logic to be executed on the server. In addition, the T-SQL language includes facilities for controlling access to the data, declaring relational integrity (DRI), and defining business rules to be enforced at the server level.


WHERE WE USE T-SQL?

The Transact SQL Language is the primary interface to the SQL Server database engine which is a product of microsoft. Any time we interact with SQL Server, whether it is through one of the provided administrative tools, a custom application, or through the command line interface ISQL, we are executing SQL statements. 

you can download a free version of sql server 2008 R2 Express from http://download.microsoft.com/ which is ideal for learning.

Lets start learning various features of sql server and how we can use it.

What Is SQL

  Learn what SQL (Structured Query Language) is, and how it is used when required.

The foundation of every Relational Database Management System is a database object called table. Every database consists of one or more tables, which store the database’s data/information. Each table has its own unique name and consists of columns and rows. The database table columns (called also table fields) have their own unique names and have a pre-defined data types. Table columns can have various attributes defining the column functionality (the column is a primary key, there is an index defined on the column, the column has certain default value, etc.). While table columns describe the data types, the table rows contain the actual data for the columns. Here is an example of a simple database table, containing customers data. The first row, listed in bold, contains the names of the table columns:

SQL Database Tables are the foundation of every RDBMS (Relational Database Management System).
Here is an example of a simple database table, containing customers data. The first row, listed in bold, contains the names of the table columns:

Table: Customers

FirstName	LastName	Email	DOB	Phone
Scott	Armstrong	Armstrong@yahoo.com	2/4/1968	626 222-2222
Adam	Red	red@fishhere.net	4/4/1974	323 455-4545
Santo	James	james@herowndomain.org	5/24/1978	416 323-3232
Jackson	Jimmy	jackson@supergig.co.uk	20/10/1980	416 323-8888

Now that we’ve learned what is a database table, we can continue with our sql tutorial and learn how to manipulate the data within the database tables.

Click Here to Start Learning.

SQL JOINS

The SQL JOIN clause is used whenever we have to select data from 2 or more tables.

To be able to use SQL JOIN clause to extract data from 2 (or more) tables, we need a relationship between certain columns in these tables.

We are going to illustrate our SQL JOIN example with the following 2 tables:

Company:

CustomerID	FirstName	LastName	Email	DOB	Phone
1	Scott	Armstrong	Armstrong.yahoo.com	2/4/1968	887-676-2321
2	Adam	Red	red@himail.com	4/4/1978	792 343-2422
3	Santo	Harry	Harry@livingin.org	5/24/1978	416 323-3232
4	Jackson	Jimmy	Jimmy@supermail.co.uk	20/10/1980	416 323-8888

Orders:

CustomerID	Date	OrderAmount
2	5/6/2004	$100.22
1	5/7/2004	$99.95
3	5/7/2004	$122.95
3	5/13/2004	$100.00
4	5/22/2004	$555.55

As you can see those 2 tables have common field called CustomerID and thanks to that we can extract information from both tables by matching their CustomerID columns.

Consider the following SQL statement:

SELECT Company.FirstName, Company.LastName, SUM(Orders.OrderAmount) AS OrdersPerCustomer

FROM Company, Orders

WHERE Company.CustomerID = Orders.CustomerID

GROUP BY Company.FirstName, Company.LastName

The SQL expression above will select all distinct Company (their first and last names) and the total respective amount of dollars they have spent.

The SQL JOIN condition has been specified after the SQL WHERE clause and says that the 2 tables have to be matched by their respective CustomerID columns.

Here is the result of this SQL statement:

FirstName	LastName	OrdersPerCompany
Scott	Armstrong	$99.95
Adam	Red	$100.22
Santo	Harry	$222.95
Jackson	Armstrong	$555.55

The SQL statement above can be re-written using the SQL JOIN clause like this:

SELECT Company.FirstName, Company.LastName, SUM(Orders.OrderAmount) AS OrdersPerCustomer

FROM Company JOIN Orders

ON Company.CustomerID = Orders.CustomerID

GROUP BY Company.FirstName, Company.LastName

There are 2 types of SQL JOINS – INNER JOINS and OUTER JOINS. If you don't put INNER or OUTER keywords in front of the SQL JOIN keyword, then INNER JOIN is used. In short "INNER JOIN" = "JOIN" (note that different databases have different syntax for their JOIN clauses).

The INNER JOIN will select all rows from both tables as long as there is a match between the columns we are matching on. In case we have a customer in the Company table, which still hasn't made any orders (there are no entries for this customer in the Orders table), this customer will not be listed in the result of our SQL query above.

 

If the Orders table has the following rows:

CustomerID	Date	OrderAmount
2	5/6/2004	$100.22
1	5/6/2004	$99.95

And we use the same SQL JOIN statement from above:

SELECT Company.FirstName, Company.LastName, SUM(Orders.OrderAmount) AS OrdersPerCustomer

FROM Company JOIN Orders

ON Company.CustomerID = Orders.CustomerID

GROUP BY Company.FirstName, Company.LastName

We'll get the following result:

FirstName	LastName	OrdersPerCompany
Scott	Armstrong	$99.95
Adam	Red	$100.22

Even though Santo and Jackson are listed as Company in the Company table they won't be displayed because they haven't purchased anything yet.

But what if you want to display all the Company and their orders, no matter if they have ordered something or not? We’ll do that with the help of SQL OUTER JOIN clause.

The second type of SQL JOIN is called SQL OUTER JOIN and it has 2 sub-types called LEFT OUTER JOIN and RIGHT OUTER JOIN.

The LEFT OUTER JOIN or simply LEFT JOIN (you can omit the OUTER keyword in most databases), selects all the rows from the first table listed after the FROM clause, no matter if they have matches in the second table.

 If we slightly modify our last SQL statement to:

 SELECT Company.FirstName, Company.LastName, SUM(Orders.OrderAmount) AS OrdersPerCustomer

FROM Company LEFT JOIN Orders

ON Company.CustomerID = Orders.CustomerID

GROUP BY Company.FirstName, Company.LastName

and the Orders table still has the following rows:

CustomerID	Date	OrderAmount
2	5/6/2004	$100.22
1	5/6/2004	$99.95

The result will be the following:

FirstName	LastName	OrdersPerCompany
Scott	Armstrong	$99.95
Adam	Red	$100.22
Santo	Harry	NULL
Jackson	Armstrong	NULL

As you can see we have selected everything from the Company (first table). For all rows from Company, which don’t have a match in the Orders (second table), the OrdersPerCustomer column has amount NULL (NULL means a column contains nothing).

The RIGHT OUTER JOIN or just RIGHT JOIN behaves exactly as SQL LEFT JOIN, except that it returns all rows from the second table (the right table in our SQL JOIN ).

MORE BASIC SQL COMMANDS