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 🙂
10 Replies to “Working with Gemini and Excel 2010 to make a pivot table”
Great (p)review! Thanks!
thanks for doing this…will be following u via twitter
Go to “regional and language options” in control panel and change current format to English (United States). That should fix slicers issue.
Thx works great !
Quick question. Is Gemini dependent on SQL Server or can you use with Excel 2010 alone?
@Bent Møller Madsen
Hi Bent, I believe you only need Excel 2010 and the gemini Add-in:
“The add-in provides the Gemini client application for adding large scale data to Excel workbooks. It also includes a local processor that enables large scale data in a workbook, and client libraries used to import and load data from external data sources.”
See more at the BOL:
Is there a reason that Gemini is implemented as an add-in instead of being a part of the core features of Excel 2010?
My guess is that there are to be additional costs to run gemini, wheter as part of SQL Server or SharePoint 10 or a CAL based gemini price.
@Kasper de Jonge
One observation I had – I was able to repo your steps against SQL2008 based A2008DW. DO you know what functionality R2 provides that enhances Gemini?
Well SQL Server R2 will be hosting the published Gemini model in SSAS in a new “in memory” type of filesystem (IMBI). Not much info is available on this subject yet.
More info can be found here:
Chris Webb Bi blog
Gemini team blog