The „Project Gemini“ is the code name for an Excel 2010 Add-In, which allows the user to use real „Self Service Business Intelligence“. Gemini consists of the three following components:
|–||An Excel Add-In with own interface and own ribbon. If you use the Add-In, you’ll be able to imports millions of rows, create relationships between them, calculate values in new columns using formulas and create attractive Pivot Charts. You don’t need to learn database query languagues like T-SQL or MDX. And, in additionl, you can publish your solutions on a sharepoint server.|
|–||There is also an Add-In availaible for Sharepoint 2010. This Add-In allows you, for example, to manage the workbooks published with Gemini, assign some rights or create more reports. Please note that the Excel Services should be installed and activated.|
|–||The last component is the new memory engine, using a column-based compression. Exactly this engine allows you quickly and easily manage and include the millions of rows.|
Microsoft let me also participate to the Gemini preview; thanks for that! The Gemini Team provided to the participants some samples and tutorials for the first steps in Gemini. In these samples are included data files for use with the Microsoft SQL Server and also an Access Database for the people who have not access to a SQL Server. Throughout this article, I lean to some steps of the tutorial. For simplicity, I used the Access Database for my first steps with Gemini. Another reason is that on one hand I’m not an expert for SQL Server and an another hand, I should have to install it, which is not possible at this time.
The tutorial from Microsoft includes the database file AW_CompanySales.accdb, which contains data tables for the sales of a fictive company „Adventure Works Cycles“, manufacturing bicycles. In the screenshot above, you can the the relationships between the tables of the database. I think, having a look on them before using Gemini can be useful. As you can see, the table „Total Sales“ is linked to the tables „Product“, „SalesDate“, „Currency“, „Geography“ and more. I rearranged some fields in the table „Total Sales“ for better presenting the relationships. Please note that the table „Total Sales“ contains 1,375,079 recordsets.
After installing the Excel Add-In (the .NET Framework 3.5 SP1 and and Office 2010 have of course been installed before), I started Excel 2010. You’ll see a new ribbon:
I’m using a german operating system (Windows Server 2008). Although I changed the language of Office 2007 to English and the English version of Office 2010 is installed, some labels in the „Gemini“ ribbon are in German. I dont’t really know why, I think the Add-In is using some components in German. Ok, let’s import the database by clicking on „Load and Prepare Data“ (in the figure „Daten vorbereiten und Laden“ in German). A new window opens:
Again some labels are in German, others in English. There are three ways to import data. First, from a database, second, from a data feed and third, from a report. The first choice opens following window:
After selecting „Microsoft Access“, we got the next screen, where we choose the Access database file:
In the next step, we are asked to choose between importing tables or creating a query. We opt for importing the tables and get a list of all available tables.
If we like, we can give friendly names to our tables by clicking in the fields in the column „Anzeigename“ (in English „Friendly Name“). If we click on the button „Vorschau anzeigen und filtern“ (in English „Preview and filter“), we can setup a filter for the selected row, as you can see in the following picture.
After creating a filter, pressing „Ok“ and closing the window, you get an info text in the selected row and the column „Filter Details“. If you click on this text, a new window opens with more details about your filter, here an extract:
Selected columns: ArabicDescription, ChineseDescription, Class, Color, DaysToManufacture, DealerPrice, EndDate, EnglishDescription, EnglishProductName, FinishedGoodsFlag, FrenchDescription, FrenchProductName, GermanDescription, HebrewDescription, JapaneseDescription, ListPrice, ModelName, ProductAlternateKey, ProductKey, ProductLine, ProductSubcategoryKey, ReorderPoint, SafetyStockLevel, Size, SizeRange, SizeUnitMeasureCode, SpanishProductName, StandardCost, StartDate, Status, Style, ThaiDescription, TurkishDescription, Weight, WeightUnitMeasureCode.
Applied Filter: ([ProductAlternateKey] = ‘BA-8327’ OR [ProductAlternateKey] = ‘BB-7421’ OR [ProductAlternateKey] = ‘BB-8107’ OR [ProductAlternateKey] = ‘BB-9108’ OR [ProductAlternateKey] = ‘BC-M005’ OR [ProductAlternateKey] = ‘BC-R205’ OR [ProductAlternateKey] = ‘BE-2349’ OR [ProductAlternateKey] = ‘BE-2908’)
Before continuing the import, I deleted the filter as I would like to import all data. When clicking on „Next“, we got a summary of our choices and then we can start the import by clicking on „Finish“. When the data is imported status messages are informing us about the progress.
As you can see in the red marked area in the picture above, more than 1,000,000 rows have been imported from the table „Total Sales“. All operations including the data preparation took approximately (only!) 3 minutes on my computer.
Let’s examine the main window now; we can see on the bottom buttons for the imported tables. If we right click on a column header of a table, we are able to perform some operations like jumping to the linked table (if a relationship exists for the selected field). You remember our first screenshot in this article? Yes, „CurrencyKey“ has a relationship to „CurrencyKey“ of the table „Currency“. If we like, we can call a window which shows the available relationships for a specific or all tables. We also can create new relationships or delete existing ones.
Let’s now select the table „Total Sales“ and scroll to the right, we can add a new column by double clicking on the header of the last column and entering a name for our column. For testing I tried to use an existing column name („UnitPrice“). The Add-In automatically renames it to „UnitPrice2“.
Creating a new column would be meaningless, if it could not contain data. Let’s try – just for fun – to calculate the difference between the unit price and the total product cost. Entering =UnitPrice-TotalProductCost in the box next to f(x) leads to an error. Fortunately, the Add-In tells us to use brackets with the field names; we try =[UnitPrice]-[TotalProductCost]; it works 🙂
Wow, it’s really very easy to handle. Please note, at this point I have not finished to read the tutorial yet. So the syntax seem to be very intuitive (to me). As you can see, the formatting (currency) was also set in the new column. If we like, we can change this by selecting a new data type and an new format. Following data taypes are available: „Text“, „Number (Decimal)“, „Number (Whole)“, „Currency“, „Date“ and „Boolean“. I think the names speak for themselves. Let’s look what lies behind the f(x):
Like in Excel, you can use formulas for calculation fields, the syntax seems to be very similar to Excel. Furthermore, we will write a separate article concerning this.
How can we access this data in Excel now? And get a report about it? We give a try to „Chart and Table (Horizontal)“ after clicking on „PivotTable“.
First, Excel ask us where to insert the Pivot Table and data. We click „Ok“ and get
I’m not an expert for Pivot Tables, so I just played a little bit, by selecting a field related to the countries of the table „Total Sales“ and adding the corresponding field ot the countries table. The result is shown below.
In summary, I think, using and analyzing data with Gemini seems intuitive and simple. However there are some inconsistencies in the Add-In. For example, if you resize or move the Add-In window, it flickers on the screen and the labels are mixed in English and German. But I also keep in mind that I’m working with a preview of the Add-In. So certainly some improvements will be made until the final release.
This article has also been published in German and in English on the following sites:
- Excel Ticker Blog, German
- Professional Excel Solutions by Maninweb.de, English
- Professional Excel Solutions by Maninweb.de, German