Kasper de Jonge PowerPivot Blog

In Excel 2010 MS introduced Sparklines, as described on the Excel blog:

For Excel 2010 we’ve implemented sparklines, “intense, simple, word-sized graphics”, as their inventor Edward Tufte describes them in his book Beautiful Evidence.  Sparklines help bring meaning and context to numbers being reported and, unlike a chart, are meant to be embedded into what they are describing.

Too bad the sparklines aren’t really tightly integrated with PowerPivot  as are other charts. In this post i’ll describe an easy way to implement sparklines on PowerPivot data, with use of slicers.

Of course we start by loading data into PowerPivot, next  we create the following workbook in PowerPivot:

To gain more insight in sales per country per month I would like to use the sparklines per country. Lets start by adding data in a new sheet

Make sure you connect this Pivottable to the slicers from my other sheet by using the connect slicers option as posted in this blog post.

Ok next we are going to add the country’s to the original workbook. Select the country’s and copy them. Paste hem into the original sheet:

Now for the sparklines, go to insert and click Line. we now have to select the source for the sparklines, in our case this is the pivottable in our newly created workbook, and select all data in the months:

next we have to select where we want to put the sparklines:

This will result in the sparklines being showed:

We want them to be a little wider so we select the cell with the sparkline and the adjacent cell and click Merge and center to let our sparkline span two cells, next we select another sparkline design, I also like to use the markers.

This results in the following workbook:

We can even use the slicers, as we can see here when we select 2008 we only have 8 months, this will show in the sparklines as well:

I love sparlines, and they aren’t that hard to implement, for more information on sparklines see:

http://blogs.msdn.com/excel/archive/2009/07/22/formatting-sparklines.aspx and http://www.msofficegurus.com/post/Excel-2010-Sparklines.aspx

Having played with Data mining in combination with PowerPivot in a previous blog post, i wanted to do data mining at the company i’m currently consulting at, they use Excel 2007 in combination with SSAS. In excel 2007 we do not have the power of the flattened pivot table.  So i had to think of a way to convert the pivot table to a regular excel table. And i found a way, since i have converted all my excel version to 2010 i had to find an older image with Excel 2007 to make this post. I have used the dutch Excel version in the screenshots, i hope you can follow it.

Let’s say i want to data mine on the Adventure works cube. First step is creating a regular pivottable containing all the values you want:

Next we want to put all rows into a regular column, right mouse click on the pivot table, and select Pivot table options, Go to the display tab and select classical pivot table layout. Press ok.

This will result in all rows being put in a column:

Next we remove all totals since we don’t need this in this data mining example, go to design, subtotals, do not show subtotals.

Ok we have made our data ready to be converted to a regular table, we can do this in 2 ways. Copy all cells and paste them in a new sheet or convert to formula’s and use that.

I copied and pasted the cell to a new worksheet, used the keep only values as paste option. This results in the following table:

the problem here is the blank values in the excel sheet, to fix this i found a solution on the web at Mr Excel.

Start by selecting all the columns we want to fill the blanks of, go to the home tab and select “find and select”, and click “Go To special”. Here we can select blanks and click ok.

This will result in all the blanks being selected.  Type =, press the up arrow and press Ctrl+Enter (that’s hold down Ctrl and press the Enter key). All the empty cells will be filled with the value of one row above. Exactly what we wanted.

Now convert it into a table (Ctrl – L) and you are a ready to unleash the data mining beast:

The SQL Server Data Mining team is working to extend the power and ease of use of SQL Server Data Mining to the Cloud. You can use this add-in to run datamining against a service in the cloud, you don’t need a SSAS instance installed ! Check it out on this page: http://www.sqlserverdatamining.com/cloud/

Here is what the add in looks like in Excel 2007:

So you can just start using datamining with PowerPivot as we have seen in my previous screencast but then without having SSAS installed on your network.

The guys from the data mining team even build an online data mining tool, so you don’t even have Excel installed, here you can load data from CSV files or from  SQL Data Services and data mine that.

Excel 2010 will be a developers heaven, the MS excel team has written a blog post on how you can get to certain data in a Excel sheet published on Excel Services 2010.

The opportunities for using REST are virtually endless. To spur your imagination, here’s a sampling of some ways to use the REST API:

• Embed a chart or range in a web page, blog post, etc.
• Embed a chart as a “linked” image into a Word document, PowerPoint presentation, or Outlook mail, and have always up-to-date information.
• Build a “mash-up” that uses Excel Services for calculation, charting, and/or as a data source.
• Build a Windows 7 gadget that displays information from an Excel workbook
• …and many more ideas I am sure our customers will think up.

Phew the SharePoint 2010 product will really be “social” and really enable self service. Read the entire post at their blog.

At the MS Excel team blog they have a great blog post where a member of the SQL Server Analysis Services team, Ashvini Sharma, will tell us about the PowerPivot.
The post gives a comprehensive look at the entire PowerPivot product, from front to back and names all components.

Read the entire article here: http://blogs.msdn.com/excel/archive/2009/10/22/introducing-powerpivot.aspx

The big question i have is, will the VertiPaq engine have an API where developers can build there own frontend on PowerPivot… with loading, connection and analysing sources in memory.

Some colleagues of mine upgraded their host system to Windows 7. Since that moment they couldn’t connect to our test SSAS server to test the new developed analysis cubes from Excel.

The following error occured:

The following system error occurred: The security database on the server does not have a computer account for this workstation trust relationship. .

My collegue Bas Kroes found the answer while searching the internet on the MS forums:

Did you by chance install Windows 7 on your workstation (the one you re-imaged)?  I understand that this issue arises in Windows 7 due to a component called “Windows Live Sign In Assistant”.  From my understanding, you basically have 3 options:

1. Configure Kerberos on your SSAS server http://support.microsoft.com/kb/917409
2. Uninstall Windows Live Sign In Assistant on each workstation
3. Use SSPI=NTLM in your connection string to force NTLM as the Security Support Provider Interface

#1 is probably the preferred method, as it addresses the problem server-side, whereas #2 and #3 are both client-side workarounds.

found at http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/b058635f-6d3e-4240-a555-1dbd945ae25a