Restoring a SSAS Tabular Model to Power Pivot

Posted on by — 35 Comments ↓

It is a very common scenario to create a SSAS Tabular Model out of an Power Pivot Model contained in an Excel workbook. Microsoft even created an wizard (or actually a separate Visual Studio project) that supports you doing this. Even further, this process is also a major part of Microsoft’s strategy to cover Personal BI, Team BI and Corporate BI within one technology being xVelocity. This all works just fine but there may also be scenarios where you need to do it the other way round – converting a Tabular model to Power Pivot. Several use-cases come into my mind but I am sure that the most important one is to making data available offline for e.g. sales people to take it with them on their every day work. And in this blog post I will show how this can be done!

But before taking a closer look into how this can be accomplished, lets first see how the import from Power Pivot to SSAS Tabular works. To do this start SQL Server Profiler and connect to your tabular instance. Then create a new Tabular project in Visual Studio based on an existing Power Pivot workbook. At this point you will notice a lot of events happening on our SSAS Tabular server. The most important event for us is “Command End” with the EventSubclass “9 – restore”:
RestoreFromPowerPivot

SSAS actually restores a backup from a “Model.abf” backup file which is located in our project directory that we just created:
BackupExtractedFromPowerPivot

So far so good – but where does this file come from?

Well, the origin of the file has to be our Excel workbook that we imported. Knowing that all new office formats ending with “x” (.xlsx, .docx, …) are basically ZIP files, we can inspect our original Excel workbook by simply rename it to “.zip”. This allows us to browse the Excel file structure:
ExcelUnzipped

We will find a folder called “xl” which contains a sub-folder called “model”. This folder contains one item called “item.data”. If you take a closer look at the file size you may realize that both, the “Model.abf” file that we restored and the “item.data” file from our Excel workbook have the exact same size:
FileProperties

A Coincidence? Not really!

What happens behind the scenes when you import a Power Pivot model into SSAS Tabular is that this “item.data” file gets copied into your project directory and is renamed to “Model.abf” and then restored to the SSAS Tabular workspace instance by using an standard database restore.

Having this information probably makes you think: If it works in one direction, why wouldn’t it also work the other way round? And it does!

So here are the steps that you need to do in order to restore your SSAS Tabular backup into an Excel Power Pivot workbook:

  1. Create a backup of your SSAS Tabular database and rename it to “item.data”
  2. Create an empty Excel workbook and add a simple linked table to the Excel data model (which is actually Power Pivot).
    This is necessary to tell Excel that the workbook contains a Power Pivot model which has to be loaded once the file is opened.
  3. Close the Excel workbook and rename it from “MyFile.xlsx” to “MyFile.xlsx.zip”
  4. Open the .zip-file in Windows Explorer and locate the “\xl\model\”-folder
  5. Replace the “item.data” file with the file that you created in step 1.
  6. Rename the .zip-file back to “MyFile.xlsx”
  7. Open the Excel Workbook
  8. Voilá! You can now work with the data model as with any other Power Pivot model!

I tested this with a SSAS Tabular backup from SQL Server 2012 SP1 being restored to the streamed version of Excel from Office 365 with the latest version of Power Pivot. I assume that it also works with older versions but have not tested all combinations yet.

There are also some features that will not work, for example roles. If your Tabular database contains roles you will not be able to use this approach. Excel will complain that the Power Pivot model is broken. However, other Tabular features like partitions actually work with the little limitation that you cannot change them later on in the Power Pivot model or process them separately:
PowerPivotPartitions
Another thing to note here is that only up to 3 partitions are allowed, otherwise you will get the same error as for roles. I think this is related to the limitation of 3 partitions for SQL Server Analysis Services Standard Edition as Chris Webb described here.

Besides these obvious features there are also some other cool things that you can do in Tabular which are not possible in Power Pivot. Most (or actually all) of them are accessible only by using BIDS Helper – a great THANK YOU to the developers of BIDS Helper at this point!
BIDS Helper enables you to add classical multidimensional features also to Tabular models which is not possible using standard Visual Studio only. Those include:

  • DisplayFolders
  • Translations (metadata only)
  • Actions
  • …

I tested it for DisplayFolders and Actions and both are working also in Power Pivot after the backup was restored and I further assume that all the other things will also work just fine.
Simply keep in mind that Power Pivot is basically a fully featured Analysis Services instance running within Excel!

For my (and your) convenience I also created a little PowerShell script that does all the work:

  1. # Load the assembly with the ZipFile class
  2. [System.Reflection.Assembly]::LoadWithPartialName("System.IO.Compression.FileSystem") | Out-Null
  3. # Load the assembly to access Analysis Services
  4. [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | Out-Null
  5. # Also install "Analysis Services PowerShell" according to http://technet.microsoft.com/en-us/library/hh213141.aspx
  6.  
  7. # INPUT-Variables, change these to match your environment
  8. $rootFolder = "D:\Test_PowerShell\"
  9. $emptyExcelFile = $rootFolder + "EmptyExcel.xlsx"
  10. $ssasServerName = "localhost\TAB2012"
  11. $ssasDatabaseName = "AdventureWorks"
  12.  
  13. # internal variables
  14. $newExcelFile = $rootFolder + $ssasDatabaseName + ".xlsx"
  15. $newExcelFileZip = $newExcelFile + ".zip"
  16. $unzipFolder = $rootFolder + "TEMP_ExcelUnzipped"
  17. $backupFile = $rootFolder + $ssasDatabaseName + ".abf"
  18. $itemDestination = $unzipFolder + "\xl\model\item.data"
  19.  
  20. # Copy the empty Excel file and rename it to ".zip"
  21. Copy-Item -Path $emptyExcelFile -Destination $newExcelFileZip
  22.  
  23. # Unzip the file using the ZipFile class
  24. [System.IO.Compression.ZipFile]::ExtractToDirectory($newExcelFileZip, $unzipFolder)
  25.  
  26. # Create a backup of the SSAS Tabular database
  27. Backup-ASDatabase -Server $ssasServerName -Name $ssasDatabaseName -BackupFile $backupFile -AllowOverwrite -ApplyCompression
  28.  
  29. # Copy the backup-file to our extracted Excel folder structure
  30. Copy-Item -Path $backupFile -Destination $itemDestination -Force
  31.  
  32. # Check if the target file exists and delete it
  33. if (Test-Path -Path $newExcelFile) { Remove-Item -Path $newExcelFile }
  34.  
  35. # Zip the folder-structure again using the ZipFile class and rename it to ".xlsx"
  36. [System.IO.Compression.ZipFile]::CreateFromDirectory($unzipFolder, $newExcelFile)
  37.  
  38. # Cleanup the unecessary files
  39. Remove-Item -Path $unzipFolder -Recurse
  40. Remove-Item -Path $backupFile
  41. Remove-Item -Path $newExcelFileZip

The last thing to mention here is that I don’t know if this is officially supported in any way by Microsoft – actually I am pretty sure it is not – so watch out what you are doing and don’t complain if something is not working as expected.

35 Replies to “Restoring a SSAS Tabular Model to Power Pivot”

  1. Did your SSAS Tab -> PowerPivot contain many measures? Did they all transfer correctly?

    Reply ↓

    • Hi Michael,
      my biggest test contained about 50 measures, which all got transferred correctly.
      are you having issues that some of your measures do not get transferred correctly?

      -gerhard

      Reply ↓

  2. HI.

    The import works wonderfully but the model seems to have detached itself from y datasource.
    When I refresh I get an object reference not set error.

    any Ideas?

    Reply ↓

    • Hi Matt,
      in my tests this worked just fine and also a refresh in the final PowerPivot model worked.
      Do you use any special data source or provider?
      Maybe the provider is not installed on the client or something like that?
      I ran all my tests on Excel 2013, what version do you use?

      -gerhard

      Reply ↓

  3. I followed the guidance exactly – tried several times. However, when I open up the file and click on the Powerpivot icon – I get a message that states:

    ‘We couldn’t load the datamodel. This may be because the data model in this workbook is damaged”.

    Any suggestions?

    Thanks,
    John

    Reply ↓

      • Hi Gerhard, great stuff here, however I am seeing the same issue that John is seeing above. My SSAS version in 11.0.3.0000 and am running Excel 2013 with Add-in provided by Excel itself. So I am not sure of the version conflict? Any thoughts?

        Reply ↓

        • Hi Mike,
          so in my case I used the following setup:
          SSAS version 11.0.5058 (2012 SP2)
          Office 365
          there were some changes from SP1 (which you are using) to SP2 which may cause the issue. Is it possible for you to upgrade to SP2?

          -gerhard

          Reply ↓

  4. Pingback: Link Data Model

    • What do you mean by “suffer”?
      an .xlsx file is just a folder structure that was zipped and renamed
      I have not tested different zipping methods (compression rate, etc.) but just the native Windows zipping functionalities and it works just fine. Also in terms of file size I did not notice any difference

      -gerhard

      Reply ↓

  6. I wondered if anyone has tried this for a SQL Server 2014 Model? I followed the procedures but it doesnt seem to work when I open Excel.

    Reply ↓

      • I have tested with SSAS version 12.0.2456.0, Excel 2013 and got “We couldn’t load the datamodel. This may be because the data model in this workbook is damaged” message.

        Reply ↓

        • might be that this does not work anymore with later SSAS versions. As I stated in the end of the post, this was never officially supported by Microsoft so this behavior might change with newer versions.

          Reply ↓

    • HI Rayis,

      great that it worked for you!
      could you post the versions of SSAS and Excel that you were using as it seems this was causing troubles for some other users

      that would be great!
      thanks,
      -gerhard

      Reply ↓

  8. Hi Gerhard,

    This is a great find, but unfortunately I can’t get it to work like a lot of other comments.

    I think I will install a new instance of SSAS which has been confirmed to work. From your experience which versions of SSAS works? Just 11.0.5058?

    Also does this still work with the latest update of the O365 Excel 2013? Have you tried it with Excel 2016?

    Thanks,
    Tony

    Reply ↓

    • Hi Tony,

      I already replied to the other comments and just referred to the end of the post where I write that this is not a supported approach by MS and might not work with other versions of Office/Excel or SSAS. If it does not work for your specific versions, you need to test it for yourself which versions work or do not work.

      Sorry that I cannot provide any better solution here, I can just say that it works if you have the right versions.

      -gerhard

      Reply ↓

  9. Hi there,
    Can I still follow your instruction if my SSAS source isnt designed in PowerPivot in the first place aka its started off as Tabular but want to downsize to Powerpivot?
    Many thanks
    Peddie

    Reply ↓

    • Hi Peddie,
      well, you can try
      to be honest, I dont know and it depends on a lot of different factors as you may already know by reading the other comments.
      Power Pivot (and Power BI) is evolving very fast and this behavior may change with every version. Also, considering that it was never officially supported, you will not find anyone who will be able to really help you in this case.

      I can just say, try it with your versions of Power Pivot and SSAS – if it works, fine; if not, I am sorry

      kind regards,
      -gerhard

      Reply ↓

  10. This worked for excel 2016 & SQL server 2016 RC0.
    Thanks it lovely solution. I have suffered because of now knowing for the last three years.

    Reply ↓

  11. Hi Gerhard

    Recently In my company there is hard disk on all server. So all the backups are deleted with SSAS Tabular Model backup files and Model also. Also Backups are not recovering from hard Disk. Is there any way to restore the tabular model from Excel File ya Another other approach. Please Help me its Very Urgent.

    Reply ↓

    • as I wrote in the article, you can take any excel file, rename it to .zip, go to the folder xl\model and rename item.data to item.abf
      assuming your SSAS Tabular server have the right version, you can simply restore the item.abf file as any other regular backup

      again, this is not supported by MS

      Reply ↓

Leave a Reply

Your email address will not be published. Required fields are marked *

*