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.

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

Even though it’s my holiday i couldn’t resist to download and install SQL Server 2008 R2 and play around with Gemini. I forgot you have to have access to Excel 2010 to do so, luckily some co-workers have access to the Technical Preview so i could get it there.

After installing SQL Server 2008 R2 and Office 2010 i still didn’t have a Gemini tab, it appears you have to download the Gemini add-in separately, you can do so here: https://sharepoint.connect.microsoft.com/SQLServer/Gemini. You do have to get access from Microsoft before you can download the plug-in.

Ok on to Gemini.

After installing the plug-in you get an extra tab in Excel 2010 that looks like this:

To start we have to load & Prepare data, i decided to use the AdventureWorksDW2008 database and recreate a part of the AdventureWorks cube.  When you click the Load & Prepare data button the Gemini client will open in a new window.

As you can see there a few options to choose from, i’ll choose the database now but the Data Feed and Report options look very promising (think connecting Berlin to the Data feed in an ESB environment).  After clicking the From database button you have to choose a data source and then select a table or write a query of the data to import. I’ll choose for a the DimSalesTerritory and the FactInternetSales tables to start with, you can even choose a friendly name:

At the last step the preparation is completed:

The Gemini add-in has now loaded the data into the Gemini client

It even found the relationship on the tables and created it in Gemini as we can see in the manage relationships window:

Now before looking at the result i want to add the time dimension to the data by hand, to do that we need to click the  “from database” button again and add the DimDate table

Now important in Gemini is to create the relationships for each table to the fact table, we’ll do the DimDate by hand, click on Create relationships and select the corresponding fields:

Make sure you get primary and foreign key in the right order. Now its time to see the result and add the data to a PivotTable by clicking the PivotTable button.

You now have a full working PivotTable in excel:

After playing around with data you can’t even feel the difference between the PivotTable on the AdventureWorks cube and the same data from Gemini. Great stuff and very powerful.

The only thing i can’t get working are the slicers, they keep coming up empty where there should be data:

Thanks to the comment of Tatyana below the slicers work as well, i love this functionality, it, besides looking great, works great too

Overall a very powerful tool and i see many great applications for it. I for one am very enthusiastic and I know some clients who are waiting for this.

Although i have my reserves about it still being somewhat too technical with the relationships for the real business analysts, it would be better if the relations where somehow automatically suggested when you add a table. I would see a DBA creating various views on the datawarehouse which the business analyst then can use to analyze. On views relationships don’t exist so Gemini won’t recognize them, a automatic relationship recognition would be great. Well maybe after some training the analysts will be able to do it themselves with the right training.

UPDATE: news on twitter travels fast i got a reply from Donald Farmer on my blog post and apparently MS is working on automatic relationship detection right now. Can’t wait to see the final product

When you want to want to govern the acces your users have to your data in the SSAS cube’s you have the ability to use Roles to set secutiry on all sorts of levels, from cube level to dimension data.

We want our users to get only acces to different parts of the data, like department managers to only data from sales in their department. When setting security on department levels Autoexists take care of the security on other dimensions and present the reports through reporting services to only show data with the department data selected in the query as well so Autoexists can do its task. This makes usermanagement of roles somewhat easier to maintain.  But users who have acces to the cube can also connect a cube through Excel and thus bypass the dimension Departments where we have put our secutiry, to counter this you can set secutiry on all dimensions you want the user to see or make sure the user can’t connect to the SSAS cube you don’ t want want to connect him to. My collegue Marc Valk  found out you can add a group policy that disables the “get external data”, ” from other sources” tab in excel:

http://www.marcvalk.net/2009/06/gpo-disable-office-ribbon-menu-items/ 

 This makes sure users cannot make a connection themselves but still can open existing connections from e.g. SharePoint.

Allan Folting, program manager who works on the Excel team has collected some. Questions customers around specific functionality in Microsoft Excel 2007 PivotTables that is missing when connected to Microsoft SQL Server 2005 Analysis Services Service Pack 2 cubes:

1. Filtering individual calculated members
2. Showing non-visual totals when filtering

He have also gotten lots of requests for the ability to add calculations to OLAP PivotTables using the object model, specifically:

1. Adding calculated members/measures
2. Adding named sets

Read the entire article with answers here:
http://blogs.msdn.com/excel/archive/2008/02/05/common-questions-around-excel-2007-OLAP-PivotTables.aspx

Teo Lachev point us to a great blog post on the Steve Novoselac’s excellent blog about designing your pivottable reports in Excel to get the following display:

Read how here:

http://prologika.com/CS/blogs/blog/archive/2009/03/31/excel-pivottable-tabular-reports.aspx