Data Visualization – The PowerPivot Tool – Part I

Introduction
With Excel 2010 around the corner I decided to make a blog series about visualizing data by using the new PowerPivot tool.

My plan with the series is to present and discuss the PowerPivot. It will also include pointers to other online resources as well as to upcoming books where it’s appropriated.

Anyway, let’s start with some keywords the series will be building around.

What is Data Visualization?
The term Data Visualization is defined as “the study of the visual representation of data” and its main goal is to “communicate information clearly and effectively through graphical means” (Wikipedia).

I have no intention to discuss Data Visualization in more details but why I bring it up here is that the PowerPivot tool’s ultimate goal is to visualize large data sets for decision makers.

What is PowerPivot?
The PowerPivot tool is a free add-in to Excel 2010 and later versions. It’s available in an x86 version as well as in an x64 version. It requires version 2010 or later of Excel. During the beta stage the tool was named Gemini but MSFT changed it to PowerPivot when it was finally released. It should also be mentioned that a version for SharePoint 2010 and later is available, it’s shipped with SharePoint 2010.

Although it’s said that PowerPivot is a new brand tool we can track it back, at least to some degree, to the Data Mining add-in. The Data Mining tool exists in two versions; one version for SQL Server 2005 and one version for SQL Server 2008. The two versions both target Excel 2007. Previously a version for Excel 2002/2003 has been available. If You’re interested to learn more about this tool You can read the excellent guide Introducing the SQL Server 2005 Data Mining Add-ins for Office 2007.

Another important aspect is that MS Query is still available in Excel 2010 so we don’t need to worry that PowerPivot will replace MS Query. If we compare these two tools we can say that the later, i.e. the PowerPivot add-in, is the modern big brother to MS Query as it’s more powerful (can handle very large data sets)  and it’s more sophisticated then MS Query.

For us, who have been around for a while, it does not come as a surprise that Excel with its toolbox can be used as a Business Intelligence (BI) tool. However, MSFT seems to first now recognize Excel as a BI tool because the PowerPivot tool has been released. In other words, the PowerPivot tool can be described as a BI tool.

For a more detailed introduction of the PowerPivot for Excel I recommend to read the following entries in MSDN:

• What is PowerPivot for Excel?
• PowerPivot Features

As a general note to the above online resources:
PowerPivot can work with a great number of external data sources and not only with SQL Server .

I find the following site as the best starting point and it also serves as a portal for the PowerPivot tool:

• PowerPivot-Info.com

In my next post I will show how we can work with the PowerPivot tool in Excel.

Kind regards,
Dennis

Upgrading to SharePoint 2010

Now that I have managed to get a new MSDN subscription I’m about to upgrade my physical testserver with Windows 2008 R2 Server, SQL Server 2008 and SharePoint 2010. Despite all the good installation wizards included in the softwares it requires more knowledge and understanding then just click on the right buttons so to speak.

One of the best online resources that can be found is Critical Path Training who provides some great articles about MOSS. In addition to the mentioned online resources in my blog article  SharePoint – The Start I would recommend to take a closer look into their excellent article SharePoint Server 2010 RTM Virtual Machine Setup Guide. To get access to it You need to create a new account at their site and the article can then be found under the Members section.

Microsoft has recently released the SDK for SharePoint 2010 which can be found at Welcome to the Microsoft SharePoint 2010 SDK. The Microsoft SharePoint Team has also recently published a blog article about available language packs to SharePoint. For more information please see the article Language Offerings for SharePoint 2010 Products.

At present I have no intention to look for new books that cover version 2010. I still have some chapters left in the books I bought the first time I set up the server.

The main reason that drives me to invest time with the SharePoint platform is, of course, the Excel Services. For me it’s still a compliment to develop custom solutions with Excel and with .NET (automation and add-ins).

Do You do any work with SharePoint and in particular with Excel Services? What is Your experience with the Excel Services?

Kind regards,
Dennis

Edit Note:
The same day as this article was published Erika Erhli (MSFT) published a compiled list of online resources at MSDN for SharePoint 2010:  MSDN: Getting Started with SharePoint 2010 and SharePoint 2010 Advanced Developer Training

Andreas Glaser has had the kindness to update his great tutorial for installning SharePoint 2010 with SQL Server 2008 R2 and Windows 2008 R2 Server. For more information please see: Installing SharePoint Server 2010 on Windows Server 2008 R2 and SQL Server 2008 R2 – Part 1: Overview

Excellent Learning Tool for Open XML SDK 2.0

For some weeks ago Microsoft announced that they had launched the final version of Open XML SDK 2.0 for Microsoft Office. With this SDK we can create and manipulate Excel workbooks in code without involving Excel and also without having Excel installed.

What comes as a bonus with SDK 2.0 is the Open XML SDK 2.0 Productivity Tool for Microsoft Office. I was very pleased and surprised to discover that this is a multipurpose tool that allows us to a) generate reflected code, i.e to see the correct code to interact with the Open XML Documents file format, b) comparing source and target files code and b) validate code. We can also via the tool easily get access to the Open XML documentation.

The process to generate reflected code is very simple:
1. Create an Excel workbook and customize it as You want the final product to look like. Save it with the Open XML file format.
2. Open the Productivity Tool and open the created Excel workbook.
3. Navigate to the part of the created Excel workbook you want the code for.
4. Hit the button Reflect Code.
5. Done!

The following screen shot shows the tool in action:

However, we cannot control which language the generated reflected code is presented to us. The output is always in C# so if we want to see the generated VB.NET code we need to convert the generated C# code to VB.NET with a tool like Code Converter from Telerik.

The blog Brian Jones & Zeyad Rajabi: Office Solutions has published an excellent map over available online (MSFT’) resources for Open XML, which can be found at the following URL Zeyad Rajabi’s Open XML SDK Blog Map.

Personally I’m still in the initially phase and up to this date I have only done five smaller Excel jobs that involved manipulation of the Open XML file format. Have You done any work that involves it and if yes what are Your experiences?  

The more I work with it and the more I explore the above tool the more attractive it becomes. The real potential, at least to me, is that with Open XML SDK installed on a server we can generate Excel Reports in a smooth and structural way without having Excel installed. 

Enjoy it!

Kind regards,
Dennis