http://blog.contextures.com/archives/2010/09/06/powerpivot-from-identical-excel-files/

Very nice!

Sometimes you want your value to be always x chars wide. For example you want to create a yearmonth column of always 6 figures, 201010 and 201001 instead of 20101. What you can do is create a new column where you use the REPT function. This function repeats a specific string for a specific number. For example REPT(“0″,2-LEN(MONTH(Tablix1[Date]))) would give me 0 for 2 – the length of my month function. If the length of my month is 2 it wouldn’t return a 0 if it is one it would return one 0.

My year month calculated column dax would look like:

=year(Tablix1[Date]) & REPT(“0″,2-LEN(MONTH(Tablix1[Date]))) & MONTH(Tablix1[Date])

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

We start in Excel,  i have again loaded the same tables as before into PowerPivot containing the Factinventory and a table containing country’s and stores. This time we are going to use PowerPivot indirect since the data in PowerPivot is not really suited for the Forecast, besides we want to use an aggregated value for this and not all individual 8 million records we used before.

We start by creating a flattened pivottable, a great new invention in Excel 2010:

Here we can select the actual values as they are in our datasource, by year and month set out against country:

Important here is that i have one field that contains all months for each year we have data, ordered chronologically. I already had this field loaded  in my date table, but you could easily create one using DAX =Year & Month

This is all it takes to start the data mining. So again I open the PredixionInsight Now Ribbon and click Forecast:

This time no direct PowerPivot connection for us:

We can just select the range my flattened pivottable is in and press OK.

In the next screen we can select which columns we want to forecast, in our case we created a column with units on stock for each country, we can select each of them. Next we need to select the time stamp we want to use, this is the Calendarmonth column, of the type Month. We want to predict 6 months ahead:

Now all we have to do is press Run, it will upload my table to the Predixion servers and in almost no time it has processed the values and i am ready to use my results. Note my previous job is also still in there ready to be used in this Excel sheet as well:

Press on results and the results are created inside Excel:

As you can see we need to pay extra attention to Germany, the forecast is that they are building up huge units on stock in the near future. Again another powerful use of your data, and very easy to do !

Update: I got a message from Jamie MacLennan CTO from Predixion who gave a hint to improve my forecast: if you cut out that part of the data that is very different from the rest of the graph (i.e. prior to 200802) the prediction may be better.

Of course i tried this and the results do look more realistic:

Predixion Insight for Excel is like a new version of the current SSAS add-in, the Predixion insight team consists of the folks that previously build the Add-in for MS and now started on their own.

The biggest change is that you no longer need an SSAS server installed. All action happens on the Predixion servers in the cloud. Second biggest (for me) is that you can use PowerPivot data as a datasource for you Data mining. Using it in combination with PowerPivot requires nothing more then Excel and a Predixion subscription for data mining. Furthermore the overal UI had been improved to make data mining a more user friendly experience. And it support 64 bits.

From the Predixion site:

Predixion’s intuitive and easy-to-use solution allows users to run predictive analytics in the familiar environments of Microsoft Excel^® and PowerPivot. Whether you are an existing SQL Server^® Data Mining user, a BI specialist or a newcomer to the arena of Predictive Analytics, Predixion Insight™ will enable you to easily create, manage and run powerful and accurate predictive models without extensive training or specific knowledge of the methodologies currently required to create successful predictive projects.

In this blog post we are going to see what are the key influencer are of the number of items on stock from the Contoso sample database.

First we need to install the Predixion Insight for Excel, just run setup and the client will be installed within Excel. Next time you open Excel the client will be there. We have two tabs “Insight analytics”:

and “Insight Now”:

The “Insight analytics” tab is mainly for the advanced data mining,the insight now enables you to get started immediately. Before we can do anything we need to connect to the predixion servers with the account we created on the website:

After logging into the Predixion cloud service we can start data mining. I have loaded information from my datawarehouse in PowerPivot for Excel, i have information about my stock. I have loaded the fact table FactInventory that contains the actual nr of stock, this contains 8 million rows . The fact table is related to a lot of descriptive tables that surround the fact table, called the dimension tables. I have loaded a few of these descriptive tables into PowerPivot as well. What do we know about an item that is on stock:

• When was it on stock? Year/month/day
• What Product?
• What Productcost
• Aging of a product in inventory
• The Country of the store it is in.

Of all these properties we want to know what influences the nr of days in stock the most. For this i want to use the “Analyze key influencers”  function. So i click on it.

This gives us a screen where i can select what my source is, Excel or PowerPivot, I select PowerPivot. Now i can select what table i want to analyze, i select the fact table. We could place filters here but i decided to plague the Predixion server all out with my full 8 million rows .

Next we can select the column we want to we want to determine the key influencers for:

Of course we don’t need all the columns to be analyzed, we can select the columns we want to include in our analysis:

And this is where we notice something not right. As you can see we can select DateKey, StoreKey, ProductKey. But when we analyze this it would analyze this as a Key value, instead of Year 2009 it would test for the value 1-1-2009 and Store “Amsterdam” it would check as Integer 12. So we need to do something first, we need to prepare our PowerPivot table so that it contains descriptive values.  Luckily for us this is not that hard, just add a column in the PowerPivot field window using the =RELATED function:

Now we can select these columns in the data mining add in:

Now we are good to go, just click Run and the data mining will be started.

The great thing here is that everything happens on the server, i can start multiple operations at the same time. And of course it being in the cloud i can open this up on another machine and immediately access the results.

One thing i noted is that the information is send to the cloud through an encrypted tunnel so no worry your data can be read while sniffing your network.

When i click on Minimize to Task pane you will see a new Predixion pane will show up where you can see all your tasks:

As you can see i ran this demo before so i can use these results to show the result of the mining Predixion did, just click “Results” and the report below appears:

As you can see it is pretty easy to combine the information you have in PowerPivot with the enormous powers of data mining. The new user interface and the availability of the Predixion servers in the cloud really make data mining available for anyone. Just as PowerPivot makes data analytics available for everyone. The Predixion Insight for Excel works with Excel 2007 and Excel 2010 32 AND 64 bit, of course PowerPivot won’t be available with Excel 2007.

Predixion Insight is also working on a on-premise and dedicated off-site cloud solution which leverages SQL Server, SSAS and SharePoint which they call Enterprise Insight.

I have a report that displays the following values. It shows Days in stock per employee per Continent and per Country. The days in stock is a calculated measure:

=DimGeography[DaysInStock per store] / DimGeography[NrOfEmployees in service]

which again contains of two calculated measures.  The question i got was if it was possible to show the minimal value of the measure [days in stock per employee]  for each country grouped by Continent:

In this case the minimal value of the country’s of the continent Europe is 2.483,69.  So how can we achieve this? The biggest problem is that we are unable to use the MIN function, the function MIN only takes a column, not a measure. So again we resort to the The 5-point palm, exploding fxn technique in this case MINX.

I want to start by getting the measure for each DimGeography[RegionCountryName]  into a Min function. This looks like:

=MINX(values(DimGeography[RegionCountryName]),DimGeography[Days In stock per employee])

Only this returns the minimal value of [Days In stock per employee] for each [RegionCountryName], this is the same result as the regular measure. MINX uses the same context as all other DAX functions.

The reason we use values(DimGeography[RegionCountryName]) in stead of the table DimGeography is because of the performance. MINX steps through each rows of the passed trough parameter, as you can imagine stepping through a single column is much better performance wise than an entire table. By using Values we get only one column from a table.

Ok MINX uses the context as other DAX functions, that means we can change it using our almight CALCULATE function as well, this gives us:

=CALCULATE(
		MINX(VALUES(DimGeography[RegionCountryName]),
				DimGeography[Days IN stock per employee]),
		ALL(DimGeography[RegionCountryName]))

What happens here is that we change the context to include all RegionCountryName’s within the group continent.

The measure gives this result in the PowerPivottable:

Calling the website from his entire URL does work from outside the domain as well: http://l01.labdomain.nl. Until you actually open the PowerPivot file from the gallerie. You get a

The file that you selected could not be found. Check the spelling of the file name and verify that the location is correct.

How can you design an effective, useful, and appealing dashboard? Many articles and books focus on the mechanics of how to choose KPIs and the various formats available for displaying data. But even the most significant information can be lost if it is not displayed in a memorable way. Is the right format for critical values a grid, a graph, a gauge, maps, diagrams, or something else? What kinds of information are best displayed in a line vs. a bar graph? Where does a scorecard fit into all of this? Since they are not static, how dashboards interact and how they fit into a larger reporting environment offer a particular design challenge. Designing the navigation across the content on each dashboard page is critical to communicating the desired message. In this session, learn techniques for displaying a set of data in a dashboard for maximum impact and receive a framework for constructing dashboards from the various content types.

You can watch the entire presentation online at the teched online site.

One of the great slides in his presentation is the Chart suggestions chart that let’s you choose the chart type you need for the specific data you want to show:

Another great source about dashboard design is Stephen Few’s: Information Dashboard Design: The Effective Visual Communication of Data

• PowerPivot for SharePoint: User Identity Fundamentals for Server Administrators
• PowerPivot for SharePoint: Security Context of PowerPivot Connections in a Farm
• PowerPivot for SharePoint: Data Refresh
• PowerPivot for SharePoint: Using Workbooks as a Data Source

Update: They are now all available on the PowerPivot Technet site.

When you import data from SSAS into PowerPivot all column are treated as text. This is a by design feature of PowerPivot:

This is a by design behavior in the current version of PowerPivot. The reason behind is due to the fact PowerPivot does not support Variant data type, while MDX does. The cell value from a MDX query may potentially return different data type, for instance, in a IIF statement IIF (condition, expression1, expression2), expression1 and expression2 can return different data type. In order to support all these scenarios inside PowerPivot, we convert the values to string during import. You would have to explicitly change the column data type after the import as you have already observed.

as we can read on this forum post. Sounds like a solution that makes sense, but it also gives the end user more to do. You have to convert all the columns that are not text (like date and decimals) to the type you want in the PowerPivot window. This might give you a problem when you have different regional settings on the client than on the server. In my case our SSAS server uses the English US regional settings on the server but i have Dutch settings on the client.

When I import data into PowerPivot all seems ok at first:

But notice that our measure is imported as text. To make aggregations you need to covert this to a decimal, doing this give strange results:

As you can see here the decimal sign is just removed and we have one big number now. Thanks to Marco Russo i found a workaround. Because we changed the data type to decimal means PowerPivot treats this column as decimal from now on, also when refresh data. And that is all you have to do to get the correct data. Refresh the data and the numbers are correct again (and will be in the future):

Marco has created a connect item for this to be solved in the next version as well, please vote for it here so they will take a look at it in the next version(s).

I don’t know what happens when you have the same regional settings on the server as on the client, but I expect it to work correct without having to use the workaround.

Another tip: When you change the regional settings of your client machine make sure you restart Excel, only then will it pick up the changed regional settings.