Following a hunch, or, geeky fun restoring tabular models from Excel files

I’ve been having a great time teaching a new BI course I’ve written for SolidQ, initially delivered to a private client, but soon to be available publicly through our learning partners. The course is an accelerated introduction to PowerPivot, Power View, PerformancePoint and tabular model design and support.

One of the topics I cover is the basic architecture of a PowerPivot solution, including the storage of the tabular model with respect to the enclosing Excel workbook. While pointing out the model content inside the workbook file, I wondered about the structure of the blob stored in the XLSX file, and its relationship to a deployed tabular “database” hosted in a tabular mode of Analysis Services. After class finished today, I thought I’d explore. The results were fun to see.

Bottom line: the blob that stores a tabular model inside the Excel file is in the form of an SSAS backup file, and can be directly restored into SSAS tabular. While there are of course more direct and visible paths to restore an Excel file containing a PowerPivot model into SSAS tabular, I thought it was nifty, and wanted to share. See for yourself below:

What you’ll need:

  • An Excel file containing a PowerPivot model. While I used my own workbooks to test this first, you can follow along with a publicly available sample file, Contoso Sample DAX Formulas.xlsx file, available for download here. Save it locally, extract the XLSX file from the rest of the archive, and save it somewhere convenient. If you want to explore the contents of the model, you’ll need Excel 2010 with the PowerPivot add-in installed, or Excel 2013.
  • Access to a SQL Server Analysis Services instance, in tabular mode. I use the Developer Edition on my laptop.
  • To follow my SSDT deployment tangent below, you’ll also need SQL Server 2012 Data Tools installed. Use the version from the SQL Server 2012 installation media, not the standalone SSDT MSDN download, since you’ll need the Analysis Services project templates.

Steps to extract and deploy the model from within the Excel file to SSAS, the least direct and geekiest way:

  • Locate the downloaded workbook. Rename the file extension from XLSX to ZIP. Note the size: almost all of that is the PowerPivot model data, with a little left over for Excel-specific content.


  • Using Windows Explorer’s native ZIP file handling (for the sake of this step), open the ZIP file to expose the Excel file structure. If you didn’t realize before that Office files were zipped-up XML files, you do now.

xlsx contents

  • Drill down into the xl folder, and then the CustomData folder. Note that some versions of Excel and PowerPivot use a model folder instead of a CustomData folder, but the Contoso sample uses CustomData. It doesn’t matter for the purposes of this walkthrough.
  • Locate the file, which again, depending on the version of Excel and PowerPivot that created it, may also be named See the size? It will be almost the full size of the parent XLSX file itself


  • Extract the file to your desktop. For convenience, I extracted it to a folder that my SSAS instance is configured to look into for backup files. On my system, that is C:\SQL12\OLAPTab\Backup, but a default location is C:\Program Files\Microsoft SQL Server\MSAS11.<instance name>\OLAP\Backup
  • Rename the file. Change its extension to .abf (the default extension for SSAS backups)


  • If you know your way around restoring SSAS backups already, either multidimensional or tabular, you can skip the next few steps and restore the .abf file yourself. You’re pretty much done with this now; If you like, jump down to the SSDT deployment tangent.
  • Launch SQL Server Management Tools and connect to your tabular instance of SSAS 2012.
  • Right-click on the Databases folder and select Restore (note: don’t select Restore from PowerPivot, which is the normal and healthy way to restore from an Excel file. Our situation requires a really futile and stupid gesture on someone’s part, and we’re just the guys to do it.)


  • Complete the Restore Database box, providing the backup file you extracted and a name for the new database. I used Contoso as the name.


  • Execute the restore, and after a moment, you should have successfully restored from the blob originally stored inside the Excel workbook XLSX file. Browse the structure, note the tables, data connections, etc. (If necessary, you may need to refresh the SSMS database tree.)


  • As an aside, you can of course also use an XMLA script to restore rather than use the GUI:


  • That’s it – you’ve now gone the long way around to deploying an existing PowerPivot model from an Excel workbook file. But hopefully, this helps better confirm your understanding of the range of development paths with tabular models, going from Excel workbooks, possibly through PowerPivot for SharePoint document libraries, to Analysis Services in tabular mode.

Nifty, eh?

Tangent: SQL Server Data Tools edition.

One other source of a “in-the-wild”model-type file I found while working on a tabular project  in SQL Server data tools. I used the Import from PowerPivot project template and used an Excel workbook as my starting source.


After the project opened, I noticed that a model.abf file was created in my project folder.

SSDT Model.abf

I figured the file extension was no accident, and successfully restored it to my SSAS tabular instance, as in the process described above. No model.abf file is created when creating a new tabular project from the other templates – the data is imported directly into the workspace database on SSAS. SSDT seems to extract the blob from the Excel file, stage it locally, then deploy the workspace database.

What can we do with this information? I suppose that knowing how to extract the model from the workbook (or catch it while it’s staged by SSDT) could be handy when normal deployment methods fail. However, most likely this should be filed under useless trivia. Nice to know, but not need to know.

Power View for Multidimensional CTP available

Well this is happening much sooner than I expected. T.K. Anand posted in his blog yesterday that there’s now a preview available for Power View against SQL Server Analysis Services multidimensional models.

Why is this a big deal? As you may know, Power View is a very cool data visualization tool which shipped with SQL Server 2012 RTM, which becomes available if you install Reporting Services (2012) in SharePoint mode, and also deploy SharePoint 2010 Enterprise. (Full set of requirements and deployment checklist available here.)

In the RTM release, Power View could consume only tabular semantic models, such as those created in PowerPivot for Excel and uploaded to SharePoint, or models created in SQL Server Data Tools and deployed to SQL Server 2012 Analysis Services in Tabular mode (not quite an exhaustive list, there are a couple of permutations of these).

Multidimensional models, better known as cubes, were not accessible to Power View (though they’re certainly available to other Reporting Services tools as well as PowerPivot).

Earlier this month, during a keynote at the PASS Summit, Microsoft announced that support for querying cubes from Power View was coming, but that it would be a v.Next timeframe. Imagine my surprise and delight to see yesterday’s announcement.

CTP/Preview download here. Release notes and links to documentation here. New to Power View? Start here.


Video: What is PowerPivot?

Some of the students in my SQL Server business intelligence classes (SSAS, SSRS, SSIS) have started asking about PowerPivot. Here’s a nice overview video (about 13 mins, can be downloaded for offline viewing)  from MSDN’s Channel 9 on the topic:

PowerPivot in Microsoft Excel 2010

PowerPivot for Excel is an Excel 2010 add-in that allows users to pull data from multiple sources, mash them up, and then build reports using regular pivot tables. You can even share these reports with others in Microsoft SharePoint (via PowerPivot for SharePoint). In this demo, Julie Strauss, Program Manager for Microsoft SQL Server Analysis Services, shows just how easy it is to get a better view into your data.