Kasper de Jonge BI Blog

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