PowerPivotGeek? Who is this mystery man?
Click on the icon to find out.

|
By powerpivotgeek (dwickert@microsoft.com), on March 7th, 2010
Ok. Now is is time for you guys to speak up! Here is a little informal poll. What is your favorite PowerPivot feature? I’ll tell you mine and then I’ll let everyone chime in via the post comments. Join in the fun!
My favorite PowerPivot feature isn’t a feature of PowerPivot at all. I love the fact that since we have this ‘document-centric’ view of the data – all of the PowerPivot data is embedded in the workbook itself. This means that all of the build-in SharePoint features automatically apply to PowerPivot as well. Here is a quick list of what comes to mind to me:
- Check in/out. Users can have an collaborative interaction with the data by using check in/out on the embedded workbook file itself.
- An approval workflow. After a check in, or a new posting of the workbook, you can kick off an approval workflow – i.e. you can route the approval around a series of users until the data shows up in the document library.
- Document library custom modifications. With SharePoint you can add your own properties to the file and include that in the document library along with everything else. SharePoint becomes a development platform with the PowerPivot workbooks as the basis for the custom changes. All of this is created and managed by end-users themselves.
- Automatic retention policies. Since the data is just part of the document, you can have business rules that automatically delete the data. For example, you might have a rule like: “items in this document library are automatically deleted after 180 days” (very appropriate for financial data).
- Many of the “data management” activities are not part of the PowerPivot application – but rather end-users do it themselves. If they want to reorganize how the data is stored on their site, then they just copy PowerPivot workbooks to a new place. They don’t have to call up IT – they just do it! If the data no longer applies to their system, they just delete it. The data is the file – the file is the data.
- Wastebasket. SharePoint has a built-in wastebasket feature. If you do delete a file, it isn’t really gone. Until the administrator empties the wastebasket, the data still lives on – and the user can copy it back to their document libraries themselves. Again, since the data is the file – you don’t need any special PowerPivot capabilities to do this.
- Security is all end-user driven. There is no special security settings for PowerPivot. Since the data is the file, we just use a user’s access rights to the file to determine their access rights to the embedded data. If an end-user drops a user’s read permissions, then they cannot access the embedded data. It is that simple. There are no special utilities or settings to configure. Users just use their regular SharePoint capabilities on the PowerPivot workbook.
I could go on and on – but you get the idea. SharePoint adds tremendous value in the PowerPivot world.
So that is my favorite feature, what’s yours? Add a comment and join in!
By powerpivotgeek (dwickert@microsoft.com), 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:
Continue reading: A Peek Inside: Unloading PowerPivot data
By powerpivotgeek (dwickert@microsoft.com), on March 4th, 2010
Recently I had a conversation with one of our excellent beta testers and I want to make sure that everyone was clear about running PowerPivot on Windows 7.
Windows 7 is only supported for PowerPivot for Excel; Windows 7 is not a supported OS for PowerPivot for SharePoint
Continue reading: PowerPivot and Windows 7
By powerpivotgeek (dwickert@microsoft.com), 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.
- You might have installed the wrong version of SharePoint. You might have installed SharePoint Foundation Server instead of SharePoint Server. It turns out that this is very easy to do. It also does not ask you about “Farm” or “Complete” – it automatically installs a standalone SharePoint installation. So, how can you tell the difference (besides one returns an error; and one doesn’t) Here is a trick:
Continue reading: Installing the right version of SharePoint
By powerpivotgeek (dwickert@microsoft.com), 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:
- Turn on SQL Agent.
- The SQL Agent service account needs access to the content in SharePoint, i.e. the workbooks being demo’ed
- Create a job with a schedule that runs every n minutes (I use every 5 minutes) and a single “SQL Server Analysis Services Query” step with the following characteristics:
- Set the “Server” to be the URL to the workbook you are trying to keep loaded
- Enter a command that you know will fail, or if you know one that would be OK, I typically use “SELECT * FROM SANDBOX” (which will fail) – we don’t need the query to run – all we need is a good connection and the embedded data will be loaded.
A valid question might be: “So tell me, Dave, why every 5 minutes? That seems way too often. After all this is a demo system and there isn’t memory pressure, so why running it so often??” Yes, that is true. The other reason for why the workbook data would not be loaded is the inactivity timeout @48 hours for detaching. But that also does not seem to apply. The reason why I use 5 minutes is data refresh. Since I typically demonstrate setting and running a data refresh, this means that new content is always being generated. When new content arrives in SharePoint, the PowerPivot cleaner thread checks every 10 minutes and it will delete unused old databases immediately. Thus you would have the ‘first access’ loading the new content – and with it, the built-in delay would happen. Running the job every 5 minutes means a forced load of the new data quickly. Your mileage may vary.
By powerpivotgeek (dwickert@microsoft.com), on February 24th, 2010
I just posted a new diagnostic guide in the troubleshooting section:
“Diagnostic Guide for Usage and the PowerPivot Management Dashboard” by Ankur Goyal
http://powerpivotgeek.com/troubleshooting/diagnostic-guide-for-usage-and-the-powerpivot-management-dashboard/
Enjoy!
_-_-_ Dave
By powerpivotgeek (dwickert@microsoft.com), on February 24th, 2010
A trick for the client-side folks doing build-to-build reinstallations (or if you wish to clear all of the PowerPivot code from your client): besides uninstalling “Microsoft PowerPivot for Excel” (via control panel Add/Remove/Uninstall Programs, the exact wording varies depending on the OS you are running), you may also have to delete files in:
C:\Program Files\Microsoft Analysis Services, and
C:\Program Files(x86)\Microsoft Analysis Services (if running on an x64 machine)
This is because the Analysis Services OLE DB provider (and its components) are also installed in these folders. They are not removed during uninstall because we don’t know if there are other components that are using them. Thus you must be careful, particularly if you know that there are other dependent products that are using them before you delete the folders. Dependent products should be uninstalled before you delete these files.
By powerpivotgeek (dwickert@microsoft.com), on February 15th, 2010
Recently there have been some discussions on the forums and in other areas concerning how PowerPivot handles nulls. As this has changed between CTP3 (back in November) and the upcoming RTM of the product, I thought that it would be a good posting. Here is the situation: suppose you have a simple calculated measure:
m1 = SUM(c1) / SUM(c2)
If c2 is NULL, then then you get #NUM as a result. No worries, pretty much expected. However, it is difficult to trap when this is happening. For example, with CTP3:
m1 = IF (SUM(c2) = 0, 0, SUM(c1) / SUM(c2))
You can use IFERROR and ISERROR but they are slower and have more overhead and should be avoided if possible:
m1 = IFERROR(( SUM(c1) / SUM(c2) ), 0)
-or-
m1 = IF(ISERROR( SUM(c1) / SUM(c2) ), 0, ( SUM(c1) / SUM(c2) ))
. . . however, regardless, m1 keeps returning #NUM, not 0. In CTP3 PowerPivot does not treat infinity as an error despite #NUM being displayed in Excel. This is effectively what is going on in this example, as NULL is being converted to zero (division by zero then yields infinity). The RTM version of PowerPivot will treat such a scenario as an error and IF/ IFERROR/ ISERROR will behave as expected.
(My thanks to Marius Dumitru, Russell Christopher and Brandon Unger for providing both the examples above and the answer.)
Enjoy.
_-_-_ Dave
By Lee Graber (leegr@microsoft.com), on February 12th, 2010
This is part of my continuing series on debugging SharePoint and PowerPivot. In this case, I do not expect any PowerPivot users to ever encounter this error in the context of PowerPivot. However, the general SharePoint developer who is in the early stages of building and testing his custom solutions might hit this and since so many people on our team have been confused by it, I thought I would throw out some help for other developers. This error is hit when trying to deploy a SharePoint solution package to the farm. It could be an ApplicationServer solution or a WebFrontEnd solution, the important part is that it has an assembly marked to be deployed to the GAC:
<Solution SolutionId=”94BE5F4C-D227-4981-A725-95ABC706364A” DeploymentServerType=”ApplicationServer” ResetWebServer=”TRUE” xmlns=”http://schemas.microsoft.com/sharepoint/”>
<Assemblies>
<Assembly DeploymentTarget=”GlobalAssemblyCache” Location=”MyAssembly.dll” />
</Assemblies>
…
</Solution>
The above solution, when deployed via either stsadm or the new PowerShell commandlets in SharePoint 2010 will attempt to put the assembly “MyAssembly.dll” into the GAC on every ApplicationServer in the farm. There are two possible reasons for this deployment to fail with the above error:
1) The assembly is already in the GAC. For the initial deploy, it appears the SharePoint uses “/i” and not “/if” and so it could fail if the assembly is already there. Most of the time, I find that this is not the issue but it could be for you. You can test if this is the issue by simply removing the version of the dll which is already there. If that is causing it, you need to figure out why you are trying to GAC the same version of the dll more than once.
2) You have not disabled strong name verification. For us … this was always the issue. Signing your assemblies is a time consuming process for a large product and so often you mark your assemblies for delay signing and then only sign “special” builds. In those cases, you need to disable strong name verification in order to add un-signed assemblies to the GAC. The tool for doing this is sn.exe which can be found in your Windows SDK directory. Using the –Vr option you can mark classes of assemblies to be skipped when it comes to verification. The classic command is “sn.exe –Vr *,*” which is indicating that any assembly with any public key token should be skipped. You might also chose to just skip verification for assemblies with your public key token with something like: “sn –Vr *,89845dcd8080cc91”. Whatever method you chose, there are a couple of key things you need to understand to make sure sn has “worked” for you:
- Deploying a solution will deploy the solution to every appropriate machine in your farm. If you install an msi on MachineA in the farm and on that machine run sn.exe, but then deploy a solution and MachineB is also a member of the farm, you had to have run sn.exe on that machine also.
- Sn.exe comes in a 32 and 64 bit flavor. It is difficult to tell which version you are running if someone just gave you the bits in a random folder (in the SDK folder the bits are under “architecturely” marked folder names). If you run the 32 version of sn.exe it will not help you when trying to GAC the binary on the 64-bit SharePoint server. There are a number of ways to try and figure out what is marked for “skipping” using the sn.exe tool, but I like to go directly to the registry because there is very little room for confusion there. Sn.exe is adding entries to the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\StrongName\Verification\ and HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\StrongName\Verification\ hives of the registry for whatever parameters you pass into the call to “sn.exe –Vr”. If you look in your registry and find that you only see an entry under the Wow6432Node, then you have only run the 32 bit version of sn. You will need to find the 64-bit version of sn.exe to get your skip registrations in the appropriate place (you can actually add them manually if you feel confident enough to edit the registry by hand).
- THIS IS THE MOST OFTEN OVERLOOKED STEP – The list of what to skip when it comes to verification is actually cached. Normally, when you run gacutil.exe by hand, the process starts, loads the skip list, verifies, fails / succeeds and shuts down. Hence, if it fails and you then run sn.exe, the next time you run gacutil.exe it will succeed. This is *not* how it works with SharePoint. Solution deployment is done within the context of their timer service. This is an nt service which is not shutdown after a failed / successfull deployment of your solution. Hence, if your solution fails to deploy because of a strong name verification issue, you run “sn –Vr *,*” to enable skipping verification, and then try to deploy your solution again … *it will fail again*. You must recycle the SharePoint 2010 Timer service on every machine on which your solution is going to be deployed (specifically on which you just ran sn.exe) in order to get the skip list reloaded. I have seen people get frustrated and so reboot their machines which will obviously work, but all you really needed to do was recycle the timer service.
Hopefully this will help some SharePoint developers avoid wasting too much time figuring out why this was failing.
HTH
Lee
By Lee Graber (leegr@microsoft.com), on February 8th, 2010
This is one of the two main errors that users could see from Excel Services when using PowerPivot. This is encountered when refreshing PowerPivot data connections or performing an action which requires re-querying the PowerPivot database, such as clicking on a slicer or expanding a node in a pivot table. To debug, some level of understanding of what Excel Services is doing is required. For this, I recommend reading an earlier post on this blog by Dave … http://powerpivotgeek.com/2009/12/11/excel-services-delegation/. In general, this is an add-on to Dave’s post which is a quick summary of how to debug this error.
Continue reading: The data connection uses Windows Authentication and user credentials could not be delegated
|
|
Recent Comments