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 🙂