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.

Follow

Get every new post delivered to your Inbox.

Join 104 other followers