locked
Distributing Power Pivot reports to non Power Pivot Users RRS feed

  • Question

  • I'm struggling to figure out the best deployment/rollout plan for some power pivot models we have created.  We are using Excel 2010 and Sharepoint. Our super users have powerpivot. Here is what we are trying to do:

    We have created a power pivot model by connecting a few sources together, creating measures etc. This model (stored inside the .xlsx workbook) is saved out to sharepoint. Within this same workbook, we have created some 'templates' or 'splash pages' designed with charts and graphs with slicers to get the end users started and give them a high level example of what is in the data model. However, we do not want to spend time creating any and every view of the data that our end users may have, so we want them to be able to take what we've started, stay connected to the central power pivot model, and create whatever views/pivots of the data they want.  I should note that most of our end users are pretty advanced excel users but do not have power pivot.  They would not be satisfied with viewing the workbook in sharepoint, they will want to build their own views and tabs.

    However, I notice that when I build this starting tab in the same workbook as the power pivot model, my end users cannot manipulate the data. It seems as though I must build this starting tab in a separate workbook with a .odc external link to the model workbook.  So it as if I need 3 files on the sharepoint site: 1. workbook with power pivot model in it 2. odc to connect to the first power pivot model 3. view of the data connected to #2

    Is this assumption correct or am I missing something here? I do not want all of my end users having power pivot as I want the models centrally managed and maintained so that all users, regardless of where they are connecting from, get the same updates.  HELP!

    Wednesday, February 5, 2014 6:48 PM

Answers

  • Just to be clear, the template workbook doesn't have to have Power Pivot installed. In regular Excel, you can just set up an external data connection to Analysis Services and point it at the SharePoint url for the published data model's bism file. Creating the bism file just takes a couple steps and is basically an odc for Analysis Services/Power Pivot. See detail on creating the bism: http://technet.microsoft.com/en-us/library/gg471575.aspx
    • Marked as answer by Elvis Long Thursday, February 13, 2014 8:38 AM
    Thursday, February 6, 2014 2:55 AM
    Answerer
  • If everyone is using Excel 2010, then everyone who interacts with a PowerPivot model in a stand alone workbook must have PowerPivot installed.

    This goes for anyone that wants to create their own pivots/charts and anyone who wants to utilize views created by someone else.

    In Excel 2010, the underlying data is stored in PowerPivot.  No PowerPivot add-in no access to the underlying data in a standalone workbook.

    So as you have stated, your users can use a PowerPivot workbook hosted on SharePoint as the backend to another workbook since Excel will treat a workbook on SharePoint like it is an Analysis Services Tabular Model.

    So really, I think you would just need 2 workbooks:

    1. Data Model hosted in SharePoint

    2. Template workbook with odc to SharePoint workbook and your "starter tab"

    Wednesday, February 5, 2014 7:01 PM
    Answerer

All replies

  • If everyone is using Excel 2010, then everyone who interacts with a PowerPivot model in a stand alone workbook must have PowerPivot installed.

    This goes for anyone that wants to create their own pivots/charts and anyone who wants to utilize views created by someone else.

    In Excel 2010, the underlying data is stored in PowerPivot.  No PowerPivot add-in no access to the underlying data in a standalone workbook.

    So as you have stated, your users can use a PowerPivot workbook hosted on SharePoint as the backend to another workbook since Excel will treat a workbook on SharePoint like it is an Analysis Services Tabular Model.

    So really, I think you would just need 2 workbooks:

    1. Data Model hosted in SharePoint

    2. Template workbook with odc to SharePoint workbook and your "starter tab"

    Wednesday, February 5, 2014 7:01 PM
    Answerer
  • This makes sense. For some reason I wasn't connecting the dots that you can't connect to the power pivot model as tabular model unless you had power pivot. And the fact that you can keep the connection tied to the connection string on sharepoint will prevent the user from actually downloading the model itself.  Perfect.
    Wednesday, February 5, 2014 9:54 PM
  • Just to be clear, the template workbook doesn't have to have Power Pivot installed. In regular Excel, you can just set up an external data connection to Analysis Services and point it at the SharePoint url for the published data model's bism file. Creating the bism file just takes a couple steps and is basically an odc for Analysis Services/Power Pivot. See detail on creating the bism: http://technet.microsoft.com/en-us/library/gg471575.aspx
    • Marked as answer by Elvis Long Thursday, February 13, 2014 8:38 AM
    Thursday, February 6, 2014 2:55 AM
    Answerer