Kasper de Jonge PowerPivot Blog

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

Today we are excited to announce the availability of the first community technology preview (CTP) of Microsoft SQL Server 2008 R2 for MSDN and TechNet subscribers, with the CTP generally available on Wednesday, August 12th.

Building on the momentum of SQL Server 2008, “R2” improves IT efficiency by reducing the time and cost of developing and managing applications; empowers end users to make better decisions through Self-Service Business Intelligence and enables organizations to scale with confidence by providing high levels of reliability, security and scalability for business critical applications.

This public preview offers the opportunity to experience early, pre-release feature capabilities including:

• Application and Multi-server Management
• SMP scale up with support for up to 256 logical processors
• Report Builder 3.0 with support for geospatial visualization

This CTP provides the first opportunity to explore some of the features of SQL Server 2008 R2 and see how it all comes together to enhance performance and scalability, enable self-service BI and improve IT and developer efficiency. The CTP process is also a great way for you to give us feedback on the new technologies.

Read more at: http://blogs.technet.com/dataplatforminsider/archive/2009/08/10/download-sql-server-2008-r2-august-ctp-today.aspx

Microsoft released a BI update to SQL Server 2008, aside from Gemini it has some other great new functions like the Lookup Function in reporting services. I was waiting for this one, you can lookup the first matching value for the specified name from a dataset that contains name/value pairs. This gives you the opportunity to join 2 datasets and whats best from 2 different cubes!

One mayor drawback is that you can only join on one key and a SSAS dataset usually doesn’t have a single key since you have data on an x and y axel, but with some custom work we can fix that.

In the following sample I have put data of 2 adventureworks datasets on one tablix. Each dataset contains year and Sales Territory Country and a measure. Lookup uses 2 keys to match the data from 2 datasets, I made a new unique key for each dataset combining the the uniquename of the x and y axel and added it as a calculated field to each dataset, added a field to each dataset with value “Fields!Sales_Territory_Country.UniqueName + Fields!Calendar_Year.UniqueName”.

Add the value of one dataset to a tablix

Then add a new column with the following expression to add the Internet_Order_Count value from the other dataset to the tablix:

=Lookup(Fields!CombKey.Value,Fields!CombKey2.Value,Fields!Internet_Order_Count.Value,”DataSet2″)

and thus adding the value from the 2nd dataset to the tablix, works like a charm. I even tested putting a filter on dataset 2 to make sure there aren’t an even amount of values and that works too, it just returns a null value.