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.
Posts Tagged 'Excel'
Reading your #PowerPivot Profiler trace
Published January 30, 2010 Internals Leave a CommentTags: Excel, Troubleshooting
Create a #PowerPivot report filtered by the Top X Users by (Part 2 of 3)
Published January 28, 2010 Calculation Leave a CommentTags: DAX, Excel
This blog posting is a continuation of previous post, Create a #PowerPivot report filtered by the Top X Users by (Part 1 of 3), where I had created an Excel Named Set based on the Top 10 Users by Events as noted in Figure 1. What is handy about using named sets is that it will only render the top 10 rows as opposed to the millions of rows of users this data set has.
Figure 1: Top 10 Users by Events using an Excel Named Set
While it is easy to show the Top 10 users, it is not straightforward to filter by those Top 10 users because Excel Named Sets cannot be used as a filter. Example reports with this type of business logic include “most common hospital departmental systems that the top 10 users are accessing” or “what are the most watched TV programs of the top 10 markets (i.e. cities)”.
Uploading #PowerPivot for Excel workbook using “Save As” vs. SharePoint UI
Published January 6, 2010 Configuration Leave a CommentTags: Excel, SharePoint, Troubleshooting
When you save a PowerPivot for Excel workbook by using the “Save As” function with Excel, this will use the Office Upload Center as its mechanism to save the file. This is different than uploading the file by using the SharePoint UI as the SharePoint UI uses a different mechanism via http to upload the file than the Office Upload Center. The Office Upload Center is included as part of Office 2010 and what is great about this feature is that you can save the file asynchronously. What this means is that you can save the file and then continue to work with your Excel file in the mean time. With the SharePoint UI, once you upload the file it is locked and you cannot work with the file until the upload has been completed. Though, based on current sets of tests, the upload via SharePoint UI is faster than the upload via Office Sync Center so there is the balance between whether you want to work with the file asynchronously or do you want to have faster upload.
To perform an Excel “Save As”, you need only to click on “Save As” within the File option of the Office Ribbon and specify the PowerPivot Gallery of your SharePoint site.
Watch out, your domain might be showing . . .
Published November 29, 2009 Configuration Leave a CommentTags: domain, Excel, SharePoint
By powerpivotgeek (dwickert@microsoft.com), on November 24th, 2009
Several new SharePoint 2010 configuration issues will impacting some PowerPivot sites and I wanted to share them with you. These restrictions are with Excel Services and have to do with the way that Windows authentication is handled, i.e. you have set the Excel Services authentication set to “Windows”, not using Secure Store or “None”. This impacts PowerPivot because Excel Services treats PowerPivot as a data source. The restrictions are not limited to just PowerPivot – they apply across the board for all Excel Services data sources.
Two great #PowerPivot tips from one half of PowerPivot Twins!
Published November 19, 2009 Configuration Leave a CommentTags: Excel, Excel Services, SharePoint
Hats off to Dave Wickert (one half of the PowerPivot Twins!) – he’s been blogging like there’s no tomorrow and it’s getting harder and harder to keep up! Saying this, he’s included on his blog, PowerPivotGeek.com, two great tips for PowerPivot:
1) Ensure when you install Excel 2010 that you also install Office Shared Tools, it contains VSTO which is necessary for the PowerPivot add-in to work properly. You can read more in his posting: And oh, you need this one more thing.
2) When you save your workbooks, save them in cell A1 so that way when your workbook is saved to SharePoint, Excel Services will render the workbook properly centered. For more info, check out his posting: Another tip – always save your workbooks at cell A1.
Enjoy!
Temperature Mashup Demo – Download the Workbook!
Published November 19, 2009 Demo Leave a CommentTags: Excel, Workbook
Don’t forget to download Rob Collie’s Temperature Mashup Demo at PowerPivotPro.com. The workbook provides a great example of how to join two different sets of data (in this case, sales and temperature data) as well as creating complex measures within PowerPivot – i.e. DAX.
And don’t forget that the latest CTP3 DAX reference is now available online at MSDN.
Enjoy!
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.
