Kasper de Jonge PowerPivot Blog

The MS gemini team posted a new blog with a overview of Gemini, what was new to me was the following part:

Sharing Gemini Applications

While many workbooks are built for personal use, some are worthy of being shared across a workgroup. Here again, Gemini works the way Office users do. Since Gemini data is stored within an Excel document file, any way to move that document – through file shares, emails, publishing to SharePoint, etc. – transport the Gemini contents along as well. Users without the Gemini addin can browse the data, those with the addin get the full experience. Just as Excel and Gemini light up together, Gemini also extends SharePoint capabilities in several ways.

Report Gallery

For the more visually-inclined amongst us, a flat SharePoint list leaves something to be desired. File names, data last updated and by who are useful but only tell part of the story. Gemini provides Silverlight based skins that present different views on document libraries. These views show snapshots of the contents of documents. In the example below, we see two workbooks with two spreadsheets within them:

These snapshots are also live links in that clicking on a thumbnail of the a worksheet will take users directly into ECS with the worksheet loaded.

Scheduled Data Refresh

The Gemini model embedded within the spreadsheet keeps information about where data came from. Once published to SharePoint, users can specify schedules for the data refresh operation so the workbooks use the resources of the server to stay fresh.

And what was even more impressive:

Using Gemini Applications as Data Sources

Once published to SharePoint, Gemini models embedded within workbooks appear as an Analysis Services databases! This means any AS client tool – Excel, Report Builder, etc. – can connect to this database as if it were on just another AS server. The only difference for these clients is use of a URL to the document stored in SharePoint instead of a server name. Gemini services running on SharePoint handle loading the right database, managing its lifetime, and transparently redirecting client queries to the right database on the right server.

This gives some incredible new options, but i wonder what it will take of server perfomance (memory ?) when you have 20 of these models on your server.

Read the entire preview here:

http://blogs.msdn.com/gemini/archive/2009/08/24/overview-of-gemini-features.aspx

UPDATE:

after posting the question about the performance on the original msdn blog post i got an answer from one of the Gemini team members:

You’re correct, the Gemini embedded data engine loads data into memory. However this is only while the models are in use. Gemini’s SharePoint services manage the lifetime of these models and move these in and out of the SP content database transparently from the end users, the only realization users might have is the first time they connect to a model it takes slightly longer because we’re extracting the workbook then extracting the AS database and loading it up in memory.

Microsoft released some greate product video’s of Excel 2010 and Gemini at their Office 2010 site:

http://www.microsoft.com/officebusiness/office2010/Default.aspx?vid=Geminiv

A great video to show your end users !

I made a blogpost yesterday about Gemini and in it I showed an issue about Slicers in a PivotTable that kept turning up empty:

Tatyana from Microsoft apperently read about the issue and posted the resolution in a comment:

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

And indeed it did fix the issue, great fast reply from Microsoft:

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

Chris Webb has dissected the Gemini demos from Donald Farmer at the teched in LA.

Read the entire report and his observations here:
http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!4530.entry

Mircosoft has release a video demonstration on the Business background to Gemini and SQL Server Kilimanjaro. It shows how business users and IT administrator will benefit from SQL Server 2008 R2 with self service BI from gemini in Excel 2010.

Watch it here:
http://www.microsoft.com/sqlserver/2008/en/us/killimanjaro-video.aspx

Microsoft announced a new SQL Server version, SQL Server 2008 R2 with realy great upgrades for BI. And you can register for the CTP.

Some very interesting new features are:

• Self Service Analysis with “Project Gemini”
  

  Build Robust Analytical Applications

  • Combines native Excel 2010 functionality with an in-memory, column oriented processing engine to allow users to interactively explore and perform calculations on millions of rows of data at lightening speeds

  • Streamlines the process of integrating data from multiple sources – including corporate databases, spreadsheets and external sources

  • Access PivotTables, slicers, and other familiar analysis features in Excel to create reports and perform advanced analysis

• More Powerful Ad-hoc Reporting with Enhanced Data Models

  Report with Ease

  • Decrease time and costs developing reports

  • Enable timely access to information to help make better decisions by empowering end users to easily design queries, reports and charts through a highly intuitive, drag and drop interface

  • Powerful and intuitive authoring and ad hoc reporting capabilities with enhanced data models

• “Grab & Go” Reporting

  Collaborate with Confidence

  • Central, secure location for IT administrators and users to publish content objects that can be broadly reused and easily customized to meet the users’ needs

  • Ensure consistency by creating and maintaining departmental content that can be accessed by the rest of the organization for building comprehensive business reports

  • Accelerate report creation by allowing end users to reuse existing components (queries, tables, charts, maps, gauges, logos) as building blocks for creating new reports

It even includes the new MDM with Master Data Services (MDS).

Read more and register for the CTP here :  http://www.microsoft.com/sqlserver/2008/en/us/R2.aspx