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 item.data 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 item.data 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.
- 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 item.data file, which again, depending on the version of Excel and PowerPivot that created it, may also be named item1.data. See the size? It will be almost the full size of the parent XLSX file itself
- Extract the item1.data 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 item1.data 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 item.data 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.
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.
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 item.data 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.