PowerPivot Training Resources

I’ve been asked to provide links to some useful resources for learning about PowerPivot. Below are a list of my favorite blogs, books, and other sites to learn from.

A quick disclaimer, some of the links below are by co-workers or other people I have an affiliation with, financial or otherwise. That’s because I’m lucky enough to work with some of the best people in the field. Also, in the case of the books I’ve linked to the Kindle version where possible, mostly because I’m a Kindle junkie. There are paper versions of the books, and you are free to buy from your favorite retailer.

Books

Professional Microsoft PowerPivot for Excel and SharePoint – This book covers all aspects of PowerPivot, from using it to installing it to configuration. Everything you want to know in one volume. I wouldn’t particularly call this a beginners book however, it assumes you are competent in BI, Excel, and SharePoint.

Microsoft PowerPivot for Excel 2010: Give Your Data Meaning – This is a good first book to get, it goes deeply into the use of PowerPivot within Excel.

Practical PowerPivot and DAX Formulas for Excel 2010 – Once you are comfortable with PowerPivot, you’ll want to learn more about DAX, Data Analysis eXpressions, the set of functions used to do advanced calculations and aggregations in PowerPivot. I’m a big fan of the way the author, Art Tennick does his books. It’s the Problem – Solution approach, where he demonstrates a common problem then shows one or more ways to solve it. Art also has books on MDX and DMX you should check out.

Blogs

PowerPivot Pro – Probably one of the best blogs around, Rob and Kasper provide excellent content.

Denny Lee’s Blog – Denny works for Microsoft and is part of the PowerPivot team. He provides some really great insights, and is co-author of the Professional Microsoft PowerPivot for Excel and SharePoint book above.

PowerPivot Info – Not so much a blog but a blog aggregator, this site brings the best PowerPivot content on the web to the forefront.

Videos

Pragmatic Works Webinars – On our website we have a big catalog of past webinars (all of which are free to watch), many of which focus on PowerPivot.

Pluralsight – A little shameless self promotion here. I did a complete course on PowerPivot for Pluralsight. This includes both using PowerPivot from Excel and managing PowerPivot within SharePoint. In addition, Pluralsight has an extensive catalog of other courses you can pick from. It’s subscription bases so there is a modest fee (starts at $29 US per month last I checked) but well worth it for the training you can get. There’s also a free trial.

For a quick link direct to this post, you can use http://bit.ly/arcanepivot

PowerPivot with Pluralsight

For the last few months I’ve been cooped up here in the Arcane dungeon, working on a new project. And now that’s it’s released I can finally share all the details.

While I’m still happily employed during the days as a BI Architect for Comframe, in my spare time I’ve been acting as a technical advisor to a training company called Pluralsight.

I’ve developed a series of four training modules on PowerPivot for Excel 2010. The first module is an overview, that gives you the basic steps on creating a pivot table using PowerPivot.

Module 2 goes in-depth on working with data behind the scenes. How to add calculations, formatting data, filtering data and more.

In the third module I cover the creation and formatting of Pivot Tables, formatting them for users, using DAX (Data Analysis eXpressions), and how to use Sparklines with PowerPivot data.

In the last module we explore the charting capabilities of PowerPivot, covering various types of charts and ways to format charts. We then use the knowledge we’ve gained over the last four modules to create a realistic dashboard.

If you are already a subscriber to Pluralsight, take a look, the courses are now live. If you aren’t, take a look, Pluralsight has the best training I’ve seen on the net and it’s growing daily.

What I learned at TechEd

Last week I was at the Microsoft TechEd conference in North America, along with over 10,000 of my closest friends. I spent a lot of time in the Microsoft floor area talking to people, and came away with some interesting info about new technologies. As I’m sharing some of these at the Steel City SQL user group tonight, I thought I’d share here too.

First up is OData, the Open Data Protocol from Microsoft. It is an ATOM feed but for data. People can publish under the OData format and be able to consume the data from either a JSON or AtomPub. You can also add security, should you wish to have data available to many consumers but only on a permission basis. You can learn more at http://www.odata.org

Next up is Microsoft’s new “Dallas” project. Dallas is the code name for a data marketplace on it’s Azure platform. Through Dallas users and vendors will be able to consume / provide data feeds. Some will be free, others will be at some cost. There is a catalog through which consumers can look at the various feeds available. This is very much in it’s infancy but there are a few feeds which you can look at and preview.

Microsoft’s SQL Server 2008 R2 Parallel Data Warehouse looked interesting, although it fits a very niche market. It’s an appliance you can purchase that is essentially a rack of SQL Servers. One is the master server, and coordinates all the child servers. As a DBA you manage what appears to be a “normal” instance of a SQL Server. Behind the scenes the controller will propagate changes to the other servers in it’s hub. Scaling can be achieved by simply adding more servers to the existing rack, or additional racks as needed. PDW becomes economical starting around 10 terabytes and scales to well over 100 terabytes of data.

The folks at Red Gate have a new tool called SQL Search that they have released for free to the community. SQL Search is an add-on for SQL Server Management Studio that does lightening fast searches of object names in your database. Just pick the database name and term to search for and SQL Search will populate a grid with all possible matches. If you double click on the row it will navigate SSMS’s Object Explorer pane to the correct spot in the navigation tree with your object. Further, if the object is a view, stored proc, etc it will even display the SQL of the object and highlight the searched item. And did I mention it’s free?

Speaking of cool, free tools the folks at Confio have created a free version of their popular Ignite tool called IgniteFree. It is a real time performance monitoring tool that will work with not just SQL Server but Oracle and DB2 as well. They have versions of the tool that run on both Windows and Unix/Linux.

PowerPivot continues to fascinate and excite me, while I was at TechEd I won a copy of “PowerPivot for Excel and SharePoint”. I had this on my “to buy” list anyway so considered myself lucky. I’m about a sixth of the way through the book and it has been really good so far. It starts with a quick tour of the Excel piece, then walks you through the SharePoint install so you can quickly get up and running in a test environment. Later chapters delve much more deeply into PowerPivot. If you are looking for a good PowerPivot book I would recommend it.

Finally, even if you couldn’t be there you can watch the sessions from this and past Tech Ed’s. Microsoft has released them to the general public at http://www.msteched.com/

 

*FTC Discloser, I am in the “Friends of Red Gate” program where I get copies of their tools in order to test and provide feedback. In this case the disclaimer probably isn’t necessary since the SQL Search tool is freely available to all, but I’d prefer to keep things above board.

SharePoint Saturday Huntsville

It’s another Saturday, must be time for another speaking engagement. Today I’m in Huntsville Alabama speaking at their SharePoint Saturday. Now you may wonder what a SQL BI guy is doing at a SharePoint event. There are two kinds of BI professionals in the world, those who are currently working with SharePoint and those who will be working with SharePoint. I believe the converse could also be said for SharePoint people. There those who are currently hosting BI solutions, and those who will be hosting BI solutions. It’s important then, that SharePoint professionals have a clear understanding of what business intelligence is, including all the terms that get used in the BI world. In addition SharePoint 2010 contains features to support a new tool called PowerPivot. Understanding what a PowerPivot workbook is, and how their customers will be using it will give SharePoint professionals better understanding of how to host PowerPivot content.

My first session of the day is entitled “Off and Running With PowerPivot for Excel 2010″. In this session I’ll be giving an overview of PowerPivot, then we’ll be looking at how to import and manipulate data within Excel. This presentation is demo heavy but there are a few slides which can be downloaded from this link.

In my second presentation we’ll dig into the guts of data warehousing and business intelligence. Business Intelligence has its own vocabulary, during the session the audience will learn all the buzzwords around BI and dig into the data warehouse to see how these terms are implemented. This particular presentation is a slide heavy deck, unlike my previous session there is little in the way of demonstrations. We’ll briefly touch on SharePoint, understand though that this presentation centered largely around understanding what BI is so you’ll understand what your SharePoint system is being used for. The slides are available from this link.

If you are reading this after the fact, then let me thank you for coming to my presentations. I hope you found them useful, please don’t hesitate to contact me if I can be of further assistance.

Calculated Columns in PowerPivot

When importing data into PowerPivot, users often find the data is almost, but not quite what they need. Perhaps the name is not quite formatted as they need, or some calculation, not important in the stored data but very important to their work, is missing. For these situations PowerPivot offers Calculated Columns.

Calculated Columns provide a way for users to add that missing information they require into the source data. The calculations are done on a row by row basis, if you want to do something on the entire table, for example count the number of rows, you will instead need to create a measure in your PivotTable or PivotChart. Measures will be covered in a later post.

Let’s get started by using the same Excel 2010 workbook we ended with in the previous blog post. If you haven’t seen it, please go back and reference my post Combining Data from Multiple Sources in PowerPivot for the full details.

Our first task will be to address our customer names. In the source data, names are broken into five columns: Title, FirstName, MiddleName, LastName, and Suffix. For ease of use we wish to combine these distinct columns into one single column. Assuming you have opened the PowerPivot workbook, select the Customer table from the list of tabs at the bottom. Now go to the right-most column, ModifiedDate. Next to it you’ll see a blank column with the header “Add Column”. Click in it, then go up to the fx box right above the data.

clip_image001[6]

The formula bar:

clip_image002[6]

Into this formula bar we can create some fairly complex expressions. Let’s do one that shows some of the power of text formulas. Into the formula bar enter:

=[Title] & " " & [FirstName] & " " & IF(LEN([MiddleName]) > 0, [MiddleName] & " ", "") & [LastName] & IF(LEN([Suffix]) > 0, " " & [Suffix], "")

As with Excel, formulas need to begin with the equal sign. All literal string values are enclosed in double quote marks. Here we have two, a single space in the form of “ “ and an empty string in the form of “” (two double quotes right next to each other). The ampersand & character is used for concatenation. When using column names in the formulas, they must be enclosed in square brackets [ ] . Finally notice we’ve leveraged some standard Excel functions, first the LEN function which returns the length of the past in field. Then the IF function which evaluates the first statement (for example, LEN([MiddleName]) > 0 ). Then the area after the first comma ([MiddleName] & " " ) is returned if the statement was true, otherwise the area after the second comma ("" ) is returned.

After pressing enter on our formula PowerPivot will then calculate the values for each individual row in the dataset. The downside is this could take quite a while depending on the size of your data. A 100 million rows of data is going to take a while, even on a fast machine. The benefit though is this is the only time the calculation is done, unless of course the underlying data changes. Values are now calculated and available at analysis time.

You may notice the column name changes from Add Column to CalculatedColumn1. Since this is not something we’d want to show other users, or work with ourselves, simply right click on the column header, pick Rename Column, and give our new column a meaningful name. In this example I used FullName.

clip_image003[6]

A quick side note, in the sample data the MiddleName and Suffix columns are not populated very often, as is often true with real data. However it can make browsing through our data a bit difficult. To validate our calculation, click the drop down menu triangle next to the MiddleName column, go to the bottom and uncheck the “Blank” option for data filtering. This will then remove all rows from the viewed data that are missing a middle name.

Note this doesn’t delete the rows, this is merely a filtering option in PowerPivot to help you view only the data you want. The other rows are still there, to prove it just click the menu arrow again and pick the “Clear filter from MiddleName” menu option and all rows will again be visible. For more information on filtering, see my post Import Filters in PowerPivot. The same filtering tools that apply to the data import process also work once the data is imported.

In addition to textual manipulation, PowerPivot also supports complex math calculations. Let’s do a simple example in the SalesOrderDetail tab. For simplicity, let’s decide that our base profit for any sale is 20 percent of the Line Total. However, for each item ordered we gain an extra 2 percent of profit. We’ll click in the Add Column area of the SalesOrderDetail tab and enter the following calculation:

=(.2 + ([OrderQty] * .02))*[LineTotal]

Now we can rename the column to EstimatedProfit using the rename menu option as described above.

clip_image004[6]

We also have the power of the Excel math functions at our disposal. Let’s do something simple, and decide that we want to round the value of our EstimatedProfit column up to the next whole value. Even if the value was 1.01, it would round up to 2 dollars. To accomplish this we can use Excel’s ROUNDUP function:

=ROUNDUP((.2 + ([OrderQty] * .02))*[LineTotal], 0)

Yields these new results:

clip_image005[6]

As you can see, they have indeed been rounded up to the next whole value. The 0 at the end of the formula indicated how many decimals should remain, I indicated none so we could see the results in whole dollars.

We’ve only just begun to explore the value in Calculated Columns. Not only can they fill in missing data, but they can also speed calculations when you reach the Pivot Table stage of your analysis by making aggregations much easier.

Combining Data from Multiple Data Sources in PowerPivot

Seldom does the user of PowerPivot have all of the data they need in one nice, neat data source. More than often it will be necessary to import data from a variety of sources and make that data work together. It’s time to start building on what we’ve learned over the last few days to accomplish this feat.

First, launch Excel 2010 and use the PowerPivot import wizard to import the following tables from the AdventureWorksLT2008 database: Address, Customer, CustomerAddress, Product, ProductCategory, SalesOrderDetail, SalesOrderHeader. (Note, for a refresher on importing data please see my blog post, Import Filters in PowerPivot.)

Now we need a second source of data. Follow the instructions in my post Creating Tables in PowerPivot to enter the data below into Excel 2010, copy and paste it into a new PowerPivot table.

clip_image001

If you recall when we import data from a relational database, PowerPivot examines the foreign key relationships found in the database to create relationships between the tables it imports. In this situation though, the CountryInfo data didn’t get imported from a database, instead it was pasted in from a manually entered spreadsheet. Thus, PowerPivot has no information with which it can implicitly create a relationship.

We do want to create one however, so we can link the longer country name in the Address table to the CountryInfo data and thus be able to use the briefer country abbreviations. As PowerPivot was designed to work with many sources of data, it has an easy way to create these relationships.

In the PowerPivot window, click on the Table tab at the very top. All the way to the right you will notice a button group named Relationships. Click the Create Relationship button.

clip_image002

As the above dialog shows, this allows you to create a relationship, or a link between two tables in PowerPivot. Here we are creating a link between the Address table and the CountryInfo table on the CountryRegion field. When complete just click Create to create the relationship.

If you want to verify the relationship was indeed created, or review any of the relationships PowerPivot inferred when it imported the tables from the AdventureWorksLT2008 database, just click the Manage Relationships button in the Table Toolbar’s Relationships group.

clip_image003

On the very first row you’ll see the newly created relationship between the Address and CountryInfo tables. You’ll also see the other relationships that were created during the import process from the SQL Server database. The three buttons at the top let us Create new relationships, Edit existing ones, or Delete ones no longer needed. Note that the altering or deleting of relationships has no effect what so ever on the original source data (SQL Server or the Excel 2010 spreadsheet). It only affects the tables as stored in PowerPivot.

Now let’s see the new relationship in action. Close the Manage Relationships window, and on the PowerPivot Home tab create a new PowerPivot table (Pivot Table, Single Pivot Table). Go ahead and put it in a new worksheet.

In the Gemini Task Pane, go to the SalesOrderHeader table and drag the LineTotal field into the Values area. Next, drag the Name field from the Product table into the Row Labels area. Now for the magic, in the CountryInfo table drag the CountryAbbr field into the Column Labels area. Your pivot table should look something like this:

clip_image004

Because of the relationships that were inferred or that we created, PowerPivot was able to link the data like so:

1. SalesOrderDetail linked to SalesOrderHeader on the SalesOrderID column.

2. SalesOrderHeader linked to Address on the ShipToAddressID=AddressID.

3. Address linked to ContryInfo on the CountryRegion column.

To validate this for yourself, just return to PowerPivot and look at the Manage Relationships dialog to see all the links.

The need to combine data from many sources is a common task, one that will most certainly be done by users of PowerPivot. Using the techniques shown here, you can create and manage the relationships that will link data from these disparate sources together and leverage the power of PowerPivot.

Creating Tables in PowerPivot

PowerPivot has the ability to import data from a wide variety of sources. But you could run across a situation where you don’t have that data stored anywhere. Perhaps it’s on a piece of paper, or in a text file, or it’s just in the user’s brain and needs to be typed in. Logically then you would want to create a new table in PowerPivot.

Except you can’t. PowerPivot itself doesn’t provide the ability to create tables and enter data directly into it. Now, before you start the usual rending of garments and gnashing of teeth plus a little wailing, there is a simple to implement solution.

Create a new Excel 2010 workbook. In sheet 1 (or any sheet) let’s enter the following information.

clip_image001

Now highlight the above cells and Copy them to the clipboard. Next, launch the PowerPivot window by going to the PowerPivot tab in Excel 2010 and clicking the PowerPivot window button.

Once PowerPivot is open, if you look in the middle group of buttons you’ll see a set named Paste from Clipboard The To New Table button should be activated now that you have data in your clipboard.

clip_image002

Click the To New Table button. When you do, the Paste Preview dialog appears.

clip_image003

This is similar to the preview window you see with the Import Table wizard, only not quite as much functionality. Here, we can view the data and validate that it is correct, which it is. We can also indicate if the first row contains our column headers, which in our case it does so we can just leave that option checked on. Click OK to import the data.

clip_image004

Above is our new data, now pasted into PowerPivot. We have the same abilities with it we have with any other table, we can sort, rename our columns, add new calculated columns, and more. As you will note from the tab at the bottom of the picture, the data was pasted into a table with the rather uninformative name of Table. We can do better than that, so right click on the Table tab and pick Rename from the menu. Overwrite Table with CountryInfo.

clip_image005

Now you can see how easy it is to create new data from scratch and paste it into PowerPivot. In this example I used a limited number of rows for illustrative purposes, but it’s quite possible to import massive amounts of data. In addition, you can add to your table later. In this example all we would have had to do is Paste Append from the toolbar.

In the next blog post we’ll build on what we’ve learned and look at how to combine data imported from multiple sources.

Import Filters in PowerPivot

PowerPivot has the ability to import millions of rows of data into Excel 2010 for purposes of analyzing, slicing and dicing. However, even though you can import vast amounts of data you may not always want to. There are many reasons for this.

You may wish to limit the amount of data for security reasons. Perhaps bringing in all of the data may put an unneeded strain on the server. Most likely though, is you simply do not need all of the data. As an analyst you may be interested in only the data for a segment of your organization, such as a single plant or department.

Fortunately filtering data is an easy task. If you are not familiar with importing data into PowerPivot, may I suggest you first review my step by step posted yesterday.

Launch Excel 2010 and go to the PowerPivot tab, then launch the PowerPivot window. Select the "From Database" to start the import process, and for this example we’ll use SQL Server. Now, like in my previous blog post enter your credentials to connect to a SQL Server. For this example we’ll just pick one table, SalesOrderDetail from the AdventureWorksLT2008 database.

With the SalesOrderDetail picked, look in the lower right side of the dialog. You will see a button labeled "Preview & Filter".

clip_image001

When you click that button you should see a new dialog appear.

clip_image002

Here you can see a subset of your data, only the first few rows. It’s enough to give you an idea of what the data looks like, but it won’t show you every single row. Considering the fact that PowerPivot is capable of importing millions of rows, this is probably a good thing.

Within this dialog we can do some pretty powerful things. Let’s start by eliminating a column we don’t need in the data we want to import. Scroll to the right until you see the rowguid column and uncheck the box next to the column header, as you see below.

clip_image003

By unchecking this box this column will not be imported into our Excel 2010 PowerPivot table.

Recall though that the name of this dialog was Preview & Filter. We can also do some review of our data to ensure it’s what we want. Let’s say we want to look to see the range of values for our line totals. Click the downward facing triangle button to the right of the LineTotal column header. A drop down menu will appear. Select "Sort Smallest to Largest"

clip_image004

Scrolling through the data you’ll notice that the data has indeed been sorted. Also note the menu icon to the right of the column name changes to indicate a sort has been applied to this column. (Remember though it’s not showing all rows, just the first few sorted in order.) An important thing to note tough, this sorting applies only to the data as you see it in the Preview & Filter area, once you click the Finish button on the Table Import Wizard the sort is removed. While the preview options in this dialog are not saved, filters are. If you had pressed Finished, you’d have seen that rowguid is not included in the result set. But don’t click Finish yet, we’re not quite done filtering.

For our next filter, let’s decide for purposes of this report we are only interested in large orders. We’ll define large as "Line Total greater than 1,000 dollars". Once again open the menu to the right of the LineTotal column header and select "Number Filters". A pop out menu will appear, from it select "Greater Than…"

clip_image005

When the Custom Filter pops up, enter 1000 next to the is greater than box and click OK.

clip_image006

Clicking OK will reveal lesser amounts have been removed. Other filters besides numeric filters are available. For text data, you have "is equal to" and "is not equal to" available. Date filters work the same, having the "is equal to" and "is not equal to" available. I’m hopeful that for date types further functionality will be added in the future, such as date ranges or "is greater/less than" types of functionality.

Let’s click the OK button on the Preview & Filters window to return to the Table Import Wizard. You’ll now see an Applied filters link in the row with the table name, click it to see what filters are in effect.

clip_image007

When you click the link you’ll see:

clip_image008

While you cannot edit this information, it is nice to see it all in one location. Click OK to close and return to the previous window.

Click Finish to complete the import. You will see the data you asked for, only rows with LineTotal greater than 1000 and without the rowguid column.

Hopefully you’ve seen how powerful the filtering tools included with PowerPivot are. Using them you can remove unwanted rows and columns, limiting not only the amount of data you have to pull across the network but that you have to store locally in the PowerPivot Excel 2010 spreadsheet. Limiting your data will ensure only the rows required for the analysis are included, saving time and enhancing security.

SharePoint Saturday Birmingham – Intro to Microsoft PowerPivot

spsbham Last Saturday I not only attended but presented at SharePoint Saturday Birmingham. First I have to give Terry Webster a shout out, he did a fantastic job organizing and running the event. I learned a lot, although I admit I felt a bit like a fish out of water, being a SQL Server guy in the SharePoint pond.

Thanks too to all the volunteers who helped run the show, my co-presenters, and the many vendors who attended. Without the support of all those folks this event would not have been possible.

I also want to thank everyone who attended my PowerPivot presentation. Last Friday I did a rather lengthy overview that encapsulated much of my presentation; here are the slides that you saw at the event.

I also want to give you a quick overview of the steps I did in my demo so you can follow along. First you’ll need the bits. The PowerPivotPro has a good blog entry on where to download them from:

http://powerpivotpro.com/2009/11/18/powerpivot-ctp3-beta-download-links/

All you need are the first two items, Office 2010 Beta 2 and PowerPivot for Excel 2010 to reproduce what I did in the demo I presented.

Next you’ll need some data. I had a SQL Server instance installed and used the AdventureWorks2008 LT database available at CodePlex. The LT version is the light weight version of the full AdventureWorks sample, I chose it for it’s simplicity.

http://msftdbprodsamples.codeplex.com/

If you don’t have SQL Server on your box you can use alternate data, or check with your friendly neighborhood DBA. Many have AdventureWorks installed on a test box and might be able to help you out.

OK, so to get this far you’ve downloaded and installed everything, and have access to a dataset. Ready? Let’s go.

1. Launch Excel 201, and click the PowerPivot tab.

2. Click the PowerPivot window button.

step01

3. Click the “From Database” button on the Home tab. In the menu select “From SQL Server”

step02

4. The Table Import Wizard will appear. In “Server name” field, click the drop down and pick your database server. Leave it set to the “Use Windows Authentication” (unless told otherwise if you are using a database provided by a DBA). Under Database name, pick AdventureWorksLT2008. It’s probably a good idea to click the Test Connection button just to make sure everything is correct. If so press Next to continue.

step03

5. On the next screen, leave it at the default of “Select from a list of tables and views to choose the data to import” and click Next.

6. On the next screen pick these tables: SalesOrderDetail, SalesOrderHeader, Product, Product Category, Address, Customer, CustomerAddress. Note the “Preview & Filter” button. In the future you may wish to experiment with this to filter the data you are importing, but it’s not needed for this demo. Click Finish to move on.

step04

7. On the next screen you’ll see it importing the tables. When it gives you the Success message click Close.

8. (Note, this next step differs just slightly from the live presentation, in it I added a column but did it in a different place.) Click on the SalesOrderDetail tab. Click on a blank cell under the Add Column out to the right of the data.

9. In this example we’ll decide that 80 percent of our Line Total is cost, which means the other 20% is profit. We’ll calculate the profit by clicking in the fx bar and typing =[LineTotal]*.20

step05

10. Right click on the “CalculatedColumn1” column header and select “Rename column” from the menu. The column header will highlight and allow you to type over it. Change the name to Profit.

11. Click on PivotTable and pick “Single Pivot Table” in the drop down menu.

step06

12. In a pop up dialog, it will ask if you want a new or existing worksheet. Pick “New worksheet” and click OK.

13. Over on the right you will see the “Gemini Task Pane”. Gemini is the original code name for PowerPivot. To create a basic PowerPivot pivot table, follow these steps.

14. Under SalesOrderDetail, click on then drag the LineTotal and Profit fields to the Values area.

15. Under ProductCategory, drag the Name down to Row Labels.

16. Under Product drag the Name to the Row Labels area.

17. Under the Address, click and drag the CountryRegion, then StateProvince to the ColumnLabels area.

18. Drag the StateProvince to the Slicers Vertical area.

19. Your Gemini Task Pane should look something like:

step07

20. Your spreadsheet should look something like:

step08

At this point you have completed all of the steps as shown in the presentation. Now start playing. You can jump back to the PowerPivot data sheets by clicking the “PowerPivot window” button as shown in step 1. Back there you might try experimenting with a chart, or on the pivot table apply different effects and slicers.

Introducing Microsoft PowerPivot

What is PowerPivot? Well according to Microsoft:

“PowerPivot is Microsoft Self-Service Business Intelligence”

I can see from the glazed looks you are giving your monitor that was clear as mud. So let’s step back a bit and first define what exactly is Business Intelligence.

Business Intelligence

Business Intelligence, often referred to as simply “BI”, is all about taking data you already have and making sense of it. Being able to take that information and turn it from a raw jumble of individual facts and transform it into knowledge that you can take informed actions on.

In every organization there is already someone who is doing BI, although they may not realize it. Microsoft (and many IT departments) refer to this person as “that guy”. A power user, who grabs data from anyplace he (or she) can get it, then uses tools like Excel or Access to slice it, dice it, and analyze it. This person might be an actual Business Analyst, but more often it’s someone for who BI is not their main job. Some common examples of people doing their own BI today are production managers, accountants, engineers, or sales managers, all who need information to better do their job. Let’s look at an illustration that will make it a bit clearer.

In this example, put yourself in the role of a sales manager. You have gotten IT to extract all of your sales orders for the last several years into an Excel spreadsheet. In order to determine how well your sales people are doing, you need to measure their performance. You’ve decided that the amount sold will be a good measure, and use Excel to give you totals.

IntroEx01

In BI terms, the column “Total Sales” is known as a measure, or sometimes a fact, as it measures something, in this case the sales amount. The grand total sales amount is often called an aggregation, as it totals up the individual rows of data that IT gave us. But now you might be wondering why Andy’s sales are so low? Well, now you want to dig deeper and look at sales by year.

IntroEx02

In BI terms, the names of the sales people are a dimension. Dimensions are often either a “who” (who sold stuff) or a “what” (what stuff did we sell). Places (where was it sold) and dates (when was it sold) are also common dimensions. In this case the sales dates across the top (2007, 2008, 2009) are a date dimension. When we use two or more dimensions to look at our measures, we have a pivot table.

Now we can see a picture emerging. It’s obvious that Andy must have been hired as a new salesperson in late 2008, since he shows no sales for 2007 and very small amount in 2008. But for Paul and Kimberly we can look at something called trends in the BI world. Kimberly shows a nice even trend, rising slowly over the last three years and earns a gold star as our top performer.

By being able to drill down into our data, we spot another trend that was not readily obvious when just looking at the grand totals. Paul has been trending downward so fast the speed of light looks slow. Clearly then we now have information to take action on, commonly known as actionable intelligence.

So remind me, why do we need PowerPivot?

As you can see in the above example, “that guy” in your company clearly has a need to look at this data in order to do his job. Not only does he need to review it, he also has the issue of how to share this information with his co-workers. Unfortunately in the past the tools available to “that guy” have had some drawbacks. The two main tools used by our analyst have been either Excel, or a complete BI solution involving a data warehouse and SQL Server Analysis Services.

Excel’s main limitations center around the volume of data needed to do good analysis. Excel has limits to the number of rows it can store, and for large datasets a spreadsheet can consume equally large amounts of disk space. This makes the spreadsheet difficult to share with coworkers. In addition mathematical functions like aggregations could be slow. On the good side, Excel is readily available to most workers, and a solution can be put together fairly quickly.

A full blown BI solution has some major benefits over the Excel solution. A data warehouse is created, and then SQL Server Analysis Services (often abbreviated as SSAS) is used to precalculate aggregations for every possible way an analyst might wish to look at them. The data is then very easy to share via tools like Excel and SQL Server Reporting Services. While very robust and powerful solution, it does have some drawbacks. It can take quite a bit of time to design, code, and implement both the data warehouse and the analysis services pieces of the solution. In addition it can also be expensive for IT to implement such a system.

Faster than a speeding bullet, more powerful than a locomotive, it’s PowerPivot!

PowerPivot combines the best of both worlds. In fact, it’s not one tool but two: PowerPivot for Microsoft Excel 2010, and PowerPivot for SharePoint 2010. What’s the difference you ask? Good question.

PowerPivot for Microsoft Excel 2010

PowerPivot acts as an Add-on for Excel 2010, and in many ways is quite revolutionary. First, it brings the full power of SQL Server Analysis Services right into Excel. All of the speed and power of SSAS is available right on your desktop. Second, it uses a compression technology that allows vast amounts of data to be saved in a minimal amount of space. Millions of rows of data can now be stored, sorted, and aggregated in a reasonable amount of disk space with great speed.

PowerPivot can draw its data from a wide variety of sources. As you might expect, it can pull from almost any database. Additionally it can draw data from news feeds, SQL Server Reporting Services, other Excel sheets, it can even be typed in manually if need be.

Another issue that often faces the business analyst is the freshness of the data. The information is only as good as the date it was last imported into Excel. Traditionally “that guy” only got extracts of the database as IT had time, since it was often a time consuming process. PowerPivot addresses this through its linked tables feature. PowerPivot will remember where your data came from, and with one simple button click can refresh the spreadsheet with the latest information.

Because PowerPivot sits inside Microsoft Excel, it not only can create basic pivot tables but has all the full featured functionality of Excel at its disposal. It can format pivot tables in a wide array of styles, create pivot charts and graphs, and combine these together into useful dashboards. Additionally PowerPivot has a rich set of mathematical functionally, combining the existing functions already in Excel with an additional set of functions called Data Analysis eXpressions or DAX.

PowerPivot for SharePoint 2010

PowerPivot for Excel 2010 clearly solves several issues around the issue of analysis. It allows users to quickly create spreadsheets, pivot tables, charts, and more in a compact amount of space. If you recall though, creation was only half of “that guys” problem. The other half was sharing his analysis with the rest of his organization. That’s where PowerPivot for SharePoint 2010 comes into play.

Placing a PowerPivot Excel workbook in SharePoint 2010 not only enables traditional file sharing, but also activates several additional features. First, the spreadsheet is hosted right in the web browser. Thus users who might not have made the transition to Excel 2010 can still use the PowerPivot created workbook, slicing and filtering the data to get the information they require.

Data can also be refreshed on an automated, scheduled basis. This ensures the data is always up to date when doing analysis. Dashboards can also be created from the contents of a worksheet and displayed in SharePoint. Finally these PowerPivot created worksheets can be used as data sources for such tools as SQL Server Reporting Services.

Limitations

First, let me preface this by saying as of this writing all of the components are either in CTP (Community Technology Preview, a pre-beta) or Beta state. Thus there could be some changes between now and their final release next year.

To use the PowerPivot for Excel 2010 components, all you have to have is Excel 2010 and the PowerPivot add-in. If you want to share the workbook and get all the rich functionality SharePoint has to offer, you’ll have to have SharePoint 2010, running Excel Services and PowerPivot 2010 Services. You’ll also have to have SQL Server 2008 R2 Analysis Services running on the SharePoint 2010 box. Since you’ll have to have a SQL Server instance installed to support SharePoint this is not a huge limitation, especially since SSAS comes with SQL Server at no extra cost.

One thing I wish to make clear, SharePoint 2010 itself can run using any version of SQL Server from SQL Server 2005 on. It is the PowerPivot service that requires 2008 R2 Analysis Services.

One other important item to note: at some point the load upon the SharePoint 2010 server may grow too large if especially complex analysis is being done. Fortunately SharePoint 2010 ships with several tools that allow administrators to monitor the load and plan accordingly. At the point where the load is too big, it is a clear indication it’s time to transition from a PowerPivot solution to a full BI solution using a data warehouse and SQL Server Analysis Services.

What does PowerPivot mean for business users?

For business users, and especially “that guy”, it means complex analysis tools can be created in a short amount of time. Rich functionality makes it easier to spot trends and produce meaningful charts and graphs. It also means this information can be shared with others in the organization easily, without imposing large burdens on the corporate e-mail system or local file sharing mechanisms.

No longer will users be dependent on IT for their analysis, they will have the power to create everything they need on their own, truly bringing “self service BI” to fruition.

What does PowerPivot mean for Business Intelligence IT Pros?

The first reaction many BI developers have when hearing about PowerPivot is “oh no, this is going to put me out of a job!” Far from it, I firmly believe PowerPivot will create even more work for BI Professionals like myself.

As upper management grows to rely on the information provided by PowerPivot, they will also begin to understand the true value BI can bring to an organization. Selling a new BI solution into an organization where none currently exists can be difficult, as it can be hard to visualize how such a solution would work and the value it brings. PowerPivot allows BI functionality to be brought into an organization at a low development cost, proving the value of BI with minimal investment. Thus when there is a need to implement a larger, traditional BI project those same managers will be more forthcoming with the dollars.

Second, as users pull more and more data, they are going to want that data better organized than they will find in their current transactional business systems. This will in turn spur the need to create many new data warehouses. Likewise the IT department will also want data warehouses created, to reduce the load placed on those same transactional business systems.

I also foresee PowerPivot being used by BI Pros themselves to create solutions. The database structure of many transactional database systems can be difficult to understand even for experienced IT people, much less users. BI Pros can use PowerPivot to add a layer of abstraction between the database and the users, allowing business analysts to do their job without having to learn the complexity of a database system.

BI Pros can also use PowerPivot to implement quick turnaround solutions for customers, bringing more value for the customer’s dollar. When a BI Pro can prove him (or her) self by providing rich functionality in a short time frame it’s almost always the case they are brought back in for multiple engagements.

PowerPivot also provides great value to BI Pros who are employed full time in an enterprise organization. They can create solutions much quicker than before, freeing them up to do other valuable tasks. In addition PowerPivot solutions can provide a “stop gap” solution, pushing the date at which the organization needs to spend the dollars for a full blown BI solution and allowing IT to plan better.

Finally I see great value in PowerPivot as a prototyping tool for larger BI projects. Now users can see their data, interact with it, analyze it, and ensure the required measures and dimensions are present before proceeding with the larger project.

I’ll reiterate, if anything I believe PowerPivot will create an explosion of work for the Business Intelligence Professional.

Where can I learn more?

Well right here for one. I have become quite interested in PowerPivot since seeing it at the SQL PASS 2009 Summit. I think it will be a valuable tool for both myself and my customers. This will be the first of many blog posts to come on PowerPivot. I am also beginning a series of presentations on PowerPivot for local user groups and code camp events. The first will be Saturday, November 21st 2009 at the SharePoint Saturday in Birmingham Alabama, but there will be many more to come. (If you’d like me to come speak at your group just shoot me an e-mail and we’ll see what we can arrange.)

There’s also the PowerPivot site itself:

I’ve also found a small handful of blogs on PowerPivot, listed in no particular order:

Summary

Thanks for sticking with me, I know this was a rather long blog post but PowerPivot has a lot of rich functionality to offer. While PowerPivot is still in the CTP/Beta stage as of this writing, I see more and more interest in the community, which will continue to grow as PowerPivot moves closer to release. I hope this post has set you off on the right step and you’ll continue to come back for more information.

Follow

Get every new post delivered to your Inbox.

Join 100 other followers