BI Analysis with Excel 2007 & SQL Server 2005

We take a look at using the new Business Intelligence analysis tools for SQL Server 2005 using Excel 2007 as a front end

Friday, June 29, 2007

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

Page(s)   1