We are sure that by now, you must have heard of the self service BI concept that has been used with the release of SQL Server 2008 R2. Two new features have been introduced in SQL Server 2008 R2 in order to help provide self service capabilities to the end users. Those are: SQL Server PowerPivot for Excel 2010 and PowerPivot for Sharepoint 2010. We will do a series of posts on this new feature. So why was this feature introduced when there are already a lot of very good BI features available in SQL Server? As the marketing materials state, this falls into the area of “BI for the masses” – for those end users who don’t have a knowledge of ETL, programming languages, multidimensional databases but still have the responsibility of analyzing the data coming from different data sources and have been using Excel and Access to do that kind of work since ages. We have met many business users as well as financial folks who are very good data folks (someone who understands the data, their relationships, how to analyze and derive information from it etc.) and these sets of features is to empower them to be able to make better decisions easily and be able to build up their own analytic apps.
PowerPoint for Excel 2010 is an add-in that extends Excel 2010’s functionality to support working with large data sets as well as importing the data from external sources and integrating with local files. You can download the add-in from:
Once you install the add-in, you will see that a powerpivot menu has been added to Excel and it will also add a powerpivot window which is the design environment for working with powerpivot data within Excel:
But that is not the only thing that the add-in installs. It also installs a local Analysis Services engine on the machine and adds the client providers to connect to Analysis Services. This engine runs within the Excel process so you will not see any service related to in in your services mmc. And the install also installs an Atom Data Feed provider which allows us to import data from Atom data feeds into the powerpivot design window.
So, great – we now have the add-in installed and are ready to go. What’s the first step then? Identifying the data source(s) from where we will be pulling the data from. There are a lot of options that are available to us in this regard – when you click on “File” in the powerpivot window, you will see that you have four options:
a) Get External Data from Database: SQL Server, Access, Analysis Services or PowerPivot
b) Get External Data from File: These are for text files and excel files.
c) Get External Data from Data Feeds: Either from SSRS or other Data Feeds, and
d) Get External Data from Other Sources: This will give you the option to connect to data sources like Oracle, DB2, SQL Azure, TeraData, Sybase, excel files etc. – anything that you can connect to using ODBC/OLEDB providers.
So, as you can see – there are plenty of options to make sure that it covers a wide variety of data sources. Each of the above options then have their own configuration and connection options.
Let’s start with a simple example – we will extract out the data from the AdventureWorks2008R2 database using this query which lists out the sales person, their territory, their sales quota and their YTD Sales:
P.FirstName + ‘ ‘ + P.LastName AS SALES_PERSON
, ST.Name AS TERRITORY_NAME
, SP.SalesQuota AS SALES_QUOTA
, SP.SalesYTD AS YTD_SALES
FROM Sales.SalesPerson AS SP
INNER JOIN HumanResources.Employee AS HR
ON HR.BusinessEntityID = SP.BusinessEntityID
INNER JOIN Person.Person AS P
ON P.BusinessEntityID = HR.BusinessEntityID
LEFT OUTER JOIN Sales.SalesTerritory AS ST
ON SP.TerritoryID = ST.TerritoryID
ORDER BY P.FirstName + ‘ ‘ + P.LastName
Now, the steps to pull this into the powerpivot window are straightforward – click on File/Get External Data From Database and Select SQL Server. You can then choose to specify a query after choosing your connection and you will get the output that looks like this:
Once you have this output, there are a lot of things you can do before you dive into the analysis of this data set. Also note that we used a query to get the data in – one could have chosen to go with the table wizard and used the relationships, filters, columns to further refine the data set then. But if you already know your data, you can push a lot of that analysis already to the source.
In the next post, we will look into the powerpivot reports.