Overview of PowerPivot features

Note: this post refered to PowerPivot by its codename, Gemini. The content, however, is still a good overview of the functionality available in PowerPivot. You can download PowerPivot here .

The key question behind Gemini is: how much value can an Excel user derive from Microsoft’s BI stack without having to pick up a book on OLAP, Dimensional Modeling or Analysis Services?

Significant, as it turns out.

This article provides a quick overview of Gemini features, pausing on the way to point out key design goals. Note that the content of this article is based on August Community Technology Preview (CTP) and will likely change in the future.

Introducing the Gemini Addin for Excel

After installation, Gemini shows up on the Excel 2010 ribbon:

Clicking on ‘Load & Prepare Data’ button launches the Gemini addin window:

Let’s pause here to recognize how Gemini and Excel complement each other:

• Gemini addin provides an embedded data engine that can:
  • efficiently hold and operate on data sets larger than those possible with Excel
  • import data from a variety of data sources
  • allow modeling operations such as specification of relationships between tables, and formulas expressing business logic
• Gemini addin also provides a Gemini Client Window (shown above) which is an Excel-oriented user interface over the embedded data engine
• Excel provides powerful visualization capabilities such as PivotTables, PivotCharts and Slicers. Together with the Gemini addin, these visualizations can also use Gemini data. Remember that with Excel Services, these are also available in a thin browser environment!
• An Excel document is a great way of packaging up both data and visualization. This means everything you see in the Gemini addin window is saved inside your workbook to ease management and sharing. No separate files to worry about keeping in sync, no separate services to manage.

Importing Data into Gemini

Of course it all starts from data, and because of that, Gemini enables you to bring in data from a variety of different data sources. Some of these are shown below, future CTPs will likely allow you to access other types of data as well:

Based on the type of data source, you can select from a list of tables, preview data, select and filter columns to import, and/or specify a query to use for the import.

Copy/Paste

Users frequently want to combine their data with a small dataset they got in an email, found on the web, or just typed in themselves. As you would expect, Gemini addin allows mashing up this with the rest of the data in Gemini. If the source data is coming from the host Excel document, Gemini also allows created a link between to keep this data in sync.

Data Feeds

In addition to the structured data sources that you expect Gemini would support, Gemini also supports reading data from Atom-based Data Feeds or from SQL Server Reporting Services 2008R2 reports. The beauty of these is that consumer don’t need to have direct access to the underlying data sources and don’t have to replicate any logic built into the report definition either… they just point and consume and data feeds show up as any other table in Gemini!

Working with Tables

Once tables have been imported into Gemini, they show up as tabs in the Client Window:

If the data source contained relationship between these tables, Gemini data import process would pick them up automatically. Otherwise, you can create these manually:

Why would you want to specify these relationships? Knowledge of relationships allows Gemini to navigate and group data correctly, and this prevents users from having to bring in all columns from related tables into one very wide conglomerate.

Calculations

Imported data is a good starting point. While we’ll have a more drill down into formulas in the future, from an overview perspective, it’s important to recognize that a design point of Gemini is to allow customers to express their business logic without requiring knowledge of dimensional modeling. Excel users expect the following simple formulas to work:

=if([Gross]>100000000, “Blockbuster”, if([Gross]>10000000, “Normal”, “Tragic”))

… and it does with Gemini.

Architecturally, Gemini calculation engine does not push its data to Excel to recalculate. That would take too long and given the Gemini data engine can support data sets much larger than what Excel does natively, could run into scalability limits. Instead, Gemini implements significant number of Excel functions itself. Here’s a snippet from the August CTP list:

You’ll notice that some familiar functions – ABS, AVERAGE, AVERAGEA – show up as expected by Excel users. Some others – ALL, ALLEXCEPT, AVERAGEX – are new. While building Gemini, we wanted to create a tool that’s easy to start with, but kept up when demands grow and users want to push further. Here’s a quick example:

Let’s say we have a database that contains a list of movies in a Movie table and box office sales in the BoxOffice table. Of course, a particular movie may have BoxOffice sales across years, and the user wants to find out what was the total money made by a movie across these years. How would we do it in Gemini? Well, once a relationship has been created between these two tables, all we have to do is for each row in the Movie table, go across to the BoxOffice, restrict the rows for just that movie, add up the Gross column for those rows and return results. Then repeat for all movies. Sounds complicated? Not if you know about the RELATEDTABLE and SUMX functions:

=SUMX(RELATEDTABLE(BoxOffice),BoxOffice[Gross])

We’ll cover this in more depth in the future.

Using Excel Visualizations

Importing and modeling is very useful to shape the data perfectly. In order to allow users to create rich, interactive, BI applications, the Gemini experience depends on Excel for the next step: visualizations.

Excel has a robust arsenal of tools in this area: PivotTables, PivotCharts, and numerous configuration options for these. Excel 2010 also introduces Slicers which provide both slicing of data in other connected controls as well as feedback to other Slicers based on data. For example, if we didn’t have any sales in Canada for 2005, after selecting Canada, 2005 would automatically be colored to reflect the fact that there’s no data there:

 

Continuing with the principle that end users shouldn’t have to worry about what’s a dimension and what’s a measure and why can I use one as the other, Gemini addin also overrides the PivotTable field list with one that we believe is much easier. Note again the tight integration between Gemini and Excel, Slicers show up as drop areas in the field list:

Building a Rich, Appealing BI Application

After spending a few minutes formatting and laying out controls just the way we want them, we end up with a very functional, self-contained workbook which acts as an application:

 

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.

 

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.

Summary

This was a whirlwind tour of Gemini functionality as it is available in August CTP. This link describes how you can get your own hands on the CTP.

In the next few weeks we’ll dig in deeper to specific areas. In the meantime, keep the feedback coming on the Gemini newsgroups!

Comments

• Anonymous
  August 24, 2009
  I love the feature that let you use the Gemini Applications as Data Sources!But i was wondering, since Gemini is in memory, what it would do to the server performance when you have a lot of gemini applications running on SharePoint? Would it all be in memory or would ik be loaded into memory when called upon?  Thanks,Kasper de Jonge
• Anonymous
  August 25, 2009
  You meantion that the underlying Data can be refreshed in SharedPoint.What about refreshing the data directly in Excel via Gemini?Imagine that the Workbook I am creating has some complexity and takes a few days to create. By the time everything has been loaded, connected (relationships) the data has changed. Before, you save it to SharePoint (and share it with the Team, Department) you want to check that everything is OK. However, that is not possible in Excel/Gemini, right?Thanks,Tiago Rente.
• Anonymous
  August 25, 2009
  Hi Kasper,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.regardsash
• Anonymous
  August 25, 2009
  Hi Tiago,Refreshing data from within the Excel addin is not possible in CTP2. However, as you describe, this is a very common need and it's very likely to be supported in a future CTP.Thanks for the feedbackash
• Anonymous
  September 08, 2010
  Hi,Will power pivot work on Mac office 2010? I really really hope it does. Please let me know.