Kasper de Jonge PowerPivot Blog

The SQL Server magazine has posted an interview with Microsofts Donald Farmer, a great interview. It explains how PowerPivot (called Gemini in the post) will affect the end users and what the reason for existence is for PowerPivot and how it will affect us BI consultants, even with some Real world samples.  A great piece of info i was really waiting for.

A small excerpt:

SQL Server Magazine: How exactly are the services managed? And what does that mean for IT and end users?

Farmer: Let’s start with the story of one of the founding legends of Gemini: We were visiting a customer—a freight company. Somebody whispered in the IT manager’s ear, “You need to come and deal with a problem.” So the IT manager left for about an hour. The problem was that the cargo validation application had failed. What’s the cargo validation application? The IT manager had never heard of it. It turns out that this was a mission-critical application. Every cargo manager in the company was using it. The IT manager didn’t know it existed. Why not? It was an Excel spreadsheet. Somebody had built this application and shared it with other cargo managers. Soon every cargo manager in the organization had it. In fact, they wouldn’t ship a piece of cargo without it. And yet IT didn’t know it existed.

Gemini gives end users the power to build analytic applications, but when they deploy, share, and collaborate, IT can see what’s happening. IT will see the usage of any application that someone in the organization publishes. They’ll understand who published it, what the internals of that application are, what data sources it uses, and who else is using it. They’ll understand that it’s mission-critical. So IT understands that this is something they need to manage and control. But also maybe it’s something they need to secure, validate, and audit.

Read the entire article here: http://www.sqlmag.com/Articles/ArticleID/102613/pg/1/1.html

Microsoft just announced at the Microsoft SharePoint Conference 2009 in Las Vegas this morning that project codename Gemini will ship as “SQL Server PowerPivot for Excel” and “SQL Server PowerPivot for Sharepoint”. And immediately launched the new site http://www.powerpivot.com/.

Loads of information at the IT pro’s at PowerPivot datasheet.

• “SQL Server PowerPivot for Excel” Is the Excel plugin to get new data analysis features on the desktop, business users can load very large data sets into memory and use powerful relational capabilities to create advanced analytics applications.
• “SQL Server PowerPivot for Sharepoint”. Is when you deploy the excel plugin to SharePoint. PowerPivot encapsulates enterprise data to help centralize BI and data management while providing reliable access to it. The PowerPivot administrative dashboard even enables IT departments to monitor and manage shared applications for security, availability and performance. As you can see below:

The thing i’ve been waiting for to surface is the use of Silverlight in BI. Bart Czernicki has released a book describing the combination of BI and Silverlight, a thing I’m very interested in!

What will you learn from investing in this resource?

Covering the entire scope of BI and applying these concepts to Silverlight applications is simply not realistic in one single resource.  Even if I had the option of writing 750 pages or more, important facets of BI would be missed.  Therefore, I decided to focus on the presentation tier of Business Intelligence applications.   For example, I didn’t think it was fair to focus on the data and services tier with these technologies going through a rapid implementation and tooling evolution (RIA Services, WCF REST, Oslo, ADO.NET Data Services 1.5, etc.).  However, Silverlight’s rendering and client processing engine is mature enough to warrant a guide on how to implement client-side BI concepts.  Therefore, topics like visual intelligence, data visualizations, predictive analytics, collective intelligence, interactive tools, parallel computing, working with large data sets, etc., are covered in my book on the presentation tier.

This book has three intended audience segments and their goals:

• Silverlight Developers – Learn how to extend your Silverlight knowledge in real-world applications.  Learn the basics of Business Intelligence 2.0.
• Business Intelligence Professionals - Get a better understanding of how Silverlight can help you overcome some of the challenges to implement simple BI tools.
• Strategic Decision Makers (architects, CIO, technical director, etc.) – Understand if Silverlight is the right platform to deliver BI software.

Read the entire release post here:

Announcing Next Generation Business Intelligence Software with Silverlight

This is one book I will order asap!

Timo Elliott found a great best practices report from TDWI on “how to create and deploy effective metrics” (registration required). The introduction to the document explains the background:

Performance metrics are a powerful tool of organizational change. The adage “What gets measured, gets done,” is true. Companies that define objectives, establish goals, measure progress, reward achievement, and display the results for all to see can turbo-charge productivity and gracefully move an organization in a new direction.

Executives use performance metrics to define and communicate strategic objectives tailored to every individual and role in the organization. Managers use them to identify underperforming individuals or teams and guide them back on track. Employees use performance metrics to focus on what’s important and help them achieve goals defined in their personal performance plans.

But performance metrics are a double-edged sword. The wrong metrics can have unintended consequences: they can wreak havoc on organizational processes, demoralize employees, and undermine productivity and service levels. If the metrics don’t accurately translate the company’s strategy and goals into concrete actions that employees can take on a daily basis, the organization will flounder. Employees will work hard but have nothing to show for their efforts, leaving everyone tired and frustrated. In short, the company will be efficient but ineffective.

The document then goes into to look at lots of different aspects of creating and deploying KPIs and dashboards, a must read for any BI’er.

By accident I heard one of our sales people talk about showing data from a geographical location on a report. I immediately jumped in the discussion and told about the new Spatial datatype of SQL server and the reporting data region Map. Of course when talking to Sales people the immediatly asked for a demo. In this blog post my findings about Spatial data, Reporting map control and Bing maps. My starting point was Robert Brucker’s reporting blogpost, and try to recreate his demo. Had it up and running in no time.

The first thing i had to do was create a Geography data type, since this is the base of the Reporting map control. This was pretty easy since i had some test data with Latitude and Longitude as float in a table. All i had to do to converting these to a geography datatype was:

update Location set GeoPostion = geography::Point(Latitude, Longitude, 4326)

There are several ways of different ways to add point data in SQL Server 2008, i found them at this blog post: http://blog.colinmackay.net/archive/2008/02/07/1812.aspx

Next up was creating some kind of representable chart, i used the steps as described by Robert Brucker to create a report of my data, the one thing that immediatly caught my attention is that the Reporting map control using Bing maps is data aware. The Bing map automatically centers and zooms in at my locations (i used the Person table from Adventure Works to shoot this screenshot, as there are persons from around the globe he centers on the world).  I created a view of the data so i can change its source on a later stage:

Next up was choosing the visualization of the data, i wanted to create a analytical overview so i chose Bubble map:

Since i wanted to show some analytical data i changed the query to point to my Dutch data and added an amount field (which i filled ranomly with values 15 to 2000) using a top 50 of the Netherlands, you can see the data awareness again, the map switched to the Netherlands only.

In the data visualization step i selected the Bubble size property along with a colour scheme to represent my data:

Resulting in the following map in reporting services, so no programming necessary (except the Query of course):

It would be great if users could zoom in on the data .. luckily the data is a layer in reporting services so the data points are reporting objects where you can set properties like an Action:

So i turned my view into a stored procedure with a parameter and using the data awareness of the reporting control i called my own report with a reporting parameter that selected the top 50 in a region (the Id they clicked)

Using the same report for master and detail, this results in the following map when clicked:

Conclusion: Reporting with geographical data has become very very easy and offers some great possibilities to report developers.

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 !

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