locked
Power BI - Office 365 data storage for Excel (Power Pivot & Power Query) architecture RRS feed

  • Question

  • Hi, I trying to find a comprehensive view of the Power BI architecture, specifically where the data for Power Pivot & Power Query files are held. I've seen reference to the Vertipaq Engine and the data being stored in the Excel File, however this is in reference to 2010.

    Other references I've seen (SharePoint on premise with Power Pivot) have shown me that the data held in the Excel files on the SharePoint sites are actually held in SQL Server instances.

    Could anyone provide clarity on the architecture for Power BI please...

    Tim


    Thank you for you time folks!

    Thursday, April 24, 2014 9:07 AM

Answers

  • Hi Tim,

    let my try to explain this:
    When you load data into Power Pivot it is actually loaded into Vertipaq (in-memory column-store) and it resides there until you close Excel. Technically speaking Power Pivot is a single-user SQL Server Analysis Services instance hosted within Excel. When you close Excel a backup of the Power Pivot "database" is created and stored within the resulting .xlsx-file .
    Any user that has access to the Excel file also has access to the Power Pivot model, so there is actually no security
    This is very similar to any other excel file

    Once the Excel is stored in SharePoint / SharePointOnline and is access the first time the backup that is stored within the Excel is restored into SQL Server Analysis Services instance running in SharePoint Integrate mode
    all connections to the Excel-File are redirected to that SSAS instance. After some time of inactivity the model is unloaded from that SSAS instance again to free up the memory

    the data itself so basically always resides inside Power Pivot or respectively the Excel file

    Power Query only stores the actual query (= M-script) but no data

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Proposed as answer by Michael Amadi Tuesday, April 29, 2014 12:38 PM
    • Marked as answer by Tim Windsor Tuesday, April 29, 2014 4:38 PM
    Tuesday, April 29, 2014 12:35 PM
    Answerer

All replies

  • You mean in general, like a diagram?

    Thanks!


    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Tuesday, April 29, 2014 12:07 AM
  • Ed, thanks for your reply. Yes exactly that a diagram explaining the component parts, but ensuring it details where data is held in the excel file. There becomes a lot of confusion when talking to customers about the security of Power BI and before we get to Azure security etc. Technical folk want to understand how and where the data resides in the Power BI stack.

    my understanding is that the power pivot/query data is held in xml format within the Vertipaq engine. The documents will then be held (i assume in binary format) in the Sharepoint SQL Server documents Database.??


    Thank you for you time folks!

    Tuesday, April 29, 2014 7:33 AM
  • Hi Tim,

    let my try to explain this:
    When you load data into Power Pivot it is actually loaded into Vertipaq (in-memory column-store) and it resides there until you close Excel. Technically speaking Power Pivot is a single-user SQL Server Analysis Services instance hosted within Excel. When you close Excel a backup of the Power Pivot "database" is created and stored within the resulting .xlsx-file .
    Any user that has access to the Excel file also has access to the Power Pivot model, so there is actually no security
    This is very similar to any other excel file

    Once the Excel is stored in SharePoint / SharePointOnline and is access the first time the backup that is stored within the Excel is restored into SQL Server Analysis Services instance running in SharePoint Integrate mode
    all connections to the Excel-File are redirected to that SSAS instance. After some time of inactivity the model is unloaded from that SSAS instance again to free up the memory

    the data itself so basically always resides inside Power Pivot or respectively the Excel file

    Power Query only stores the actual query (= M-script) but no data

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Proposed as answer by Michael Amadi Tuesday, April 29, 2014 12:38 PM
    • Marked as answer by Tim Windsor Tuesday, April 29, 2014 4:38 PM
    Tuesday, April 29, 2014 12:35 PM
    Answerer
  • Gerhard thanks for the excellent reply - this makes a lot of sense.  The key piece in your explanation is the operation of how Excel / PowerPivot / SSAS interact using the backup with integration mode.

    To conclude, am I right in saying the excel document itself still reside in the SharePoint Document tables?

    Thanks again,

    Tim


    Thank you for you time folks!

    Tuesday, April 29, 2014 12:49 PM
  • yes, the Excel-file still is stored in the SharePoint Document Library

    and, if necessary, the Power Pivot model of that Excel-file is loaded into SSAS running in SharePoint Integrated mode but the Excel-file always contains the master-copy of the model


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Tuesday, April 29, 2014 2:40 PM
    Answerer
  • Thank you Gerhard, greatly appreciated.

    Thank you for you time folks!

    Tuesday, April 29, 2014 4:37 PM