Twitter Updates

    follow me on Twitter

    Wednesday, December 07, 2011

    PowerPivot Nuggets - Part 13 - Save PowerPivot workbook as Analysis Services Cube

    Consider the following scenario's:

    • A business person created a PowerPivot workbook, but is struggling with some more advanced DAX.
    • A BI consultant created a protype of a cube in PowerPivot
    • A PowerPivot workbook becomes very popular and needs to be rolled out at the enterprise level.  We need to add security, partions, perspecitves ...

    Wouldn't it be nice if we had an option like "File - Save As Analysis Services Cube".

    Well, the title of this nugget is a little misleading.  You won't find "File - Save As Analysis Services Cube" in PowerPivot for Excel.  However there are  "Import from PowerPivot" possibilities.

    First one is in Visual Studio (SQL Data Tools).  Choose, File, New Project, Analysis Services.  And there you have an option to import a PowerPivot workbook.

    Second option is via SQL Server Management Studio.  Connect to a SSAS instance running in tabular mode and Choose "Restore From PowerPivot" from the object explorer.



    Personally I think this is one of the killer features of PowerPivot in SQL Server 2012.  The possibility to easily "upgrade" and enchance an existing PowerPivot workbook and turn it into a full blown Analysis Services cube will dramatically change the we way how we develop and implement BI projects in the future.