Project: Gain insight into your music taste using Last.fm, PowerPivot and the PivotViewer

I love music, i listen to it a lot on my ipod and on my computer.I also love statistics, I send all the tracks I listen to the online music service last.fm where I have stored all the tracks i have played since 2006.

This is a list of my top artists (yes I am kind of a metal head):

Wouldn’t it be great if I could load all my played tracks into the PivotViewer? This blog posts describes how I got all my played track information into the PivotViewer. In a previous blog post is described how you we can make a PivotViewer application on top of PowerPivot. Today we are going to load data from Last.FM into PowerPivot and base a PivotViewer app on top of PowerPivot.

First thing we need to do is download the played tracks so we can load the data into PowerPivot. Last.FM has all kinds of developer services available, but i didn’t feel like building an app that did that for me. So while browsing the Internet i found an Perl application that collects Last.FM data into an XML file. It’s called lfmCOL.pl using this Perl script you can put the result of a api call from Last.FM into an XML file. In my case i used the library.getTracks api call. This will get a list of tracks with playcount, album,artist and track image. The api has got a build in max amount of rows that it will return. In my case i want to return all the tracks I ever played, luckily the perl script automatically will download all the tracks by using the build in paging algorithm.

All i had to do is call the following command:

perl lfmCOL.pl -xmlfile=MyLibrary.xml method=library.getTracks user=Sinistrad

This will download all my tracks in an XML file, I use XML marker as XML tool:

We now want to load the information into Excel, i didn’t know excel could read XML as datasource but it works astonishingly well.

In Excel you have two choices, use the XML table view or the load it as plain text into the existing worksheet. I chose to load is as plain data:

Each row is loaded 4 times because we have 4 different images, what I did to filter them out is create a table and filter out only the image I want:

Then copy and past all the rows into a new workbook. Next I loaded it into PowerPivot.  So now I had data into PowerPivot, but all I had was information about tracks. To get a better PivotViewer I wanted to have more information about a track. So i decided to get more information from the Last.FM services about the artist. I decided to write a Macro to get information from the webservice into Excel. I vowed a few years back that I never again write VBA code 🙂 But Rob’s latest macro blog post made me reconsider.

First I copied the artists from the artist column into a new worksheet and used the Excel remove duplicates function to create unique rows. Next i created a macro that called the Last.FM api artist.getInfo. Make sure you get a API key yourself so you can call the web service. I read every row in the excel sheet and pass that into the webservice. The webservice then returns XML we need to read information from. I was specifically interested in the tags. I used ActiveSheet.Range to determine which rows to pass to the webservice because of the Last.FM webservice webcalls, I have had some timeouts when looping through the entire workbook. After getting the data from the XML i put it into the Excel sheet.

Below is the macro i wrote (mind you this is in no way perfect code).

Sub LoadInfo()
Dim NumRows As Integer
Dim ThisRow As Integer
Dim rngSrc As Range
Dim artist As String
Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address)
NumRows = rngSrc.Rows.Count
ThisRow = rngSrc.Row
Dim j As Integer
For j = ThisRow To NumRows + ThisRow - 1 Step 1
    artist = Cells(j, 1)
    Call LoadRow(artist, j)
Next j
Set rngSrc = Nothing
End Sub
Function GetRawArtist(artist As String)
    Dim objHttp As Object
    Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
    Dim url As String
    url = "http://ws.audioscrobbler.com/2.0/?method=artist.getinfo&artist=" & artist & "&api_key=APIKEYHERE"
    Call objHttp.Open("GET", url, False)
    Call objHttp.Send("")
    GetRawArtist = objHttp.ResponseText
    Set objHttp = Nothing
End Function
Sub LoadRow(artist As String, rownumber As Integer)
    Dim XMLDOC As MSXML2.DOMDocument
    Set XMLDOC = CreateObject("Microsoft.XMLDOM")
    Dim Xml As String
    Xml = GetRawArtist(artist)
    If Len(Xml) > 0 Then
        XMLDOC.LoadXML (Xml)
        Dim image As String
        image = XMLDOC.SelectSingleNode("lfm/artist/image[@size='extralarge']").Text
        Cells(rownumber, 2) = image
        Dim listeners As String
        listeners = XMLDOC.SelectSingleNode("lfm/artist/stats/listeners").Text
        Cells(rownumber, 3) = listeners
        Dim nodelist As IXMLDOMNodeList
        Set nodelist = XMLDOC.SelectNodes("lfm/artist/tags/tag")
        If nodelist.Length > 0 Then
            Dim tag1 As String
            tag1 = nodelist.Item(0).FirstChild.Text
            Cells(rownumber, 4) = tag1
            Dim tag2 As String
            tag2 = nodelist.Item(1).FirstChild.Text
            Cells(rownumber, 5) = tag2
            Dim tag3 As String
            tag3 = nodelist.Item(2).FirstChild.Text
            Cells(rownumber, 6) = tag3
            Dim tag4 As String
            tag4 = nodelist.Item(3).FirstChild.Text
            Cells(rownumber, 7) = tag4
        End If
        Set nodelist = Nothing
    End If
    Set XMLDOC = Nothing
End Sub

This gives me a new excel workbook i can load into PowerPivot again:

I created a relationship using the artist name between the two tables.

To get to know my data I created a PowerPivot workbook to display my top artist, tracks and charts by genre and time listenend:

Pretty cool to play with my music data like this. But now for the final steps we want to load it into the PivotViewer.

The first thing we need to do is make sure we have all the data we want to use in the PivotViewer app in one table, check out the first PivotViewer blog post for more details. I used the =RELATED function to get the columns I want in the PivotViewe from the other related table.

Now we build the report we want to use in the PivotViewer, the report will be used to show details about the track. The report must have a parameter so the PivotViewer can crawl each song  by using the parameter, I want to use the image belonging to each track to show in the PivotViewer together with information about the track:

We now can ise the report to create the PivotViewer application. I configured the BI Collection administration as below:

Now I crawled the BI collection using the PivotViewer crawler tool.

This results in the PivotViewer app with all my tracks in it, below you can see the tracks with playcounts grouped by genre:

And a detail of the track:

A pretty cool excersise with a great result. Although I could do some more work in making the PivotViewer app a little better by presenting it a better report with other categories to show. But unfortunatly my time is limited 🙂