Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Archive for June 21st, 2010

Where did my PowerPivot Excel 2010 tab go?

Posted by denglishbi on June 21, 2010

The other day I launched Excel 2010 to do a quick calculation and changed my mind.  By changing my mind I didn’t mean that I didn’t want to perform the calculation anymore, just decided I didn’t want to wait for Excel 2010 to load.  The reason that it takes a while now to load is the additional add-ins that I have installed which includes PowerPivot.  As long as theses are activated they need to be loaded each time.  Needless to say I am somewhat impatient and decided I would simply use another application to perform the calculation so I cancelled Excel during the load process.

image

It is an option, not sure if it is the right one that you want to pick, but needless to say this is what I went with and this caused some other message boxes to be displayed.  And at this point I simply pick the option to close or cancel because once again I am a bit rather impatient sometimes.

image

image

So once that was done I open up my handy calculator and do the original calculation that I wanted.  Later on I go ahead and open up Excel 2010 because I want to actually do some PowerPivot work.  Well…where did my PowerPivot tab go?

image

What did I do?  Oh no, I lost my PowerPivot.  Did I corrupt it?  Do I need to re-install the add-in for Excel?  Help!  I decided to do a quick Bing search on the issue and I come across a posting by the one and only Dave Wickert, PowerPivotGeek, PowerPivot for Excel add-in is not coming up in Excel.  This tells me that maybe I need to activate the COM Add-in.  Well the only thing it doesn’t tell me is where to do that…minor detail.  First I need to find where the add-ins are even displayed.

  1. Click File—>Options and then select the Add-Ins page
  2. The in the Add-Ins page from the Manage drop down list select COM Add-ins and click the Go… button

image

    3.   Now check the box next to PowerPivot for Excel and click the OK button

image

Now the PowerPivot tab is back in the Excel 2010 Ribbon and you are back in business. 

image

I still don’t like having to wait for the PowerPivot add-in to load each time because I am not actually using PowerPivot every single time when I open Excel and as I have stated, I can tend to be a tad on the impatient side sometimes.  It would be nice to simply have a button that I could toggle to have this feature loaded on an as needed basis.  Now I will tend to shy away from using Excel just because of the load time.  I will look to leverage other applications like the calculator and the Internet more to handle the basic calculations that I used to use Excel 2007 for.

Here are a couple of additional resources for troubleshooting PowerPivot issues (in addition to the one I mentioned above):

Advertisements

Posted in PowerPivot | Tagged: | 12 Comments »

PowerPivot Memory Error – Not Enough Storage

Posted by denglishbi on June 21, 2010

I just got the Professional Microsoft PowerPivot for Excel and SharePoint book and I started working through the tutorials.  Well right out of the gate I received an error message – Memory error: Allocation failure : Not enough storage is available to process this command. . The operation has been cancelled.  My initial reaction was to check the hard drive space.  I had around 15GB of space available, so that wasn’t the issue.  I then checked my available memory (RAM).  And I had just about nothing left.  I went ahead and shutdown every service and application that was not needed, along with restarting my SQL Server database service, so that I just had Excel open.  At this point I had 1.6GB of RAM free and tried to reload the table, FactSalesQuota from the new Contoso Retail DW database, that I was unsuccessfully able to load initially.  Still no luck –

image

I was baffled.  Initially I was able to load the required dimension tables, FactInventory (over 8MM rows), FactSales (over 3MM rows), but I couldn’t load the FactSalesQuota (over 7MM rows) data.  I found it odd that the sales quota data was double the sales fact table, but i believe this is because of the scenario dimension.  Anyway, I was try to figure out what to do next.  I should rewind a bit and state that I am run the 32-bit (x86) version of Excel 2010 and PowerPivot add-in.  The reason for this was because it was the easiest upgrade path for me from Office 2007 since I didn’t want to uninstall the 32-bit version of Office 2007 in order to install the 64-bit version of Office 2010.

I should also mention that when I initially loaded all of my fact tables that were required for the first tutorial I had removed the ETLLoadID, LoadDate, and UpdateDate fields since these would not be needed for analysis and didn’t want them to consume memory and space.

image

So now that I was stuck in a rock and a hard place because of my 32-bit limitation I reviewed the fact table to see if I could possibly remove some unneeded data that was possibly outside the related fact sales information, but it was the same range of dates.  Then I check the columns in the table and realized that there was an identity field setup on the table.  Well I don’t need this column for analysis or relationships, so I went ahead and removed this column as well during the import process to load the data into PowerPivot.  Plus this column is unique per record, so no compression would be performed during import (I would recommend removing the other fact table identity columns as well).

image

And this time…success!  I was able to import an additional 7.5MM rows of data into PowerPivot.

image

After this import my Excel file was hovering around 700MB of RAM consumption.

image

Now I was off to the races in my 32-bit environment.  I would highly recommend going with Office 2010 64-bit at least for Excel if you are going to be doing anything with PowerPivot – at least this way I can experience what other users will go through and be able to help troubleshoot their issues.  I will have more posts coming out shortly related to the tutorials and my experiences and feedback in regards to PowerPivot, so stay tuned.  For more information in regards to PowerPivot memory consumption issues check out the following additional links:

Posted in PowerPivot | Tagged: | 4 Comments »