by June 29, 2007 0 comments

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

Direct Hit!

Applies To:
Even a non-technical user can
do advanced BI analysis
Primary Link:

Google Keywords:

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.

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

No Comments so far

Jump into a conversation

No Comments Yet!

You can be the one to start a conversation.

Your data will be safe!Your e-mail address will not be published. Also other data will not be shared with third person.