I attended beginning this week the Microsoft BI Conference in Seattle where Microsoft presented an interesting new product that might change the way you build your Excel dashboards in the future: “Gemini”, an End-User Business Intelligence plug in for Excel.
End-User Business Intelligence has been around for quite a while with some of the most prominent Applix TM/1 , PALO, PowerOLAP and MIS Alea. These tools where tailored for non-IT proficient Excel users to allow them to build real Business Intelligence solutions with Excel, without leaving Excel or having to learn complicated BI or Data Warehousing techniques. All you have to learn is that a cube is like a very powerful multidimensional spreadsheet that can easily aggregate and hold large amounts of data. To pull out data from the cube you use a formula like this…
=LookupCube(“Sales”, “Units”, “Hats”, “Store 19″, “Oct-2007″)
…returns unit sales information consolidated by product line and region for the month shown. And this formula…
= LookupCube(“Sales”, “Sales USD”, “Hats”, “Store 19″, “Oct-2007 YTD”)
…returns year-to-date sales in US dollars for the same product line and region
Anyone who has set up a YTD calculation with an Excel Dashboard that holds all data and BI logic in Excel knows how complicate that can be with Excel offset and lookup formulas.
End-User BI can make Excel such a powerful BI tool that Charlie Kyd, an known Excel authority and Excel veteran, dedicates End User BI a complete section on his portal:
So, how can I build a BI model with Gemini?
The following screen shots show some of the key screens Donald Farmer showed us at the key note presentation:
Gemini manifests itself as a new Ribbon in Excel 2007:
The modeling happens in the Gemini modeling window
You load some Sales data from external tables in your data warehouse:
Donald loaded 20 million rows in a simple $1,000 workstation using Excel 2007
Sorted the table ….
…or filtered it…
…incorporate some Excel data….
…by copying / pasting it into the Gemini Client
The Gemini model allows you to mash up data from many sources, including you data ware house, Access tables, Web queries, Excel sheets and more
To analyze your data in Excel you create a Gemini PivotTable…
Gemini automatically infers the relation ship between your tables in the Gemini model. So, the end users don’t even have not to build dimensions or cubes before they can analyzes the data (as with TM/1 or PALO), they can directly can go ahead and slice and dice the data…
A new navigation control concept, the so-called slicers, allow you to select members in a way where all members are layed out horizontally or vertically, all visible and selectable, unlike standard PivotTable filters that requite you to open a combo and to select an individual member from a tree.
What was not quite clear what would happen if a dimension would contain 00s or 000s of members.
What the user creates technically behind the scenes is a star schema, dimensions and a cube, without actually knowing what a cube is or having to build one himself.
Existing BI Client applications will be able to access Gemini via the standard Cube query language MDX. For example an average Business User will be able to create a Gemini model and browse large amounts of data graphically with the Excel Add-In XLCubed
Microsoft expects to have a beta available in the next 12 months. One thing that is not quite clear is the licensing and pricing. I really hope Microsoft gets this right and makes the Excel based plug ins available for free, as I expect that having real BI capabilities in Excel will boost Excel based dashboarding a lot and will make your lives so much easier.
I am a bit skeptical about how soon a final Gemini release will be available and how Excel-like this will be, as this is crucial for Excel power users. Also, will that be a free, integrated part of all future Excel versions? Will Jon Peltier, Charlie Kyd and Mike Alexander adopt this technology?