The other day we were doing some work with PowerPivot, we needed to union multiple excel files, without manual intervention or an SSIS integration, to create a single data source. After finding the following post; http://www.contextures.com/PowerPivot-Identical-Excel-Files.html, that task became pretty easy.
One really interesting thing that I learned after trying to select excel columns where a period was present, ie. ‘column.name’, is that you need to replace the ‘.’ with a ‘#’ for the query to work. The following statement
Select [Sheet1$].[Column.Name] from test.xlsx.[Sheet1$] would be replaced with Select [Sheet1$].[Column#Name] from test.xlsx.[Sheet1$]
The other day I faced an issue with a client application not being able to display the results of an MDX Expression in scientific notation. It turns out that the offending cells were evaluated as part of a scope statement and the measure formatting was not being respected. To solve this issue I added the Format_String property on ‘this’ as follows
Scope (Measure);
this = x*y;
Format_String(this) = “#.00″
End Scope;
Scientific notation gone, client application happy
I was trying to install PowerPivot for SharePoint the other day in an existing SharePoint farm and it kept failing with the following error:
“cannot load file or assembly ‘Microsoft.AnalysisServices.SharePoint.Intergration, Version = 10.0.0.0, Culture=neutral. PublicKeyToken= 89845dcd8080cc91′ or one of its dependencies. The system cannot find the file specified.”
It turns out that the Installation Configuration .ini file stores a port number for the Farms SharePoint Central Admin site. Not sure how it comes up with the port number, but modifying the entry in the .ini to correspond to your existing Central Admin site, prior to completing the istall to seems to solve the issue.
The ini file is found at C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\YYYYMMDD_1234567\ConfigurationFile.ini
