which server's RAM will be loaded with PowerPivot data?

Question

Hi.  We have one physical server where sharepoint runs.   A different one where our olap data resides.   We're having some success using PowerPivot on the sharepoint server to source powerview reports.    My understanding is that the plumbing involves a file (BISM?) that is "exported" to the sharepoint server. 

If our powerpivot BISM references the olap engine on the other server, into which server's ram will the new technologies attempt to load all data into memory?   What triggers the data to be loaded into memory?   And what triggers that memory to be released?  

Answer 1

Hi db042188,

It will load PowerPivot data into server memory. For more information, please refer to the following article about Configuring Usage Data Collection:
http://technet.microsoft.com/en-us/library/ee210659(v=sql.105).aspx

Regards,
Bin Long

---

Bin Long

TechNet Community Support

Answer 2

Thx Bin.  That article seems to be discussing usage data, not the actual pivoted data.

I've been told that PowerPivot loads the actual pivoted data into server memory (RAM) to achieve maximum performance.   If that is true, I'm wondering which server, the sharepoint server or the db server?

I'm also wondering what triggers its decision to do so (load the data) and what triggers its decison to release those memory resources. 

Answer 3

Hello db042188,

It is my understanding, your current Sharepoint server points to an instance of ssas/PowerPivot.  This was done thru a PowerPivot configuration tool.   It is on the server where that ssas/PowerPivot  instance runs that the memory resources will be consumed.

So while Sharepoint is a prerequisite to installing and running an ssas/PowerPivot instance on any machine, your current Sharepoint server can point to another Sharepoint server for its default  ssas/PowerPivot instance.   It all depends on how you want to split up the responsibilities.

If your default ssas/PowerPivot instance runs on the same Sharepoint server where you are running PowerView, then memory will be consumed on that machine.      

---

Answer 4

Thx SSDBREADER.   What event(s) trigger the consumption and release of those RAM resources on the server hosting the ssas/PowerPivot instance?    

And I'm kind of disappointed that MS is going to make me install Sharepoint on servers whose only purpose is to serve up database info thru PowerPivot.   It seems wasteful and unnecessary. 

Answer 5

Hi db042188. As soon as you create (or change or upload to your current sharepoint server) a power pivot document, it will be persisted on the appropriate ssas/powerpivot instance as a cube (similar to tabular mode) workspace.

So, whenever something (Sharepoint, PowerView etc) opens or closes the PowerPivot document, RAM resources are consumed or released on the server hosting the ssas/PowerPivot instance.

---

Answer 6

Thx this all pretty interesting.  One more thing I'll throw out there before the rest of the community responds is this:

It is my understanding that a BISM file can be created and introduced into the plumbing between PowerView and PowerPivot.  And one way (maybe the only way) to create the file is from a sharepoint server that already has the PowerPivot document uploaded.  

I'm guessing that if the BISM file is created on our original sharepoint server, the sharepoint server's config setting for the ssas/PowerPivot instance location holds true.   And similarly even if the BISM file was created elsewhere and uploaded to our original sharepoint server, our original sharepoint server's config trumps any settings that may have been in effect on the server where the BISM file was originally created.     

Answer 7

one of my peers believes powerpivot can work on a machine that is not a sharepoint machine as long as the user expects nothing more than the ability to pivot from within the powerpivot/excel doc.  

But he himself admitted that he was fuzzy on whether or not a powerpivot/ssas instance would even get used on such a machine when a powerpivot doc is opened there.   And what (if any) RAM's involvement would be.