The trouble with data…

I was able (thanks to some friends at Microsoft) to download and install the SQL 2008 R2 August CTP and Microsoft Office 2010 “Gemini” add-in…

Gemini is effectively an Excel Add-in…. and appears as an additional tab on the Ribbon…

First I tried to just get some simple stuff together by getting some data from the Access Northwind database..I was taken through a neat little import wizard, selected all the Northwind tables and the result was a report on what had been imported

On returning the data to Gemini (note that we’re not really in Excel here – still in Gemini) we can view existing and (if necessary) create new table relationships within Gemini… This is useful where you start to drag in related data from multiple sources.

I like it…. in no time at all I’d got together a basic sales Pivot Table with a couple of slicers

Something to keep an eye on as we move towards what is pretty likely to be Office 2010 by all accounts…

The Microsoft Office 2010 IT Blog

SQL Server Kilimanjaro and Project Madison

This week the BI Conference has kicked off with an announcement!  There will be a BI focused release of SQL Server code named Kilimanjaro which is expected H1 2010 with CTPs available within the next 12 months.  Kilimanjaro is focused on "People Ready BI".  
A key component of Kilimanjaro is "Project Gemini", this is all about self-service BI using Excel.  The vision behind Gemini is to free IT resources by giving business intelligence analysis and reporting capabilities to the end-users without IT losing control.

Is this where Microsoft finally fill the gap left when they shoe-horned some parts of Proclarity in to PPS, leaving themselves without a great stand-alone OLAP client tool? Let’s hope so… I am a little uncertain as to the direction that they are taking BI in. As a once frustrated end-user, I can see how the masses of information workers will love the capabilities. I can also see that this may well lead to a huge increase in unstructured an potentially conflicting information if not managed very carefully.

At the same Microsoft will be releasing "Project Madison", this is the integration of Datallegro which will offer you an easy scale out solution to support data warehouses of up to 100s of terabytes.

All in all very exciting news and I will keep you up-to-date whenever I learn more. I am certainly hoping for more information on the management of the data, how this fits with the overall DW framework, and mysterious lack of information to date on PerformancePoint2…

This looks interesting …. see the link here 

Panorama Pivot Table for Google Docs is an interactive table that automatically extracts, organizes, and summarizes your data. You can use this report to analyze the data, make comparisons, detect patterns and relationships, and discover trends. In just a few steps any users can find answers such as the best selling region, and best selling product or analyze results of an academic research. With Panorama Pivot Tables for Google Spreadsheets, you can interact with the data and look at it in various ways in a simple drag and drop environment.

The Pivot Table for Google Docs is developed by Panorama, who were, interestingly enough, the original inventors of Microsoft Analysis Services OLAP (Online Analytic Processing) engine. So now, part of Panorama code will be inside two of the biggest software companies in the world!

With this new feature, every Google spreadsheet user will have access to powerful OLAP, as a free BI SaaS add-on to Google Docs. In my opinion – a very wise move by Google to continue to push Google Docs into enterprises.

In order to compete with Excel, among many other features, Google spreadsheets lacked specific business functionality needed to analyze vast amounts of data stored in these spreadsheets. With Panorama free add-on Google spreadsheets, users can now take advantage of this lightweight – but still very respectable and powerful – OLAP engine. Yes, out of the box native pivot tables in spreadsheets can do basic analysis on rows and columns of data, but Panorama takes it much farther with more powerful OLAP functions like (I don’t actually know yet what subset of full Panorama OLAP functionality is available as Google add-on):

• Drill up/down
• Drill across / Drill through / Drill anywhere
• Allocations
• Combine/merge/split dimensions
• Exception handling and conditional formatting

 John Walkenbach found this buried in the Excel 2007 help files. Nice to see that some humour can still get outside of those large corporates….

 ;)

Microsoft Excel : Common Questions Around Excel 2007 OLAP PivotTables

Interesting article over at the Excel Team Blog by Allan Folting, a program manager who works on the Excel team.

I have gotten lots of questions from customers around specific functionality in Microsoft Excel 2007 PivotTables that is missing when connected to Microsoft SQL Server 2005 Analysis Services Service Pack 2 cubes:

1. Filtering individual calculated members
2. Showing non-visual totals when filtering

I have also gotten lots of requests for the ability to add calculations to OLAP PivotTables using the object model, specifically:

1. Adding calculated members/measures
2. Adding named sets

Today I’ll discuss these topics and outline current ways and workarounds to address this.

A new Excel User Group site founded by UK Excel MVP Nick Hodge.

It’s looking busy despite only being a few days old… 

Excel User Group