PowerPivot Twins!

Howto: Get more flexibility when importing data from text files

Published August 18, 2010 Configuration , Internals Leave a Comment
Tags: Data Source, Import

By powerpivotgeek (dwickert@microsoft.com), on August 18th, 2010

Question: By default PowerPivot has two limitations when working with text files (using the Office ACE OLE DB provider): (1) will only import data from files with an extension of .txt, .tab, and .csv; and (2) it will only recognize tab, comma, semicolon, space, colon, and vertical bar and column delimiters. Is there a way to change PowerPivot’s configuration so that it will recognize other file extensions or column delimiters?

To continue reading, please click through to Howto: Get more flexibility when importing data from text files

PowerPivot Technical Diagram: PowerPivot Security Architecture

Published August 18, 2010 Configuration , Internals Leave a Comment
Tags: Excel, Excel Services, Connectivity, Troubleshooting, Configuration, Security

Just in case you didn’t already know, we just uploaded the PowerPivot Security Architecture technical diagram on to sqlcat.com / MSDN. To get access to the PDF, XPS, and/or VSD files, please click through to: http://sqlcat.com/whitepapers/archive/2010/08/17/powerpivot-technical-diagram-powerpivot-security-architecture.aspx

PowerPivot Security Architecture

Enjoy!

[Note, this is cross posted from dennyglee.com in its entirety]

Working with the 2010 Office ACE provider

Published August 18, 2010 Configuration , Internals Leave a Comment
Tags: ACE, FAQ, SharePoint

By powerpivotgeek (dwickert@microsoft.com), on August 18th, 2010

Ok. I’ve promised some best practices – here is my first one.

What is the 2010 Office ACE provider and why is this important to PowerPivot? The 2010 Office System Driver for Data Connectivity Components (aka the Office ACE provider) is a OLE DB provider that can be used to read data from and write data to Office 2010 system files such as Microsoft Access Beta (mdb and accdb) files and Microsoft Excel 2010 (xls, xlsx, and xlsb) files. You can also use it to access text files.

Click through to continue reading Working with the 2010 Office ACE provider

What is a “snapshot” when you enable client-side tracing

Published August 2, 2010 Configuration , Internals Leave a Comment
Tags: Troubleshooting

By powerpivotgeek (dwickert@hotmail.com), on August 2nd, 2010

In getting ready for a recent internal presentation on PowerPivot, I was asked what this button actually does . . . (see below)

The primary purpose for the settings page is to setup diagnostic tracing.

Continue Reading: What is a “snapshot” when you enable client-side tracing

Don’t forget your Central Admin when creating AAM entries

Published August 2, 2010 Configuration Leave a Comment
Tags: Configuration, SharePoint

By powerpivotgeek (dwickert@hotmail.com), on August 2nd, 2010

(my thanks to Dan English http://denglishbi.spaces.live.com/ for the solution in this post)

Dan’s system failed trying to bring up the Workbook Activity or Server Health workbooks as part of the PowerPivot Management Dashboard in Central Admin. The error was: “The file that you selected could not be found. Check the spelling of the file name and verify that the location is correct”. The file was there; he could be seen it using WebDAV “\\ServerName@CAPort#\PowerPivot Management\<guid for service application>\1033\Workbook Activity.xlsx” or the UI:

Please click through to continue reading Don’t forget your Central Admin when creating AAM entries

If running on Windows Server, make sure you have ‘Desktop Experience’ feature turned on

Published July 12, 2010 Configuration , Installation Leave a Comment
Tags: Configuration

By powerpivotgeek (dwickert@microsoft.com), on July 12th, 2010

Oh the joys of SharePoint again. I just responded to an internal posting where someone was raising the issue that “Path not found” errors were being reported when Excel Desktop tried to publish a workbook to SharePoint. Again, this isn’t PowerPivot directly, but it can be reported as a PowerPivot problem if the workbook contains PowerPivot data.

Continue Reading: If running on Windows Server, make sure you have ‘Desktop Experience’ feature turned on

Using a SharePoint list as a data source

Published July 12, 2010 Configuration , Installation Leave a Comment
Tags: Data Source

By powerpivotgeek (dwickert@microsoft.com), on July 12th, 2010

So . . . here I am playing around with PowerPivot to get ready for a demo. As this is a group from the SharePoint dev team (actually SharePoint Online), I wanted to include some cool SharePoint functionality into the demo. Trying to be cool and ‘wow’ them, I decide to use a SharePoint list as a data source. I want to show the data mashup capabilities of PowerPivot so I have the bulk of my demo come from the Contoso sample database (the three product catalog tables) and the 4 million row Sales Fact table.

I create my SP list by extracting the 11 distinct manufacturers – and I assign them to a ‘shipper’ that I made up.

Continue reading: Using a SharePoint list as a data source

Incoming emails to a PowerPivot Gallery

Published July 12, 2010 Internals Leave a Comment
Tags: SharePoint, Troubleshooting

From time to time you run into something on SharePoint that is a known SharePoint ‘issue’, but you wouldn’t necessarily see PowerPivot being impacted. After all SharePoint is this huge juggernaut of a product and PowerPivot is just this one small little app in the corner. Well, here is one that I ran into today. We had a bug report that the incoming email setup for a PowerPivot Gallery was missing the ‘incoming emails’ link –> after some searching, I found this interesting posting:

Click through to continue reading.

PowerPivot, you are so insensitive! (case that is)

Published June 18, 2010 Internals , Perspectives Leave a Comment
Tags: Connectivity, Data Source, Excel

June 18, 2010 by dennyglee

Working with my compatriots Ayad Shammout (@aashammout) and Peter Myers, we had found out that PowerPivot at times can be really insensitive…in terms of case that is. That is by default, the collation for PowerPivot is case-insensitive.

For example, if you import the table below:

Continue reading: PowerPivot, you are so insensitive! (case that is)

Client: What happens if I install the wrong ‘bitness’?

Published June 17, 2010 Configuration , Installation Leave a Comment
Tags: Configuration, Excel, FAQ, Troubleshooting

By powerpivotgeek (dwickert@microsoft.com), on June 17th, 2010

Recently ran into this situation: Customer installed the wrong PowerPivot bits on their machine. They were running on a 64-bit OS with Office 2010 x64, but installed PowerPivot for Excel 32-bit. Everything installed OK, but when they clicked on the PowerPivot tab in Excel; then the PowerPivot Window they received the following error: