locked
Using data from Powerpivot in excel workbook RRS feed

  • Question

  • Hello,

    Is there a way to use the data from powerpivot in a new excel workbook? I don't want to pivot the data but use the data in a regular excel data table.

    I'm learning powerpivot so please excuse me if I've asked a wrong question.

    Thank you

    Friday, February 7, 2014 6:23 PM

Answers

  • I don't want to pivot the data but use the data in a regular excel data table.

    Hello,

    PowerPivot is not a relational database, which you could use for a data list in Excel; you always work with aggregated data.

    What is the data source for your PowerPivot model, a SQL Server? Then you can connect from MS Excel to SQL Server to get the data.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Michael Amadi Sunday, February 9, 2014 9:13 PM
    • Marked as answer by Charlie Liao Monday, February 17, 2014 1:57 AM
    Sunday, February 9, 2014 9:26 AM
  • I don't want to pivot the data but use the data in a regular excel data table.

    Hello,

    PowerPivot is not a relational database, which you could use for a data list in Excel; you always work with aggregated data.

    What is the data source for your PowerPivot model, a SQL Server? Then you can connect from MS Excel to SQL Server to get the data.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Hi nihcas7713

    Olaf is spot on. The only exception is if the workbook has been uploaded to SharePoint 2010/2013 Enterprise Edition and Power Pivot for SharePoint (SQL Server 2012 version) has been installed and enabled. In this scenario a connection in the Excel workbook could be created which points to the Power Pivot workbook .bism connection file in SharePoint. This would allow it to consume the Power Pivot model and use DAX queries to return tables from it.

    Create a BI Semantic Model Connection to a PowerPivot Workbook

    http://technet.microsoft.com/en-us/library/hh213103.aspx

    PowerPivot BI Semantic Model Connection (.bism)

    http://technet.microsoft.com/en-us/library/gg471575.aspx

    Execute a DAX Query on SSAS Tabular in Excel

    http://sqlblog.com/blogs/marco_russo/archive/2013/01/31/execute-a-dax-query-on-ssas-tabular-in-excel.aspx

    Regards,

    Michael


    • Edited by Michael Amadi Sunday, February 9, 2014 9:14 PM
    • Proposed as answer by Charlie Liao Tuesday, February 11, 2014 9:02 AM
    • Marked as answer by Charlie Liao Monday, February 17, 2014 1:57 AM
    Sunday, February 9, 2014 9:13 PM

All replies

  • I don't want to pivot the data but use the data in a regular excel data table.

    Hello,

    PowerPivot is not a relational database, which you could use for a data list in Excel; you always work with aggregated data.

    What is the data source for your PowerPivot model, a SQL Server? Then you can connect from MS Excel to SQL Server to get the data.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Michael Amadi Sunday, February 9, 2014 9:13 PM
    • Marked as answer by Charlie Liao Monday, February 17, 2014 1:57 AM
    Sunday, February 9, 2014 9:26 AM
  • I don't want to pivot the data but use the data in a regular excel data table.

    Hello,

    PowerPivot is not a relational database, which you could use for a data list in Excel; you always work with aggregated data.

    What is the data source for your PowerPivot model, a SQL Server? Then you can connect from MS Excel to SQL Server to get the data.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Hi nihcas7713

    Olaf is spot on. The only exception is if the workbook has been uploaded to SharePoint 2010/2013 Enterprise Edition and Power Pivot for SharePoint (SQL Server 2012 version) has been installed and enabled. In this scenario a connection in the Excel workbook could be created which points to the Power Pivot workbook .bism connection file in SharePoint. This would allow it to consume the Power Pivot model and use DAX queries to return tables from it.

    Create a BI Semantic Model Connection to a PowerPivot Workbook

    http://technet.microsoft.com/en-us/library/hh213103.aspx

    PowerPivot BI Semantic Model Connection (.bism)

    http://technet.microsoft.com/en-us/library/gg471575.aspx

    Execute a DAX Query on SSAS Tabular in Excel

    http://sqlblog.com/blogs/marco_russo/archive/2013/01/31/execute-a-dax-query-on-ssas-tabular-in-excel.aspx

    Regards,

    Michael


    • Edited by Michael Amadi Sunday, February 9, 2014 9:14 PM
    • Proposed as answer by Charlie Liao Tuesday, February 11, 2014 9:02 AM
    • Marked as answer by Charlie Liao Monday, February 17, 2014 1:57 AM
    Sunday, February 9, 2014 9:13 PM