With PowerPivot you can quickly extract data from a wide variety of data sources and built impressive looking reports right within Excel. In this post, I will extract data from an OData feed made available by Pluralsight training website.
I am on Windows 7 platform with Excel 2010 and PowerPivot.
I will start with a new Excel Spreadsheet and launch PowerPivot. In PowerPivot, click on “From other data sources” and click on “Other Feeds”.
Click Next and enter the Data feed URL: http://www.pluralsight-training.net/odata/
Here is a list of all OData producers: http://www.odata.org/producers
Click Next and it should come back with the source tables that are exposed in this feed:
You can give new friendly names to these source tables. This looks good to me, so I will go ahead and click Finish. This should extract the data into PowerPivot tables:
Take some time to explore and get familiar with data as the next step involves building a relationship.
Click on the Design View in PowerPivot:
Here is the view: note that there is no relationship that currently exists. This is what we will work on next.
To create a relationship between Modules and Authors, right click on Modules and choose “Create Relationship” and click “Create”.
This will create the relationship between Modules and Authors using the Author/AuthorName.
Similarly, I can go ahead and create the relationship amongst all the entities as seen below:
Next we need a Date Table to be our time dimension.
The Date Table can be built easily in Excel. Use a new sheet to generate date data. By exploring the data, I realized that data goes back to 2008. So, enter 1/1/2008 and drag the cell until you cover 2008, 2009, 2010, 2011 and 2012. This should give you about 1827 rows. Next create the formulas for Year, Month, Quarter and Month Name:
Year: =YEAR(A2)
Month:=MONTH(A2)
Quarter: =IF(C2<4, "Q1", IF(C2<7, "Q2", IF(C2<10, "Q3", "Q4")))
Month Name: =IF(C2=1, "Jan", IF(C2=2, "Feb", IF(C2=3, "Mar", IF(C2=4, "Apr", IF(C2=5, "May", IF(C2=6, "Jun", IF(C2=7, "Jul", IF(C2=8, "Aug", IF(C2=9, "Sep", IF(C2=10, "Oct", IF(C2=11, "Nov", IF(C2=12, "Dec"))))))))))))
Here is a partial screenshot:
Now, you need this as a Date Table in PowerPivot. There are two options to do this: (1) Click “Create Link Table” under the PowerPivot ribbon item in Excel (2) Copy and Paste directly into PowerPivot.
Here is the Date table in PowerPivot:
Before we setup the relationship between the Date table and Modules, we need to fix the PublishDate column in Modules because it contains the Date and Time components in it. In order for our join with Date table to work, we only need PublishDate. Rename PublishDate to PublishDatetime and create a new column PublishDate in Modules table with this formula: =DATEVALUE([PublishDateTime])
The next step is to create the relationship which can be created as follows:
Another thing to do is to create a Date hierarchy as follows:
Right Click>Create Hierarchy
Give a name to the hierarchy
Drag and drop the levels of the hierarchy
Almost there with the data model, we just need to create a couple of new measures: (1) Total Modules (2) Total Courses. You could possibly create more measures with the data we have, but we are good for now.
Switch to Data View in PowerPivot and go to Modules table. Create a new measure as follows: Total Modules:=COUNTROWS(Modules)
Next, In the Courses table: Create Total Courses:=COUNTROWS(Courses)
With that we are now ready to create our reports.
Create a new PivotTable>Chart and Table (Horizontal)
You get a blank slate. You can now start to drag and drop Objects from the Pivot Field List:
Drag and Drop the Course attributes as follows:
Table:
Row Labels: Category, Title
Slicer Vertical: Category
Values: Total Courses
Chart:
Axis Fields: Category
Values: Total Courses
Here is the view I get:
Let’s take a few minutes to beautify the table and the chart:
Table: Highlight the table and choose the table format you like:
Chart: Highlight the chart, go to Design and apply the template you like:
You can hide the Axis Field Buttons on the Chart since we already have a slicer setup which is a much better way of slicing-dicing the data.
That’s it. Feel free to explore with Pivot Tables and Chart and create reports for the other Entities in PowerPivot database.
Here is the final Spreadsheet that I ended up creating: https://skydrive.live.com/redir.aspx?cid=d3aff4f279dff93d&resid=D3AFF4F279DFF93D!703&parid=D3AFF4F279DFF93D!701
Good Luck!
