Archive for April, 2010

PowerPivot Technical Diagram: PowerPivot Client/Server Architecture

Because PowerPivot for Excel and PowerPivot for SharePoint involve many components from SQL Server 2008 R2 Analysis Services, Office 2010, and SharePoint 2010, this poster contains all of the key components that make up PowerPivot in one view. This view includes nearly all of the logical architecture components and illustrates how these componets work together.

Included in this diagram are the components for:

  • PowerPivot for Excel
  • PowerPivot for SharePoint
  • Browser-Based Clients and their connection to PowerPivot
  • Data Import and Data Providers in relation to PowerPivot
  • Analysis Services Clients and their ability to connect to PowerPivot
  • Timer Jobs, Health and Usage Data Collection in relation to PowerPivot

For more information, please go to the reference site: PowerPivot Technical Diagram: PowerPivot Client/Server Architecture


PowerPivot and data feeds . . .

Great video by John Hancock on PowerPivot and data feeds

PowerPivot and data feeds . . ..

Howto: Get more flexibility when importing data from text files

By powerpivotgeek (, on April 27th, 2010

Question: By default PowerPivot has two limitations when working with text files (using the Office ACE OLE DB provider): (1) will only import data from files with an extension of .txt, .tab, and .csv; and (2) it will only recognize tab, comma, semicolon, space, colon, and vertical bar and column delimiters. Is there a way to change PowerPivot’s configuration so that it will recognize other file extensions or column delimiters?

Read more…

Querying data within the PowerPivot Excel client add-in

By powerpivotgeek (, on April 27th, 2010

Recently we have been seeing some users complain about how the PowerPivot Excel client add-in deals with queries. There are three limitations that you need deal with when working with SQL (TSQL, PL/SQL, or whatever) and stored procedures:

Continue reading Querying data within the PowerPivot Excel client add-in

Why PowerPivot requires ‘classic-mode’ web applications

By powerpivotgeek (, on April 22nd, 2010

SharePoint 2010 has a new ‘claims-based’ authentication system that allows you to use federated identities with SharePoint. And there are certainly some customers that are excited to start playing around with this capability. That is neat and cool and all . . . but that isn’t the major reason why folks should be getting excited about ‘claims’. It is just a side-effect. The real reason why SharePoint uses claims is to bypass the Kerberos requirement within the farm. This is a huge benefit for SharePoint. In SharePoint 2007 once you grow beyond a single machine, then you must configure Kerberos between all of the servers – this quickly becomes a huge problem and limits both SharePoint adoption and its growth because many customers do not have the infrastructure needed to run Kerberos. But before you get too carried away with claims, particularly with PowerPivot, you have to remember two things: (a) PowerPivot only support Windows users and (b) we don’t support claims integration down to the client. The first one is easy to understand as SSAS only supports Windows credentials – and Excel Services uses that to establish the Windows identity for the connection. Having claims down to the client means that the user can perform one login to SharePoint – and have it be remembered for all future logins.

Continue reading: Why PowerPivot requires ‘classic-mode’ web applications

Howto: Write to the ULS

By powerpivotgeek (, on April 14th, 2010

As more and more folks start to get into PowerPivot and SharePoint, there is a need to programmatically write entries into the ULS. Since all of PowerPivot and all of SharePoint share this common logging infrastructure, why not also include log entries from your own processes. Here is some sample code for doing that:

First via PowerShell:

Continue reading Howto: Write to the ULS

Giving remote users rights to log on to your domain controller

By powerpivotgeek (, on April 7th, 2010

For all of you that are running a combined all-in-1 system, i.e. domain controller, SharePoint and all of PowerPivot (desktop + server), you will notice that if you are trying to debug with non-administrator accounts that you can no longer remote desktop on to your machine. This is because by default only administrators are allowed to remote desktop onto a domain controller. To allow all Remote Desktop users that right:

  1. Click on Start and type “gpedit.msc” into the Start Search box
  2. Navigate to “Computer Configuration – Windows Settings – Security Settings – Local Policies – User rights Assignment”
  3. Click on “Allow log on through Terminal Services”
  4. You will notice that only Administrators are listed. Add “Remote Desktop Users”

Continue reading: Giving remote users rights to log on to your domain controller

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


Copyright © 2010 Denny G Lee - All Rights Reserved