Showing posts with label OData. Show all posts
Showing posts with label OData. Show all posts

Thursday, February 16, 2012

Analytics using PowerPivot and OData

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 .

I will start with a new Excel Spreadsheet and launch PowerPivot. In PowerPivot, click on “From other data sources” and click on “Other Feeds”.

image

Click Next and enter the Data feed URL: http://www.pluralsight-training.net/odata/

image

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:

image

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:

image

image

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:

image

Here is the view: note that there is no relationship that currently exists. This is what we will work on next.

image

To create a relationship between Modules and Authors, right click on Modules and choose “Create Relationship” and click “Create”.

image

image

This will create the relationship between Modules and Authors using the Author/AuthorName.

image

Similarly, I can go ahead and create the relationship amongst all the entities as seen below:

image

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:

image

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:

image

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])

image

The next step is to create the relationship which can be created as follows:

image

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

image

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)

image

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)

image

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:

image

Let’s take a few minutes to beautify the table and the chart:

Table: Highlight the table and choose the table format you like:

image

Chart: Highlight the chart, go to Design and apply the template you like:

image

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.

image

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!