locked
Using PowerPivot (Excel) as the data source in Dashboard Designer (PerformancePoint) RRS feed

  • Question

  • Hello Team,

    I'm want to use a PowerPivot (Excel) as the data source in Dashboard Designer (PerformancePoint) to create KPIs. I'm using SQL Server 2008 R2, Excel 2010 and SharePoint 2010 to achieve this. I used "PROVIDER=MSOLAP;DATA SOURCE=http://contoso/Documents/PowerPivot_Sample.xlsx"

    I also followed link which explains the way to use an excel source in the connection string: http://technet.microsoft.com/en-us/library/ff191197.aspx. However I get the following error:

    image

    I already have ADOMD 10 installed http://www.microsoft.com/download/en/details.aspx?id=16978

    What could be the possible ways to get over this issue! Thanks for any assitance.

     

    Best Regards, Abhishek Madan

     


    Abhishek Madan
    Friday, February 3, 2012 3:03 PM

Answers

  • Hi,

    Have you installed and configured PowerPivot using SQL SQL Server 2008 R2 Enterprise Edition??

    I recently ran into this issue when configuring SharePoint 2010 to use SQL 2012 RC0 for PowerPivot my issue was that I hadnt upgraded admo.net to version 10.5.

    SharePoint's pre-requisite installer will install both the 2005 and 2008 versions of ADOMD.Net. This ensures that Analysis Services data connections work smoothly out of the box. If you look in add/remove programs on your SharePoint server, you can see the provider versions. Version 10.1 is the one that PerformancePoint will use when connecting to the PowerPivot workbook when you use it as a data source.

    My problem was that there was no policy telling the request to use the latest version of the provider, and PerformancePoint is looking specifically for major version 10. So, in effect, version 11.0 was unused. However, when you install the ADOMD client version 10.5, things work because that version is compatible with the latest PowerPivot models, and PerformancePoint is still happy because it sees version 10, just like it expects to. Unlike major versions of the provider, which exist side-by-side, minor versions overwrite each other, which is why PerformancePoint grabs the right one even after you overwrite version 10.1 with version 10.5.

    -Ivan


    Ivan Sanders My LinkedIn Profile, My Blog, @iasanders.

    Sunday, February 12, 2012 12:02 PM

All replies

  • Hi,

    Have you installed and configured PowerPivot using SQL SQL Server 2008 R2 Enterprise Edition??

    I recently ran into this issue when configuring SharePoint 2010 to use SQL 2012 RC0 for PowerPivot my issue was that I hadnt upgraded admo.net to version 10.5.

    SharePoint's pre-requisite installer will install both the 2005 and 2008 versions of ADOMD.Net. This ensures that Analysis Services data connections work smoothly out of the box. If you look in add/remove programs on your SharePoint server, you can see the provider versions. Version 10.1 is the one that PerformancePoint will use when connecting to the PowerPivot workbook when you use it as a data source.

    My problem was that there was no policy telling the request to use the latest version of the provider, and PerformancePoint is looking specifically for major version 10. So, in effect, version 11.0 was unused. However, when you install the ADOMD client version 10.5, things work because that version is compatible with the latest PowerPivot models, and PerformancePoint is still happy because it sees version 10, just like it expects to. Unlike major versions of the provider, which exist side-by-side, minor versions overwrite each other, which is why PerformancePoint grabs the right one even after you overwrite version 10.1 with version 10.5.

    -Ivan


    Ivan Sanders My LinkedIn Profile, My Blog, @iasanders.

    Sunday, February 12, 2012 12:02 PM
  • Did you ever find out the solution for this? I have the same problem, and I've validated the versions of ADOMD.NET that I have installed. PerformancePoint works expected in all other regards
    Friday, September 20, 2013 8:07 PM