Instant Creating Data Models with PowerPivot How-to

Click on the Download PowerPivot button on the main page of http://www.PowerPivot.com.

Click on the Instructions link on the left-hand side of the page under the Quick Links tab. Follow the instructions to install .NET Framework 4.0 and Visual Studio 2010 Tools for Office Runtime.

Go to the top of the page and download the PowerPivot for Excel accordingly. x86 should be installed if the Excel is 32-bit, and x64 should be installed for a 64-bit Excel. The version of Excel 2010 can be checked by checking the About Microsoft Excel section under the File | Help menu as seen in the following screenshot:

After the installation is completed, the features of PowerPivot will be accessible under the new PowerPivot ribbon as highlighted in the following screenshot:

Ensure that it operates properly by clicking on the PowerPivot Window button as highlighted in the previous screenshot. It will open up a new window where we can import, organize, analyze, and publish data from various sources as shown in the following screenshot. This is the tool that we will be using throughout all the tasks.

Now we are fully equipped to use the Microsoft PowerPivot add-in for Excel 2010 – the business intelligence tool.

Download and install the small version of Microsoft SQL Server 2008 R2 SP2 – Express with Tools (SQLEXPRWT_x64_ENU.exe or SQLEXPRWT_x86_ENU.exe), which is sufficient for our purpose. It can be downloaded from http://www.microsoft.com/en-ca/download/details.aspx?id=30438.

Download the sample database AdventureWorks by selecting AdventureWorks2008R2 Data File at http://msftdbprodsamples.codeplex.com/releases/view/59211.

Open SQL Server Management Studio as an administrator. Click on Attach... as shown in the following screenshot:

In the next screen, select Log in the database details section and click on Remove as shown in the following screenshot. This will remove the logs. Now you can press the OK button.

Once the sample database has successfully been installed, it will be visible on the left-hand side pane of the SQL Server Management Studio. After confirming, open Excel 2010 with PowerPivot.

In Excel 2010, open up the PowerPivot window. Select From Database | From SQL Server. Select the details as shown in the following screenshot. If the server name is not available, type in PC-NAME\SQLEXPRESS.

Then, select 13 tables with Person in the Schema column and click on Finish. It will import the tables into PowerPivot.

The web page http://data.worldbank.org/indicator/NY.GDP.PCAP.PP.CD contains historical GDP for each nation in the world, which can be simply downloaded as an Excel file. Open up the new Excel file using PowerPivot as shown in the following screenshot:

Select both the tables, and check the resulting sheet. Save as GDPData.xlsx for later usage.

Similarly, download the unemployment rate data as an XLS (Excel) file from http://www.oecd-ilibrary.org/employment/employment-and-labour-markets-key-tables-from-oecd_20752342, which contains the historical unemployment rate of each OECD nation. This will not import to PowerPivot directly as the file is in the .xls format, but is based on another format (XML). Open it in Excel and save as a CSV file.

Use PowerPivot to import the CSV file and save as UnemploymentData.csv.

In Excel 2010, open up the PowerPivot window. Select From Database | From SQL Server. Select the details as shown in the following screenshot. If the server name is not available, then type in PC-NAME\SQLEXPRESS.

However, this time, click the Preview & Filter button as highlighted in the following screenshot for each of the 13 tables.

It will display a preview of every table that we are importing—and we haven't explored what each column means. However, one easy thing is to remove logs and other items that may be necessary for the database, but not for us. This may include logs, last modified date, last user modified, and so on. For the first table Address, deselect the last two columns rowguid and ModifiedDate as shown in the following screenshot:

After deselecting, click on OK and we will see that new comment, Applied filters, will appear for the table under the column Filter Details, as shown in the screenshot in step 2. Repeat this procedure for every table, deselecting the data that we are sure will not be useful. Save the final datafile as DatabaseData_v2.xlsx. The file should be about 5 MB, around 11 to 12 MB smaller than the original one.

Similarly, repeat the step for the GDP datafile by deselecting the columns from 1970 to 1999. Import and then save the final data as GDPData_v2.xlsx. This filtered data should be about 375 KB, less than half of the size of the original file that would be 848 KB.

Open the PowerPivot window of the DatabaseData_v2.xlsx file with its 13 tables.

Click on the PivotTable button near the middle of the top row and save as New Worksheet.

Select the checkboxes as shown in the following screenshot:

Now, this shows the number of clients per city and per country. However, it is very difficult to navigate, as each country name has to be collapsed in order to see the next country.

Let us move the CountryRegion | Name column to Slicers Vertical. Now, the PowerPivot Field List dashboard should appear as shown in the following screenshot:

Now, the pivot table should display simple results: the number of clients in a region, filterable by the country using slicers.

Let us apply some formatting to allow for a better understanding of the data.

Right-click on Name under the Slicers Vertical area of the PowerPivot Field List dashboard. Select Field Settings, then change the name to Country Name. We now see that the title of the slicer has changed from Name to Country Name, allowing anyone who views this data to understand better what the data represents.

Similarly, right-click on Count of AddressLine1 under Values, select Edit Measure, and then change its name to Number of Clients. Also change the data title City under the Row Labels area to City Name. The result should appear as shown in the following screenshot:

Let's see our results change as we click on different country names. We can filter for multiple countries by holding the Ctrl key while clicking, and can remove all filters by clicking the small button on the top-right of slicers. This is definitely easier to navigate through and to understand compared to what we did at first without using slicers, which is how it would appear in Excel 2010 without PowerPivot.

However, this table is still too big. Clicking on Canada gives too many cities whose names many of us have not heard about before. Let us break the data further down by including states/provinces.

Select StateProvince | Name and move it under Slicers Horizontal and change its title to State Name. It is a good thing that we are renaming these as we go along. Otherwise, there would have been two datasets called Name, and anyone would be confused as we moved along.

Now, we should see the state names filter on the top, the country name filter on the left, and a list of cities with the number of clients in the middle part. This, however, is kind of awkward. Let us rearrange the filters by having the largest filter (country) at the top and the sub-filter. (state) on the left-hand side This can be done simply by dragging the Country Name dataset to Slicers Horizontal and State Name to Slicers Vertical. After moving the slicers around a bit, the result should appear as shown in the following screenshot:

Again, play with the results and try to understand the features: try filtering by a country—and by a state/province—now there are limited numbers of cities shown for each country and each state/province, making it easier to see the list of cities.

However, for countries such as the United States, there are just too many states. Let us change the formatting of the vertical filter to display three states per line, so it is easier to find the state we are looking for. This can be done by right-clicking on the vertical filter, selecting Size and Properties | Position and Layout, and then by changing the Number of Columns value.

Repeat the same step for Country Name to display six columns and then change the sizes of the filters to look more selectable. Change the name of the sheet as PivotTable and then save the file as DatabaseData_v3.xlsx.

Open the PowerPivot window of the GDPData_v2.xlsx file with its two tables.

Click on the PivotTable button near the middle of the top row and save as New Worksheet.

Move the dataset from the year 2000 to the year 2010 to the Value field, and move Country Name in the Row Labels field, and Country Name again into the Slicers Horizontal field. In the slicer, select five countries: Canada, China, Korea, Japan, and United States as shown in the following screenshot:

Select all fields and reduce the number of its decimal places. We can now clearly see that GDP in China has tripled over the decade, and that only China and Korea saw an increase in GDP from 2008 to 2009 while the GDP of other nations dropped due to the 2008 financial crisis.

Knowing the relevant background information of world finance events, we can make intelligent analysis such as which markets to invest in if we are worried about another financial crisis taking place.

As the data get larger in size, looking at the GDP number becomes increasingly difficult. In such cases, we can switch the type of data displayed by using available buttons in the PivotTable Tools | Options menu, the Show Value As button. Play around with it and see how it works: % of Column Total shows each GDP as a percentage of the year, while % Different From allows the user to set one value as the standard and compare the rest to it, and the Rank Largest to Smallest option simply shows the ranking based on which country earns the most GDP.

Change the name of the sheet as PivotTable and then save the file as GDPData_v3.xlsx.

Open the DatabaseData_v3.xlsx file again. Open the PowerPivot window and click on PivotChart (instead of the PivotTable button), located as a drop-down menu of the PivotTable button. Save it as New Worksheet.

Then, rename each field accordingly (Country Name, State Name, and City Name), copy the highlighted chart, and paste it below as shown in the following screenshot:

Change the chart type of the bottom chart to a pie chart.

From here, there are three different types of reports that we can easily create.

Place CountryRegion | Name under the Axis Fields area, and now we have a chart of number of clients per country, represented both as a bar graph and as a pie graph as shown in the following screenshot:

Similar steps can be repeated for state/province and for city. Of course, because it will list all states for all the countries, the charts will be very disorganized. Play around with it and filter it to select only one country. For instance, Germany will appear as shown in the following screenshot:

Once we are finished with changing chart types, filters, and other ways to create charts to represent the data, rename the sheet as PivotChart and save it and close.

Certain chart types are more effective than others in representing a specific type of data. We have created a chart from the beginning here, adding and removing datasets just like the pivot table.

Open the GDPData_v3.xlsx file again. Click on the pivot table.

Under the PowerPivot Tools | PivotChart option select a chart type. We will create a column chart first, then copy the chart, and change the second chart type to Doughnut. Change the doughnut's chart layout to Layout 3 and the result will be as shown in the following screenshot:

The column chart shows the generally increasing GDP per nation, but the numbers for China are very small and hard to compare. The doughnut chart is more effective in showing the exponentially increasing GDP of China, but is still difficult.

For each of the Sum of … column, change the Show Value As button as % of… with China as its base. This will display all other GDP as a percentage of China. The resulting bar graph shows that the GDP of China showed great growth year after year compared to other countries. It would appear as follows:

Make a copy of the DatabaseData_v3.xlsx file and save as DatabaseData_v3_test.xlsx. Copy the sheet PivotTable and rename the sheet as Relationships.

Firstly, click on the PowerPivot Window tab and then under the Design tab, click on Manage Relationships and remove all relationships. Return to Excel, refresh, and confirm that the results are all out of place as they are not related to one another anymore. Excel will have a Create button that says "Relationships may be needed" in the PowerPivot Field List dashboard. However, instead of having it automatically generate data, we will manually reproduce it.

First, start by studying where the data are. Number of clients is from the BusinessEntityAddress | BusinessEntityID column, Country Name is from the CountryRegion | Name column, State Name is from StateProvince | Name, and City Name is from Address | City. Then, we can observe that there are common data between the tables, which is how they are linked. The same key act as a link in saying that these data are related.

Observe the following connections:

As evidenced, one data links to another table, whose value links to another table.

Then, create the relationships in the mentioned order. For instance, the first relationship created would be as follows:

After creating the third relationship, return to Excel, refresh, and check the results. Congratulations! We have managed to link all the relevant data together to reproduce our previous settings.

Now, create two more relationships:

We have successfully linked the phone number of each client, and then linked the phone type of each phone number. Return to Excel, refresh, and add PhoneNumberType | Name into Slicers Horizontal. The result would appear as shown in the following screenshot:

We can now sort the clients by their phone number types, and can reach conclusions such as that no one from Australia, France, and Germany has provided their work phone numbers, and that some of the phone numbers are not categorized under Home, Work, or Cell.

If we were to produce the same pivot table in the original DatabaseData_v3.xlsx file, the phone number types will not show in the pivot table, and PowerPivot will not be able to detect it automatically because its relationship has been set up in another manner.

The table SalesOrderHeader contains the OrderDate, DueDate, ShipDate, OnlineOrderFlag, ShipMethodID, and TerritoryID columns. These are the relevant data if we are interested in analyzing the shipping time based on: whether the order was an online order or not, which shipping method was chosen, and which area it was delivered to. We can also analyze whether the shipping period met the due date deadline or not.

Create a pivot table in the order you think is necessary to analyze the shipping time for each order. For instance, it would be:

However, this does not give us what we want. It only gives us the order date and number of orders that have that order date (Count of ShipDate and Count of DueDate), which is filterable by the ShipMethodID and OnlineOrderFlag columns, and the destination region.

What we want is the duration it took to ship, and whether it met the due date. Because we can't manually type data inside the pivot table, there is no way to know both of these. This is where it is crucial to add custom columns manually.

Return to PowerPivot. Under the Design tab, click on the Add button to add a new column to calculate the number of days it took to ship from the moment of order. Type in the formula as follows:

= [ShipDate] – [OrderDate]

It will create a column called CalculatedColumn1 showing the bunch of 1900/01/06 0:00:00. Let us change the format to numbers so that it may represent the number of days between order and shipping as shown. Also, change the name of the column to ShippingDays.

Then, because we want to see if it shipped before the due date, create another custom column with the following formula:

= [ShipDate] <= [DueDate]

This column will return a Boolean (True or False) as to whether the shipped date is equal to or less than (before) the due date. It will return True if it was shipped on or before the due date, and will return False if it was shipped past the due date. Change the column name to DeliveredOnTime.

Return to the pivot table and refresh. Remove all three datasets from Row Labels and Values. Add ShippingDays to Row Labels and Slicers Vertical, add DeliveredOnTime to Slicers Vertical, and add SalesOrderID to Values and change it to Count of SalesOrderID. The pivot table should appear as follows:

Now, we have what we want: number of days it took to ship (7 or 8) and number of orders for each (31456 orders that took 7 days to ship, and 9 orders that took 8 days to ship). By filtering the data using our slicers (ShipMethodID, OnlineOrderFlag, ShippingDays, DeliveredOnTime, and RegionName), we are able to make a number of conclusions such as:

This is good! We are getting used to modifying the data by adding custom columns to give us what we want—and to make conclusions based on those data.

Save the sheet as DeliveryTime01 and save the file.

Copy the sheet DeliveryTime01 and rename as DeliveryTime02.

Now we want to expand on this to see the per-month, per-season, and per-year result of number of orders. However, a quick look at the PowerPivot table suggests that the closest data we have is the order date column, which gives date format in a number not orderable by year, by season, or by month. Well then, it's time for custom columns.

Return to the PowerPivot table SalesOrderHeader and add three custom columns with the following formulas and rename them as Year, Month, and Season respectively. It should appear as follows.

=year([OrderDate])
=month([OrderDate])
=if(or([Month]<3, [Month]=12), "Winter", if([Month]<6, "Spring", if([Month]<9, "Summer", "Fall")))

The previous three lines of code are shown in columns Year, Month, and Season as shown in the following screenshot:

Return to the pivot table, and let us reformat the slicers. From Slicers Vertical, since all orders were delivered on time, remove DeliveredOnTime. Since all online orders are shipped using the ship method ID 1 and all non-online orders are shipped using the ship method ID 5, remove ShipMethodID. Then, in Slicers Horizontal, add Year, Month, and Season.

Well that was relatively easy, wasn't it?

Our analysis shows that the deliveries took longer than usual (8 days instead of 7) only during the year of 2008 in February, March, April, and May, for Germany only. We also observe that orders were relatively well distributed over the months as well as the season. The number of orders has been steadily increasing since 2005, so that's good.

One thing rather troublesome in our analysis is that the regions of U.S. are treated separately. Let us fix that and have all the U.S. regions as United States. Since the RegionName value comes from the table SalesTerritory, let's re-open PowerPivot and check the table.

Oh! It seems that there is already a column called CountryRegionCode that shows the country, instead of the region! Let us use this. Return to the pivot table and replace the filter Name with SalesTerritory | CountryRegionCode. We have just saved unnecessary work by finding another column that serves our purpose exactly. It is worthwhile to note that the U.S. only accounts for about 40 percent of our sale, meaning that we are a very international company with much room for growth.

Let's save the file for now.

Copy over the DeliveryTime02 sheet, and rename as DeliveryTime03.

In treating orders, the number of orders is important. However, what is more important is how much we made from those orders! We want to expand our table even further to see our revenue made from these orders per month. Let us rearrange the data:

Now we have a per month, per year figure of sales, tax, freight fees, and total charged on orders. An analysis could be made that while the number of orders is relatively stable throughout the year (based on the previous sheets), the amount of an order is generally lower in January and higher during the months of June to August. The absolute $ amount helps us to understand our businesses better.

However, the sheer numbers may be difficult to see at times. Create a doughnut-style pivot chart and click on Switch Row/Column. As we filter through the data using slicers, the pivot chart makes it easier for us to observe trends. For instance, the tax rates for the United Kingdom and France increased around the middle of 2006; the tax rate for Germany increased around the middle of 2007.

We can further modify the data for our analysis by changing the data type from Sum to Count, Min, Max, Average, and DistinctCount as shown in the following screenshot:

Each will give a unique result: total order dollar amount, total number of orders, cheapest order, most expensive order, average of order, and number of different amounts of order, all on a monthly basis. Modify the fields as you will and try to comprehend the results.

Save the file.

Copy over the sheet DeliveryTime03 and rename as DeliveryTime04.

To further our analysis, we want to look at which product sold well over the years. Looking at the PowerPivot tables, we know that the detail of each order is stored in the SalesOrderDetail table, with ProductID, UnitPrice, LineTotal, and so on. Based on our experience so far, it is logical that ProductID links the SalesOrderDetail table to another Product table that we don't have right now; let us import that table as well so that we know which product ID corresponds to which product.

Follow the same steps for importing data from the database. Looking through the tables, we find that the table Product contains both the ProductID and Name columns, which states what the product is for each ProductID. Since we are only interested in the names of the products, in Preview & Filter, deselect all columns and select only the ProductID and Name columns for import, as shown in the following screenshot:

Return to the pivot table/chart. Click on the pivot chart and then click on Switch Row/Column Data to have the year and months as the rows, and SubTotal, TaxAmt, and Freight as the columns. Then update the PowerPivot Field List dashboard as follows:

In the pivot table, we now have the number of orders and dollar amount of those orders on a monthly basis, filterable by products, year, month, season, country, online order flag, and shipping days. This data is relatively easy to comprehend based on the pivot table. For instance, we can easily see that the product All-purpose Bike Stand was first ordered in July 2007 and has been selling at a rate of roughly 20/month. If we analyze even further, we may be able to find which products are popular for each region.

Due to the large number of products, the pivot chart is not useful for this purpose. Similar to CountryRegionCode, if we can find some data or create another custom column to sort products by other criteria such as accessories, bike gear, and clothes, then the pivot table may be useful in helping us comprehend the data quicker.

Save and close the file.

Can we present this to our boss? No, of course not. The pivot chart does not mean anything, there is a field called Row Labels, and it just does not look presentable in any way or form. This is no different than just providing the raw database's data and telling someone to interpret it, not to our standard.

Then let's start re-packaging our results. Firstly, let us name items by changing the Row Labels value to Year/Month as shown in the following screenshot:

Then, also change the names of slicers as follows:

Click on the pivot chart. Under the Design tab, click on Switch Row/Column. Now our pivot table will be represented horizontally. However, this is too long! Move the Year dataset from Column Labels to Row Labels.

Oh! As we have changed our row, Row Labels and Column Labels appeared again. Rename as Year and Month respectively. Also change the Sum of OrderQty and Sum of LineTotal options to Order Quantity and Order Amount ($) respectively.

Create an additional pivot chart (copy over the previous one). Set the first chart type as doughnut, and the second chart type as bar graph. Place underneath the pivot table.

Above the pivot table, extend the horizontal slicers to match the size of the pivot table as well. Rearrange as necessary. It may sound feasible to remove the Month slicer because we can easily compare the monthly results year over year, but we will not remove it because it helps us to filter our graphs, which would otherwise show all 12 months at the same time.

Since we can see the direct numbers from the pivot table, we don't need exact numbers in the pivot charts. Instead, the pivot chart should be used to observe trends. Thus, we will modify our bar graph, which doesn't show us much information other than the Order Amount ($) values due to the sheer largeness of the order amount in comparison to the order quantity.

Double-click on vertical axis, and under Axis Options, check the Logarithmic Scale option. Then manually specify the Minimum value as 100. Now we have represented the values in logs of 10. The result should be as shown in the following screenshot:

Even though we may not have specific values, it is so much easier to observe the trends—whether the sales have increased or decreased over the months. We are able to observe in more detail if we use the horizontal slicers to filter using per product, or per year. And if we need specific figures, we can always refer to the preceding pivot table.

For instance, by filtering on yearly basis, we can clearly observe that the increase in number of products sold signifies greater sales revenue. Although it may sound obvious that the more products we sell, the more money we make, it is not necessarily the case as 2007 December appears to have sold fewer products than 2007 August, but has higher revenue ($ amount sold). Referring to the pivot table, we can observe that it is indeed true: December sold about 3,000 fewer products, but generated about $200,000 more than August.

We also notice in the bar graph that the online orders have increased sharply in the middle of 2007, and that orders have been increasing ever since. By filtering based on Months, the doughnut graph makes it easy to view year-over-year changes in the orders.

We can filter through more, but the format is not good as the pivot table seems to resize based on which slicers we use. Rearrange the orders to slicers, pivot charts, and then pivot tables. The slicer also has many criteria, and we can remove the overlapping ones. In this case, that would be the seasons, as we can manually filter by selecting the months, instead of the seasons (that is, clicking on 1, 2, and 12 using the Month slicer will give the same result as clicking on the Winter slicer). Thus, remove the Seasons filter.

Also remove Shipment Duration (days) as we are not interested anymore in few outliers, which took one day more to ship. That leaves more space for the Product Name slicer, enabling us to scroll through faster.

Now, since our pivot table, chart, and slicers look good, it's time to make the final arrangements using Excel.

Under the third tab, Page Layout, uncheck the View button of the Gridlines button. It removed all gridlines in the background, making it look more clean and crisp. Also change the color of the sheet to red, highlighting that this is the final, most relevant sheet. The final product should appear as shown in the following screenshot:

In short, the final preparation step for creating a presentation-worthy PowerPivot is as follows:

First, since all our data is stored in PowerPivot and not in other sheets, we can simply remove all sheets except the final sheet Result. This alone should save about 0.1 MB.

Then, going through all the data tables we have as shown under the PowerPivot Field List dashboard, we notice many of the data tables are not used at all. For instance, the table CreditCard contains none of the information we are using nor contains any significant relationships that we should not remove in order to maintain our data's sanity. Then, logically, let's delete the table.

Open up PowerPivot. We notice sheets such as CreditCard contains about 19,000 rows. Let's delete the whole table by right-clicking on the sheet name and selecting Delete. Luckily for us, the data used in our analysis came from few major tables only so we probably will not make a mistake of removing a crucial table by accident.

Delete all data tables (sheets) except for SalesOrderHeader, SalesOrderDetail, SalesReason, SalesTerritory, and Product. If you are unsure at any point whether the table is used, the simplest way to check is to go back to the pivot table/chart and see which fields are being used (checked) under the PowerPivot Field List dashboard. The file should be about 7.0 MB after deletion.

Even though we managed to delete the unused data tables, the file is still very large for such a basic analysis. If we were to perform a more extensive analysis, no doubt the file will be tens of megabytes. Thus, we will optimize our file further by removing unused columns as well, just like how we had filtered out the date log column when we first imported the data.

In the table SalesOrderHeader, remove the following columns: Revision, Status, SalesOrderNumber, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, BilltoAddressID, ShipToAddressID, CreditCardID, CreditCardApprovalCode, and CurrencyRateID. This saves an additional 2.6 MB, for a file size of 4.4 MB.

In the table SalesOrderDetail, remove the following columns: SalesOrderDetailID and CarrierTrackingNumber. This saves an additional 1.8 MB, for a file size of 2.6 MB.

This is good. We have managed to still maintain all critical data used in our analysis and have shrunk the file size to about 25 percent of the original by simply removing unused tables and columns. 2.5 MB is small enough to be sent over e-mail easily! Save as Excel and close. It will be readable by anyone with access to Excel 2010.