Kasper de Jonge PowerPivot Blog

Just a quick one tip today, I thought I already blogged this one but i couldn’t find it.

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

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.

Read more…

In my previous blog post I checked what the key influencer is for having a lot of stock using PowerPivot and Predixion Insight for Excel. Now i want to see what the number of units on stock will do in the future. I want to see this by country for the next 6 months.

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.

Read more…

Since this week the public beta of Predixion Software’s Data mining in the cloud for Excel is available. Those of you who are familiar with the the Microsoft SSAS Data mining Add-ins should be very comfortable with what is inside Predixion Data mining for Excel.  I have done a previous blog post on doing data mining using PowerPivot with the MS data mining add-in where you can see how it currently works .

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.

Read more…

I got a question that proved more challenging than I imagined.

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:

A lot of times you want your PowerPivot site to have another URL then the one you original got during the install. During installation the it uses the server name as url. In just installed PowerPivot on a local machine in our netwerk called l01, but because we have this running in a separate lab domain it cannot be accessed from outside the domain.

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.

A tweet that got around twitter today reminded me of a great session I attended at Teched this year. It wasn’t about PowerPivot and it wasn’t even very technical. It was a session from Dan Bulos about Dashboard Design Best Practices.  This is what the session was about:

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

The SQL Server SSAS team just release 4 great video’s where MSFT’s Lee Graber explains the PowerPivot for SharePoint Architecture. This gives great insight on PowerPivot and SharePoint integration. Mind you these videos are not for the weak of hart and go pretty deep But well worth the time if you are into SharePoint and PowerPivot:

• 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.