By powerpivotgeek (dwickert@microsoft.com), on February 6th, 2010
Sometimes when you look at the timer jobs associated with the PowerPivot service application you see these weird entries:
So where do they come from and what do they do?
Archive for the 'Internals' Category
Memory Considerations in PowerPivot
Published February 14, 2010 Internals , Perspectives Leave a CommentTags: memory
By powerpivotgeek (dwickert@microsoft.com), on February 3rd, 2010
In scanning through some recent discussions on the PowerPivot forums, I noticed this very nice posting by Marco Russo (http://sqlblog.com/blogs/marco_russo/archive/2010/01/26/memory-considerations-about-powerpivot-for-excel.aspx) . I strongly recommend it.
The only additional point I would like to make is that the RTM version of PowerPivot has an additional check on the in-memory database when saving to disk.
Reading your #PowerPivot Profiler trace
Published January 30, 2010 Internals Leave a CommentTags: Excel, Troubleshooting
To help troubleshoot your PowerPivot for Excel workbook, you can click on Settings within the PowerPivot selection in the Office Ribbon which will bring up the Support & Diagnostics tab within the PowerPivot Options & Diagnostics dialog. Click on the checkbox next to “Client tracing is enabled” and after opening the PowerPivot window, you will have a trace recorded on your desktop.
Could not load type ‘Microsoft.AnalysisServices.SharePoint.Integration.ReportGalleryView’.
Published January 20, 2010 Configuration , Internals Leave a CommentTags: SharePoint, Troubleshooting
By Lee Graber (leegr@microsoft.com), on January 20th, 2010
A number of people have hit this issue and so it deserves a quick walkthrough of why it (unfortunately) can happen and how you fix it.
The error occurs when you try to select a Document Library of type PowerPivot Gallery (sometimes referred to as Report Gallery). After installing Power Pivot for SharePoint, all new site collections you create have the option to create a new document library of this type and if you use the PowerPivot Site template when creating the site collection, we automatically add a library of this type (that is the main thing we do in this template).
So i requested a PowerPivot workbook from SharePoint, what happens?
Published January 12, 2010 Configuration , Internals Leave a CommentTags: Excel Services, SharePoint
In my previous blog posts we made use of a published PowerPivot workbook to use a datasource. In this post we are going to discover what goes on under the hood when you call a PowerPivot workbook on a SharePoint 2010 server.
Note from Denny: While Kasper has his own excellent blog, I thought it would be nice to reference his blog posting at PowerPivotTwins.com since its a great quick read to explain what happens. Enjoy!
Troubleshooting #PowerPivot Excel Services connectivity
Published November 18, 2009 Installation , Internals Leave a CommentTags: Connectivity, Data Refresh, Excel Services, SharePoint, Troubleshooting
You’re on your way to PowerPivot for SharePoint functionality – you’ve uploaded your PowerPivot for Excel workbook to your SharePoint PowerPivot Gallery. You view the thumbnails of your report and they look nice.
From the thumbnail, you click on the report you want to see, and the report renders nicely.
But then you click on a slicer, and then all of a sudden you get an error like the one below.
What can you do?
Steps taken during a #PowerPivot data refresh
Published November 18, 2009 Internals Leave a CommentTags: Data Refresh, SharePoint
In this posting we will take a more detailed technical look at how the data refresh facility works and the steps that it takes to accomplish a data refresh cycle. Rather than starting with the “Manage data refresh” page, we will assume that you know how to setup a schedule – in this posting, we will take a deep dive into the cycle itself.
What steps are taken when the data is refreshed?
Now that you have configured your schedule(s) for the workbook, let’s take a step back and examine more closely what data refresh actually means. I think that it is valuable to understand, at some basic level, exactly what the system is going to do on your behalf at 2am in the morning. When a job actually run, the data refresh facility goes through the following steps:
A Peek Inside #PowerPivot: The client architecture
Published November 18, 2009 Internals Leave a CommentTags: Excel
Well, I have a fun one today. We get asked all of the time about how the PowerPivot add-in works. Particularly how it relates to the SSAS components that BI Pros are used to, e.g. making connections to SSAS. As I am getting ready to write an architecture overview blog posting for the PowerPivot team blog, I decided to take the client architecture out for a spin and attempt a more detailed drilldown here . . .
Here is a block diagram of the PowerPivot client component architecture
Understanding why an Excel PowerPivot workbook is so large
Published November 7, 2009 Internals 3 CommentsTags: Excel, Workbook
During my and Dave Wickert’s SQLPASS session (SQLCAT: A Preview to PowerPivot Best Practices), we had shown to the audience how to view and better understand the PowerPivot database file structure within the Excel workbook. First of all, I’d like to give credit where credit is due – and the thanks really should go to Ashvini Sharma – Power Pivot, Analysis Services, and Integration Services Dev MasterMind. So while the presentation piece is ours – the actual knowledge and know how is his 
.
Saying this, what do we mean by all of this?
For starters, recall that an Excel utilizing the PowerPivot add-in actually contains the database within the Excel workbook. For more information on this, please refer to For Excel PowerPivot, the database is IN the workbook.
But now that you know that the database is IN the workbook, how can you find out why the workbook is so large as my esteemed PowerPivot twitter @VidasM recently inquired?
Well, if you recall from the presentation or blog post, when Excel utilizes PowerPivot, it makes use of the VertiPaq database. While the database is primarily in-memory, it will have an on-disk structure for it cache against before finally saving itself into the workbook itself (when saved and no longer in use). To find this temporary cache folder,
-
Open up an Excel PowerPivot workbook
-
Go to your %TEMP% folder (e.g. c:\Users\MyName\AppData\Local\Temp) and find the IMBI_<GUID> VertiPaq_<GUID> folder. If there are multiple IMBI_<GUID> VertiPaq_<GUID> folders, find the most recent one
-
When you open up this folder, for those of you who work regularly with Analysis Services, this file structure will be very familiar as it is very similar to your native Analysis Services data folder structure.
-
Within the [Search Box] (upper right hand corner of your Windows Explorer for those using Windows 7) and type *.* and sort by size.
-
By doing so, you can now identify which IDF and dictionary files exist and which files are larger which correspond to the tables and columns you have imported.
While we have yet to develop best practices, the basic principal here is by doing this, you will be able to find the tables and columns that are taking up alot of space within your Excel PowerPivot workbook and can potentially remove any columns that are not required.
For Excel PowerPivot, the database is IN the workbook
Published November 7, 2009 Internals 5 CommentsTags: Database, Excel, VertiPaq
Concerning Excel PowerPivot, the question is often asked how is it possible for Excel to now handle tens or hundreds of millions of rows – the way native Excel handles tens or hundreds of thousands of rows.
How you ask?
Well, the reason why is because Excel (with the PowerPivot add-in) is using the new In-Memory BI (IMBI) database. It utilizes a column-based store which has been seen academically and empirically to have some interesting characteristics within BI scenarios…namely, it can calculate many (but not all) BI aggregate very quickly. Because it is in-memory, it does not utilize disk I/O and performs all of its scans in-memory – i.e. much faster because you do not have disk contention. As well, it has a great compression algorithm in order to significantly reduce the size of the overall data stored. By the way, this new database architecture is called VertiPaq for all those interested in the trivia!
So while Vertipaq is really cool, what does this have to do with Excel PowerPivot? Well, the reason is because the PowerPivot database is actually in the workbook itself. To prove it, here’s what you can do:
-
Take your Excel PowerPivot workbook, which you’ll notice is typically much larger than your typical native Excel workbooks
-
Copy the Excel file and rename it using a .zip extension instead of a .xlsx. Note, the reason why is because .xlsx is actually XML
-
When you open up the .zip file, you’ll notice a lot of various folders within it (as noted, it’s all XML).
-
Open up the xl folder, and then you’ll notice the customData folder. When you navigate to it, you’ll notice the item1.data file which contains the bulk of the size of the Excel file
This item1.data file is your PowerPivot database stored right IN the Excel file.
