The WorkerThread Blog

we know stuff so you don't have to

Archive for the ‘Microsoft SharePoint’ Category

Power to the Pivot

Posted by workerthread on November 24, 2010



I’ve been spending a good bit of time with PowerPivot in recent months, primarily as part of a proof of concept exercise for a client.  Like many others I have to say I started looking at it with a high degree of scepticism.  Surely I thought, this thing goes against all of the principles we’ve been trying to drum into users, particularly the importance of building a well designed Unified Dimensional Model with calculated members in our Analysis Service cube – then we get that “one version of the truth” that we’re all after when we build a Business Intelligence solution.  PowerPivot on the other hand is a free add-in for Microsoft Excel 2010 which lets you load very large amounts of data into memory work and with it in a new desktop version of Analysis Services called VertiPaq. So you build your models, including all the calculations, inside of Excel.

The thing is, the big majority of analysts who need to pull together and work with large sets of data often need to do so in response to rapidly changing requirements (and frequently, changing data sources).  And of course they spend most of their day using Microsoft Excel.  Show PowerPivot to someone who spends their time creating complicated Excel VLookups to build reports and pivot tables against data loaded from database extracts and you will quickly see their eyes light up.  And in all of the time I’ve spent working with Microsoft Analysis Services (since the first release in 1998) I’ve never seen as much excitement online about a BI tool as I’ve seen recently for PowerPivot.  Do a search on twitter for the #Powerpivot hashtag to see what I mean.

PowerPivot in Excel 2010

So if you have Excel 2010 you can download the PowerPivot add-in from Microsoft’s PowerPivot site here.  Note that there are two different versions of the download, one each for the 32-bit and 64-bit versions of Office 2010.  Right now most users are likely to be running the 32-bit version I suspect.  There are very good reasons for this – if you install 64-bit Office 2010 you may have problems with existing add-ins which are 32-bit only, and there is also an issue with SharePoint datasheet views with 64-bit Office.  On the other hand, if you are planning to use PowerPivot with very large data sets you will almost certainly need to install the 64-bit version of Office 2010 – as it says in this TechNet Installation Note“the 64-bit version of PowerPivot enables you to work with up to 4GB of data in memory, and the 32-bit version enables you to work with up to 2GB of data in memory”.  There is some guidance from Microsoft on 64-bit Office 2010 usage here.

Once you are up and running, you’re going to be impressed with the performance you get, even on large datasets.  Data can be loaded into PowerPivot from a good variety of sources – SQL Server, Analysis Service cubes, other OLEDB/ODBC data sources, text files, Excel data, also interestingly SQL Server Reporting Services, Atom data feeds and the Azure DataMarket.  Again, this is where lightbulbs start coming on over Excel Users’ heads because PowerPivot lets you create joins between disparate data sources.  So providing there is a matching column, it’s quite easy to create a relationship between line of business data, maybe residing on SQL Server, and other data you might have acquired from an online service such as Azure DataMarket.

To get the best out of PowerPivot, you need to learn how to use Data Analysis Expressions (or DAX for short).  One of the reasons Excel users are likely to warm to PowerPivot more than they have to Analysis Services cubes is that DAX calculations are closer to the type of functions they may already be using in Excel.  That’s not to say DAX doesn’t also require some effort to learn, it definitely does if you want to master it.  DAX has some great date calculation features built-in by the way, so very good for year-to-date, or same-period-last-year type reports.

So what are the downsides when you’re using PowerPivot with Excel?  For me the same issues have cropped up whenever I’ve been shown it to clients.  First, there is no easy way to automate the refreshing of your data from within Excel.  Right now the only option you have is to open the PowerPivot window and manually refresh one or more of your connections.  Because PowerPivot is a separate add-in you can’t automate it via VBA right now either.  If you publish your model to PowerPivot on SharePoint 2010 then you can get the server to schedule refreshes for you but if you don’t have this luxury then right now manual refresh is the only option.  There is an interesting post from Ken Puls on some of the bits he would like in the next release.

Something else that concerns me slightly is the need for governance when defining and using complex DAX calculations in Excel models.  As I’ve already said, a key benefit of the dimensional model, with all of your custom calculations defined in the cube, is that there is a single definition.  So if your company has a complex ratio calculation, or way of defining utilisation percentages or whatever then the possibility that two different analysts use different calculations (and arrive at different results) is significantly reduced.  DAX formulae can get pretty complex so ideally you would want to store “templated” definitions somewhere.   Over on PowerPivot Pro there is an interesting post on how you might use NotePad++ to create and troubleshoot the more complicated formulae.  Perhaps something similar could be used as a reference guide for sharing complex DAX calculations.

PowerPivot in SharePoint 2010

So as I’ve mentioned above, as well as being an add-in for Excel 2010, PowerPivot is also an option for SharePoint 2010 in conjunction with SQL Server 2008 R2.  Once you have PowerPivot running on a server in your SharePoint 2010 farm you can publish your models to “galleries” and your users can view your models in their browser, without even the need for Excel.  Again, a lot of work has gone into the SharePoint bits here, making good use of SilverLight to browse through published documents.  SharePoint administrators can also monitor usage of each model, identifying resource utilisation, which items have been opened most frequently and so on.  This would then be used to see which PowerPivot models might be suitable candidates for re-development as a true dimensional model in an Analysis Services cube.

PowerPivot on SharePoint 2010 can be an expensive proposition though.  You will need SQL Server 2008 R2 Enterprise Edition on the PowerPivot Server.  Your SharePoint 2010 farm will also need Enterprise Client Access licences.  And the server running PowerPivot will need to be pretty substantial (lots of memory, multi-core etc).  Here’s an MSDN document on recommended configurations.  I know we keep hearing the phrase “BI for the Masses” but these masses need to have pretty deep pockets.  Also note that the PowerPivot Analysis Services “VertiPaq” engine will be a separate instance to any existing traditional Analysis Services instance you might be running, almost certainly requiring its own server.

Getting Started

Here is some recommended online reading to get you started:

Loads of useful information on the PowerPivotPro and PowerPivot FAQ sites

Marco Russo’s blog (one of the authors of the book recommendation below)

The DAX articles on Chris Webb’s BI blog

Book Recommendations

If you want an in-depth understanding of how to use PowerPivot and DAX in Excel 2010 I would wholeheartedly recommend PowerPivot for Excel 2010 by Marco Russo and Alberto Ferrari.  Excellent explanations of how it all works, best practices and lots of examples.  You can download sample chapters here.

If you are interested in the SharePoint 2010 side of things, take a look at Professional PowerPivot for Excel and SharePoint which has useful information the PowerPivot architecture and how it’s all put together in a SharePoint farm.

Summing Up

This is already a long post and I haven’t even mentioned the next release of SQL Server (currently code named Denali but my spell checker keeps suggesting “denial”).  From the information recently released understanding how to use PowerPivot is going to stand you in good stead when the new release is available.  Here’s a good overview of the “Denali” roadmap.  I’ll try to follow up this post with news about Denali BI features when more information is available. 

For my own part I’m very excited about the possibilities that PowerPivot opens up.  Given that we are only in version 1 most of what I’ve seen is very impressive and I would definitely recommend taking it for a test drive.  And your Excel “power users” will definitely like what they see.

Let me know what you think – can you see a use for PowerPivot in your organisation?  Is “self service BI” a step in the right direction?

Technorati Tags: ,,,

Posted in Business Intelligence, Microsoft SharePoint, Office, PowerPivot, SQL Server | Comments Off on Power to the Pivot

SharePoint 2010 Project Tasks – Summary Tasks

Posted by workerthread on August 21, 2010

Christophe from PathToSharePoint commented on my previous post, asking “how does SP deal with MS Project summary tasks?”.  I thought I would show what it does here, as it’s quite interesting and illustrates another new feature of the SharePoint 2010 Project Tasks list – the Summary Task content type.

Users of Microsoft Project will be aware that you can easily create summary tasks to act as a container which groups sub-tasks together.  On the Project 2010 ribbon you will see “indent” and “outdent” options – the picture below shows “Indent Task” which will indent selected tasks and make them subtasks of the nearest preceding task at a higher level:

Project 2010 Indent Task

You can see this in action in the sample project below, where the three tasks concerned with mobile development have been indented and made subtasks of Phase 2. 

Project 2010 Summary Tasks - Click to view full size

So, what happens when we sync this with our SharePoint 2010 Project Tasks –

SharePoint 2010 Summary Task - Click to view Full Size

As you can see, I have a new Summary task item for Phase 2, and if  click on the hyperlink to drill down further, I get to see the subtasks:

Summary Task Drilldown - Click to View Full Size

This is all made possible because a SharePoint 2010 Project Task list actually includes a second “summary task” content type:

Summary Task Content Type

Which is actually a folder with appropriate project task metadata – and Project 2010 uses this when synchronising.  Here is list view of the same project task list, which shows the Phase 2 folder more clearly:

SharePoint 2010 All Tasks - Click to view full size

So there you go Christophe – and thanks for all your efforts on PathToSharePoint!

Posted in Microsoft SharePoint, Office, Uncategorized | 16 Comments »

SharePoint 2010 Project Tasks Part 1

Posted by workerthread on June 1, 2010

(A quick update on my posts on SharePoint 2010 and Project tasks: as far as I am aware the SharePoint synchronisation feature is only available on Project Professional 2010, NOT Project Standard).

It’s been a good while since the last post here – I’ve been really busy on client projects, as well as trying to learn as much as possible about SharePoint 2010 now that the released product is available.  I thought I would try to cover some of the things I’ve found out while working with the 2010 SharePoint and Office products in this and future posts, kicking off with how Project Tasks now look and behave.

The Project Tasks List in SharePoint 2007 / WSS 3.0

One of the standard list templates in both SharePoint 2007 and WSS 3.0 is called “Project Tasks”.  You can find it in the “Tracking” section when creating a new list:


It comes with a default “Gantt View” already set up, so that when you add entries to the task list, you get to see them like this:

2007 Gantt - click to view full size

When I show this to clients new to SharePoint they get very enthusiastic about managing project tasks via a web application.  Then they ask about task dependencies – in other words can the tasks be linked so that the Gantt shows how each one is linked to its predecessor or successor tasks – same as you would be able to create if you were using Microsoft Project.  Unfortunately SharePoint 2007 Project Tasks can’t do this “out of the box”.  There are several 3rd party options available, such as SharePoint Task Master from Bamboo Solutions and of course Microsoft’s own Project Server.

Improvements in SharePoint 2010

The Project Tasks list is still there in SharePoint 2010 (all versions, including Foundation) and in the “Tracking” you will see this icon:


In 2010, the first thing you will notice once you create a new list from this template is that the Gantt view has changed so it now looks a lot more like the one you would see with Microsoft Project:

2010 Gantt - click to view full size

And as you’ll notice from the screenshot above, it’s possible to define predecessors for tasks when adding or editing them:


So far so good, but there are a few other things you need to know.  First, updating one of the tasks does not automatically re-schedule its predecessors.  You can consider a SharePoint 2010 Project Tasks list as using manual scheduling, not the clever auto-scheduling you get in Microsoft Project.  Here you can see the duration of the “Define Scope” task has been extended, but nothing happens to the start date of successor tasks:

2010 re-schedule - Click to view full size

The other point to bear in mind is that it’s possible to set as task as it’s own predecessor, without SharePoint giving you any kind of error:


Then you can end up with a task that looks like this on the Gantt

Self Predecessor gantt - Click to view full size

This is something that isn’t permitted in full-blown Microsoft Project, where you will get an error like this if you try it:

project 2010 error - Click to view full size

So overall some nice improvements. It doesn’t stop there though.  Microsoft Project 2010 adds more – namely the ability to synchronise with your SharePoint list.  More on this in part 2….

Posted in Microsoft SharePoint, Office | 4 Comments »