BI Analysis with Excel 2007 & SQL Server 2005

Most organizations collect a ton of data-be it of sales, revenue, customers,

employees, finances or anything else. All of this data can grow quite large over

time and making sense out of the data can become more difficult. This is where

BI (Business Intelligence) tools come in. These tools allow you to analyze the

data looking for 'patterns' that can show you what kind of trends you have been

having as well predict the future based on these patterns. You can even change

values around to see the effect it might have on the future. However, performing

these tasks requires a very good knowledge of databases, BI concepts like OLAP,

data warehousing and data mining before anyone can do the analysis. But there is

some good news for all non-database professionals as well. Microsoft has

released a couple of small tools that allow anybody to do a fairly complex

analysis of data. All you require for this is SQL Server 2005 Analysis Services

on the network you use. You then need to update this instance with the recently

released Service Pack 2. Once this is done, you need Excel 2007 on all clients'

machines and a new, small and free add-in from Microsoft called the Data

Mining Add-in for Excel 2007. This tool is available from the same location

as the download for the SP2 of SQL Server. Once the tool is installed on the

machine having Excel 2007, open Excel and the file that contains all the data

you wish to analyze. Select the table and make sure that the data is organized

as a 'table'. For this, select any cell in the data and click on the 'Format as

Table' button and choose a template. When prompted, make sure the entire set of

data is chosen in the range and the headers are appropriately selected. Now when

you click inside the table, you will see a new contextual tab called 'Table

Tools' that appears with two ribbons-Analysis and Design. The Design ribbon was

there previously as well and allows you to format the table. The Analysis Ribbon

is the one that we are interested in. The Data Mining add-in adds this new

ribbon as well as a more advanced 'Data Mining' ribbon. We will cover the Data

Mining ribbon in another article later. Click on the ribbon name to see the

options in it. The first time you click on the Analysis ribbon, a Wizard comes

up to prompt you for a connection to the SQL Server Analysis Services. Enter the

information prompted for-remember you will also need permissions to read and

write to this server. In most cases, an administrator will do this as a new

Analysis database is created in the server that will be used for all Excel 2007

analysis requests from different users and machines. Once created and selected,

Excel will use this database for performing all the analysis on the system.

Let us now take a look at the different analysis forms that you can use on

data. For the purpose of this article, we will take a sample demographic data of

customers of an organization that sells bikes. (Note: when you install the DM

Add-in, this sample Excel file is installed for you as well.)

Analyze key influencers

Key Influencer 'Marital Status' shows single customers are more likely to buy the product	Detected Categories shows the fields/values in that category and allows you to rename them

This button allows you to analyze the effect of other columns on the value

of a selected column. For instance, in the customer table, you might have

information like age, profession, marital status, car / home owned, etc. And the

field you wish to analyze could be whether the customer purchased your product

or not. Now if you wish to analyze which are the key influencers on the data you

have collected that tells you what factors lead to a product purchase you can

run this tool. For instance, you can select a criterion like marital status and

see if married people are more likely to buy your product or those unmarried.

This allows you to target your audience even better using BI. Detect categories

Your data could have characteristics that allow grouping different sets. For

instance, your customers might have 'groups' like 'Female, Highly Educated,

Professional', 'Male, Self Employed, High Income', etc based on their different

fields like gender, education, profession and income. To detect these

categories, the tool allows you to select the fields to analyze and then append

a column to the original table. It also creates a new sheet that displays the

characteristics for each category and allows you to rename them from the default

Category 1, Category 2, etc to something more meaningful like the names of the

groups above.

The renamed categories show up as a new column in the original data

Highlight exceptions



Although your data may be more or less accurate, there might be rows in your

data that do not conform to the overall patterns that are detected. For

instance, if a pattern is detected where males with a graduate degree are the

professionals earning high income, a row where a male customer with a graduate

degree is doing manual labor and earning low income is an outlier. This might be

due to a real case or maybe due to some error in the data gathering. This tool

highlights the exception row as well as the actual field that is the exception.

Goal seek and What-if



These two options do the opposite of each other. By using the Goal Seek

option, you can see what changes are required in your data to reach a particular

goal — for a single row or the entire data. For instance, to see what can make

your customers buy your product, you might want to see what effect is needed in

say, their marital status, to achieve that goal. On the other hand, the What-if

tool lets you change a value and see what effect it has on the final goal.

Forecasting



This is a tool that allows the system to analyze the patterns of data and

predict future values. The data to be analyzed must be numeric (sales, cost,

revenue, etc) and there must be at least one time/date field in the data so that

the 'past' can be used to predict the values for the 'future'. Fill form The

last tool in the Analysis Ribbon, Fill Form, allows another type of forecasting

where some values are already entered (as samples) and based on that the rest of

the values are predicted.

The highlight exception tool shows you the Exception  row as well as the relevant field in that row

The Goal Seek allows you to see what needs to change to reach a goal you set	The What-if shows you the effect on a target when  a value of another field is changed

The forecast tool shows the forecasted values based on numeric and time based data	The last column uses the one before it for the Form Fill function as samples