Working with Gemini and Excel 2010 to make a pivot table

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:

image

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.

image

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:

image

At the last step the preparation is completed:

image

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

image

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

image

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

image

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:

image

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:

image

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.

image

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

slicer

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 🙂

  • Shuwi

    Great (p)review! Thanks!

  • greg kramer

    thanks for doing this…will be following u via twitter

  • Tatyana

    Go to “regional and language options” in control panel and change current format to English (United States). That should fix slicers issue.

  • Kasper de Jonge

    @Tatyana
    Thx works great !

  • Quick question. Is Gemini dependent on SQL Server or can you use with Excel 2010 alone?

  • Kasper de Jonge

    @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:
    http://msdn.microsoft.com/en-us/library/ee210644%28SQL.105%29.aspx?ppud=4

  • Kory

    Is there a reason that Gemini is implemented as an add-in instead of being a part of the core features of Excel 2010?

    • Kasper de Jonge

      @Kory

      Hello Kory,
      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

  • Arpad

    @Kasper de Jonge
    Hello Kasper,

    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?

    Cheers,
    -as

  • Kasper de Jonge

    @Arpad
    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
    and
    Gemini team blog