PowerPivot – 2

By Stuart Box


If you've been following these blog posts, you'll have PowerPivot installed in Excel (click here to read PowerPivot 1). Now we'll start to get some data into PowerPivot, and as you'll see this is one of the most powerful ways you can join together disparate data. Once PowerPivot is installed, click on the PowerPivot Window Icon on the Excel ribbon. This opens the PowerPivot client window, a separate window that controls PowerPivots's access to data. From here you can import data into PowerPivot, as you can see from the ribbon you have a wide variety of data sources to pick from. Of course, you can get data from other Excel files, but also Access, SQL and Oracle databases, text files, ODBC connections and the Azure Datamarket. This means that if you have, like most of us, data spread around several different applications, maybe an Access database, and Excel tables, you can combine data from them on one Excel PowerPivot. That final option the Azure datamarket, is also significant. If you don't have the data you need, - maybe Weather data, Stocks information, Average House Prices, and so on, the Azure datamarket may have it. Some of the datamarket datasets are free, as the three I've mentioned and some are available for a fee. In this series I'll look at the simplest case of using data from other Excel spreadsheets, the concepts are the same as using data from any other database but we don't have to worry about connection to a database. To import data that's in the same Excel Workbook, format the data as an Excel Table. To do this, click in your data, and on the Home tab click on Format as table. Select any of the formatting options. Now, on the PowerPivot tab of the Excel ribbon, click on Create Linked Table. This will switch to the PowerPivot window with the data linked in. Repeat this for as many tables of data you have in your Excel spreadsheet. Each table will show up as a tab in the PowerPivot client window.

Creating Relationships.

Once the data is imported, we can set about the task of defining relationships between the tables in PowerPivot. This is really useful, as we can import a series of data tables and define the relationships between them, effectively creating a relational database. Of course, if you're importing from a relational database, (SQL, Oracle, Access etc.) that already has relationships defines these are automatically recognised by the PowerPivot Data Import Wizard. But in the instance where we've got data on separate Excel tables that we want to link together it's very useful. So, consider the following tables, Customers and WineSales. You'll see that in the WineSales table, each Customer is identified by their CUSTOMERID. In the Customers Table, we have the name for each customer alongside their ID. You can see that the CustomerID field in the Customers table is used in the WineSales table to link each customer to their wine sales.  Therefore the relationship would be created like this... In the PowerPivot window, on the Home tab, click on the Diagram View icon.  In the little windows that represent your tables, drag from one linking field and drop onto the same field in the linked table e.g. from CustomerID in the Winesales table to CustomerID in the Customers table.  PowerPivot will create a many-to-one relationship represented by the arrows between tables.  

Sorting and Filtering

You'll notice that the column headers on the powerpivot table view have drop-down icons just like Excel tables. This allows you to filter and sort the columns here in PowerPivot before we begin to create pivot tables or charts. Once you click on the drop down next to the column name you'll see a familiar sort and filter dialogue. Use this to restrict the data reported on by Excel. It's very handy for example if you just want to report and a particular area, department or type of wine. The PowerPivot window allows you to operate with large data sets very quickly. Common operations such as sorting and filtering typically complete in under a second on millions of rows of data. Now we have data tables linked into our PowerPivot, and have defined relationships and filtered or sorted the data, we are ready to create a Pivot table of the resulting data, and that's exactly what I'll be looking at in my next blog post.

Leave a Reply