Power BI On Demand Webinar

Once again it has been ages between posts and this one is a shameless plug for the company I work for, Dimensional Strategies.  We have created a great On Demand webinar for Power BI,  here’s the link and a synopsis of the webcast

Power BI Webcast

Power BI for Office 365 brings Self-service analytics for all your data to the business

Log on to DSI’s on-demand preview and learn how your organization can unleash Interactive Power View dashboards to:


Discover and Combine – Gary in Finance can find, connect to, explore, and compile data easily from various sources

Model and Analyze – Mary your Analyst can Bring the power of in-memory analytics and flexible modeling into Excel with Power Pivot.

Share and Collaborate – Sharon and Marc can use collaborative Power BI sites quickly to share live reports and custom data sets with the rest of the team.

Get Answers and Insights – Graham in Marketing can Ask questions and get back instant answers with the new Q&A feature.

Visualize – See your data in new ways with bold interactive visualizations in Excel using Power View and Power Map.

Access Anywhere – Stephane from Sales can stay connected from anywhere, on any device, with mobile access to his key live and interactive reports.


The user is not a farm administrator. Please address the validation failures and try again – PowerPivot Configuration Tool

I was installing PowerPivot for SharePoint on a new app server in a multi server farm and came across the following error, “The user is not a farm administrator. Please address the validation failures and try again.”, problem is the PowerPivot install account was the Farm Admin, the SharePoint install was done with the Farm Admin account, the Farm Admin had all access required. So after scouring the internet and trying everything that I could find

  • Install PowerPivot addin on all servers in the farm
  • Remove Farm Admin from admin group, re add Farm Admin to admin group
  • reinstall all components using SharePoint install account

I remembered that when I ran the SharePoint Configuration wizard to add the new app server to the existing Farm, I referenced the Farm to join with an IP rather than a Computer name, this led to the ultimate solution:

  • Uninstall everything (SharePoint and PowerPivot for SharePoint)
  • Install SharePoint
  • Run configuration wizard, be sure to reference the Farm by Name not IP
  • Install PowerPivot for SharePoint
  • Run the PowerPivot for SharePoint 2013 Config tool

Issue resolved, loud Arg, Sigh of relief

SQL 2012 – Exciting times

Just installed SQL 2012 and it was the most pleasant installation experience that I have had with SQL Server, no work arounds or config file modifications; double clicked setup.exe and clicked next until finished. Make sure you uninstall any Denali/2012 pre RTM instances including PowerPivot that you may have and remove old Denali/2012 PowerPivot and SSRS service applications in SharePoint Central Admin

PowerPivot Excel datasource with period in Column Name

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$]

PowerPivot for SharePoint Installation Hiccup

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 =, 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