Archive for March, 2010

64-bit verses 32-bit . . . What’s right for you?

By powerpivotgeek (, on March 25th, 2010

Recently we been hit with a rash of inquiries around whether folks should install the 32-bit version of the PowerPivot client, or go with the larger capacity 64-bit version. Office 2010 is the first version of Office to offer a native 64-bit option. In previous releases, you had to install the 32-bit version to run under WOW (Windows-On-Windows) mode. WOW emulates a 32-bit environment under the 64-bit OS. That is cool and all, but you were still limited by the 32-bit address space (2GB of memory). Now with Office 2010 64-bit the memory use is virtually unlimited for a client application.

So where to begin . . . Being a geek, let’s start off with a technical topic. When you are running in a 64-bit process, then you can only use dlls and other executables that are build as native 64-bit applications. Therefore whatever our choice, 32-bit or 64-bit, the ‘bit-ness’ of our approach has to hold for the entire process. For example, if we pick 64-bit, then we need the 64-bit version of Excel 2010, we need the 64-bit versions of any add-ins, such as PowerPivot for Excel, and we need 64-bit versions of any OLE DB providers or ODBC drivers (using the 64-bit version of the OLE DB Provider for ODBC here). So let’s keep that in mind.

Ok, here we go:

Read more…


Collation, Localization and Globalization, oh my!

By powerpivotgeek (, on March 24th, 2010

From time to time we get questions about these three topics. Let’s take them one at a time and see how PowerPivot works under the covers. PowerPivot works on all types of collations, languages and regional settings.

(if you like this kind of ‘internationalization stuff’, read on)

Read more…

A Peek Inside: Unloading PowerPivot data

By powerpivotgeek (, on March 4th, 2010

Ok folks. Here comes a cool one.

As I am sure that everyone is aware by now, one of the most powerful new features of PowerPivot is that we do on-demand loading of embedded data. Well, if you are going to load it, then you had better at least think about unload it at some time. There is probably some law of thermodynamics that applies here.

First rule for deleting data is that we don’t touch data if there are users currently connected to it. But other than that, any databases loaded or cached by the PSS (i.e. their database folder is contained in the SSAS Backup folder tree) are candidates for being unloaded.

There are two ways that databases can be unloaded in PowerPivot:

Read more…

Installing the right version of SharePoint

By powerpivotgeek (, on March 4th, 2010

So you think that you have done everything right, but still SQL setup (in PowerPivot SharePoint integration mode) still reports “You don’t have a ‘Complete’ SharePoint installation.” How can this happen?

  • You might not have followed the installation guide. It tells you to first select “Farm” and then “Complete” on your first two screens after you enter your SharePoint product key. If you are installing on a domain controller, then the only selection that is supported is “Farm” and “Complete”, thus on a domain controller you won’t even be asked – it just defaults to this configuration. They don’t even offer you the possibility of running in an unsupported configuration. Just double check what you asked for. BTW: If you are running a RC0 version of SharePoint there is a known bug that a “Standalone” rather than “Farm” install is done. This is a RC0 only bug and it was fixed in a later build.

Read more…

Keeping a workbook ‘pinned’ in memory

By powerpivotgeek (, on March 2nd, 2010

One of the special considerations that arise when demonstrating PowerPivot is that you would like to ensure the workbooks that you are showing are kept in memory all of the time. PowerPivot does not have a built-in ‘pin’ feature to do this, but there is nice trick that you can use. Here is what I do:

Read more…

Why you may want to use RBS for your #PowerPivot for #SharePoint Farm

By Denny Lee, Ed Campbell

Introducing RBS

So what is RBS and why would I want to use it in order to help my PowerPivot for SharePoint farm?  Well, RBS in this particular case is Remote Blob Store which was introduced as part of one of the SQL Server 2008 Feature Packs.  As of this post, the current version can be found in the SQL Server 2008 R2 November CTP Feature Pack (search SQL Server 2008 R2 Remote Blob Store).  Simply put, remote blob store allows you to put blobs outside of the SQL Server database so you can reduce the performance and manageability impact of storing blobs directly within the database.  For more information about RBS, please refer to the documentation linked below.

Read more…

Diagnostic Guide for Usage and the PowerPivot Management Dashboard

Ankur Goyal from the Analysis Services Test team has written a great diagnostic guide on how to use the PowerPivot Management Dashboard.  For more information, please refer to:


Denny Lee’s Tweets (1/2 of PowerPivot Twins!)


Copyright © 2010 Denny G Lee - All Rights Reserved