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.


Why I’m keeping my Surface RT

There’s a lot of excitement, both positive and negative, about the new Microsoft Surface RT tablet. I thought I’d add to the noise.

Disclaimer and background: I’ve been an iPad user since they came out, and cling to my personal iPhone 4 even though I also have a Samsung Focus S Windows phone as my business line, as well as for better integration with my work Outlook mail and calendars. I’ve currently got the “New” iPad (which is no longer the newest iPad), with a Verizon 4G plan. Great device, even if it’s not so great for my work with Office docs. But that’s ok, my tablet really is more for consumption than creation. I also have a Verizon MIFI 4G hotspot, which predates the iPad, and doesn’t get much use, but it’s in my bag for when I need it.

When I read the specs for the Surface RT I was initially underwhelmed. Here are the key factors I thought would keep me from liking it as a business device (mind you, my iPad does just fine for my non-business use):

  • Low-resolution screen (I hated netbooks for the same reason) useless to me for real work at 1366×768 unless hooked to an external monitor. When you live in SQL Server Management Studio or Visual Studio, less than 900 vertical pixels just sucks.
  • Limited storage – even with an external SD card, the native Windows Video and Music apps won’t “see” media files on the external card without some opaque sorcery (sorcery to be described separately). To play a video from the card, you need to change to desktop mode and browse to the file, opening it from Windows Explorer. Hardly seamless, and though that’s a limitation of Windows 7/8 libraries, not the RT, it would be very annoying on a tablet. Desktop Windows Explorer is painful with a touch interface. And without a Dropbox RT app, I wouldn’t be able to conveniently get to all the stuff I need on any machine I’m using. Even the iPad has Dropbox access from apps like GoodReader and Dropbox.
  • Limited memory – I keep a virtual machine with a SQL Server BI and SharePoint environment running almost all the time. I’ve been a VMWare Workstation user since the late 90s, but with the arrival of Hyper-v in Windows 8, I’m all Hyper-v now, all the time. This wasn’t going to happen on a Surface, not even on a Pro, unless I can add a LOT of RAM to the Pro.
  • Lack of a full Office suite. Outlook is critical to me. Windows Mail can connect to my corporate Office365 account, but it’s minimally useful.

I figured I’d pass on the RT, and back-burner the whole idea until the Pro was due. But I chewed on it some more. I realized that the Surface Pro, for quite a bit more money, was still going to have the display, space and memory limitations of the RT, even though it would be more flexible in terms of allowing a greater range of applications to run on it. I couldn’t see myself using even a Surface Pro as a desktop/laptop replacement.

What was I looking for? I really wanted a lightweight backup machine for presentations, in case my ThinkPad failed. (My ThinkPad T420, as great a machine as it is, has eaten quite a few primary drives, once doing so 2 days before I left for a 10-long trip to Indonesia.) I want something very lightweight that can be there in a pinch to help me successfully deliver a class or presentation. Let’s be honest: it’s gonna spend more time in Season 4 of “The West Wing” than it is in Word.

My presentations and classes for SolidQ need two things: the ability to display PowerPoint decks (those I create and those provided for courses I teach), and the ability to run demos within a virtual machine. For example, last month I taught an online two-week class (Designing BI Report Solutions with SQL Server 2012 Reporting Services and SharePoint 2010) using GotoWebinar (for sharing of my decks and demos in a local VM), and using VMs hosted in Windows Azure for the student labs. Worked great, though I always miss the face-to-face energy of an in-person class.

Could I do that with a tablet? The subset of Office 2013 which ships with the Surface RT includes PowerPoint. With a Remote Desktop app on a tablet, I could theoretically connect to a hosted VM (I knew the x64 version of the Windows 8 desktop RDP client was just fine at connecting to VMs in Windows Azure; shouldn’t the RT version be able to do so?). With enough time and planning, I can build my own Hyper-v VMs locally, sysprep them, and upload them to Azure storage for later use in the VM role.

So I pre-ordered the Surface RT with the Type Cover, plus the dongles for VGA and HDMI output. The Surface arrived on a Friday, I left for my first trip on Saturday. I’d barely set up the Surface RT before I left, putting the slides and PDFs for my next several classes, as well as some TV episodes on it. I played with it on the 7-hour flight from Denver to Honolulu, enjoying the long battery life and the wide screen for watching TV. (It took me way too long to find the notch for easily opening the kickstand; I might have broken a nail prying it open.) I did not like the lack of integration between the Movies app and the external storage, nor the apparently inability to detect the season and episode numbers for the video files. Displaying them alpabetically or by date added were the only choices, which was annoying.


Here’s where the Surface earned its keep. I was teaching a 5 day class at a minimally-equipped customer facility: ethernet only (no visitor wireless access), a 1024×768 VGA projector, no additional display for me (which limits my use of PowerPoint presenter mode). Fine, I’ve done a million of those. The ThinkPad gets the ethernet and connects to the projector, presenter pointer in one USB slot, mouse receiver in another. I’m ready to go for slides and Hyper-v demos. I set up the Surface on the side as a PowerPoint teleprompter, so I see where I am in the presentation and what’s coming up next. The MIFI hotspot comes out of the bag and provides the net connection to the Surface. Fine and dandy for day 1.

Day 2: disaster strikes. 15 minutes before class will start, the ThinkPad won’t wake up. Moments go by, and it throws the dreaded “Drive not recognized” error. My SSD has shuffled off this mortal coil. (happily the SSD only held the OS and Programs – course docs, base images for all my VMs, etc., are on the second drive in the CD-ROM bay). I’m screwed. Students (wonderfully arrayed in aloha shirts – I’m in IT geek shirt heaven) are filtering in, unaware of my impending doom.

And then I remember the tablet sitting next to me. Ok, it has the slides and PDFs. I can even plug the presenter clicker into the single USB port. I hook the Surface to the projector with the dongle (really glad I have it now), plug in the presenter clicker, but can’t use the mouse. Teaching with touch? I guess we’ll see. What about the VMs? (My courses are very demo-heavy, as I don’t believe in death-by-PowerPoint). I head to the Azure management portal. I can’t use the Ethernet connection, but MIFI to the rescue. I have a Windows Azure account, and while I don’t have any capability to upload a sysprepped VHD from the tablet, especially over 4G, there are templates I can choose from to provision and launch.

One more little twist. This is a custom session based on SQL Server 2008 Integration Services, followed by a couple of days of Reporting Services 2008 R2, including Enterprise features.

Azure only offers a template for SQL Server 2012. Rats.

What about Amazon Web Services? I’d screwed around with AWS before, though certainly not to the depth to which my friend Lynn Langit (b|t) knows it. They too, have templates. Including one for a Standard Edition SQL Server 2008 VM. I pretend I know exactly what I’m doing as I quickly spelunk my way through the AWS screens (it turns out I initially grabbed an under-spec’d VM, but limped along until the morning break, when I traded it in for a template with more RAM)

Five minutes later, I have my SQL Server 2008 VM provisioned, have the encryption keys and Administrator password generated, and my RDP session is underway. Whew. Five minutes after that, the VM is pulling my course content and a copy of ZoomIt from my Dropbox account, it’s now 8:30am, and class is starting on schedule.

I’m teaching from the Surface RT, and I can barely tell that I’m not on my ThinkPad. Even across the 4G MIFI connection, the RDP session to the VM is smooth as silk.

I didn’t plan this test, and I certainly didn’t welcome it, but the Surface just earned its keep, for exactly the use case I’d considered. A one-pound backup presentation device.

The only laptop drive on the shelf at the Honolulu BestBuy went into the ThinkPad that night in my hotel room. I installed Windows 8 and Office 2013 from a flash drive, re-enabled the Hyper-v role, and allowed the Thinkpad to resume its primary duty on Wednesday to finish the week. The Surface went back to being a dedicated West Wing display device. I’ll deal with the RMA process when I get off the road next. I got my bill for ~$30 in usage charges from AWS later in the week. That is $30 I don’t mind paying.

Speaking at MCT Summit NA

This week is MCT Summit NA, the North American summit for Microsoft Certified Trainers. I’ll be presenting a session there on Friday, entitled “SQL Azure and MCTs: Future-Proofing Your SQL Career.” After my session, I’ll make available the slides, notes, demo scripts and links from my presentation.

See you in San Francisco!

Don’t install Failover Clustering before Sysprep

Posting for my own reference – this one bit me hard this weekend. Traced it back to having enabled the Failover Cluster feature before I sysprepped a virtual machine. Don’t do this! It creates the duplicate address problem.
The validation fails when you run a cluster validation wizard for a Windows Server 2008 cluster.

SQL Server 2008: Forgot to add an administrator account?

(Note: This post was originally written on SQL Server 2008. I have migrated it intact as it still applies to SQL Server 2008R2)

File under: letting myself serve as an example for others.

Screen shot 2009-12-10 at 8.16.51 PM.png

I recently built a test box with an instance of SQL Server 2008 Developer Edition. I’ve done this so many times that I apparently got sloppy and stopped paying attention during setup. This time it caught up with me. On first launching Management Studio to do some post-install configuration, I realized that I’d “locked myself out” – I skipped the step to add my account to the sysadmin role on one of the instances. Setup requires that some account be added as sysadmin, but I didn’t remember what I’d chosen, and there were no other accounts on the box that stood out as likely candidates.

I’d also opted to stick with the default Windows authentication mode, meaning the built-in sa account was not available.

For those who haven’t made the move to SQL 2008, you’ll find the following step in the Setup program. Unlike in earlier versions, local Windows admins aren’t automatically granted access to SQL Server 2008. You’ll want/need to designate one or more account to be added to the sysadmin server role. Skipping that step somehow got me in this mess.


I really wanted to avoid the time it would take to uninstall and reinstall the instance. Happily, I remembered another way: as a member of the local Windows Administrators group, I could start the SQL instance from a command line with the -m parameter for single user mode, connect to the service, and let myself in the “back door”. (“m” for single user? Sure, that makes perfect sense. I remember this as “mono” for one at a time.)

One of the side effects of starting in single user mode is that members of the local Windows Administrator group can connect to SQL Server with its one connection. (Whether this is a good idea from a security standpoint remains open for debate, but it sure saved my bacon this time.) This would give me the ability to log in, map my Windows account into a SQL Server login, and add my new login to the sysadmin role. Here are the steps I followed.

  1. Using the SQL Configuration Manager, I stopped the instance I needed to work with.
    Screen shot 2009-12-10 at 8.18.14 PM.png


  2. I also stopped any other SQL-related services which might try to connect to the instance, using up my one connection. This included Agent, and might also include Analysis Services or Reporting Services, depending on your installed options.
    Screen shot 2009-12-10 at 8.19.07 PM.png


  3. From a command prompt, I navigated to the SQL Server folder where the program executable lived. 
  4. I started SQL Server’s executable with the -m parameter and waited for it to complete startup and recovery of any databases. (Note that the error log information echoes to the command prompt window but will simply pause when it’s done startup – leave this window open to keep SQL Server running in this single-user mode. You can confirm if you successfully launched in single-user mode by looking for a line in the output that reads “SQL Server started in single-user mode. This an informational message only.
    Screen shot 2009-12-10 at 8.28.49 PM.png


  5. From a query tool, I connected to SQL Server using my Windows account. While I could have used a new query window in SQL Server Management Studio, I didn’t want to wait for it to load, so I used SQLCMD from another command prompt session. (You can’t use the Object Explorer pane in SSMS to connect when in single-user mode: clickers beware! This is one of those times where knowing the code (or having a saved script) is essential) If you’re unfamiliar with SQLCMD, the -S switch specifies the instance name, the -E specifies that you’re using Windows Authentication. These switches are case-sensitive.
    Screen shot 2009-12-10 at 8.35.19 PM.png


  6. Once connected, I created a login for my Windows account:
    Screen shot 2009-12-10 at 8.39.23 PM.png


  7. Then I used a system stored procedure to add my account to the sysadmin fixed server role.
    Screen shot 2009-12-10 at 8.43.30 PM.png


  8. Now that I’ve let myself back in to SQL Server, I stopped the service with the SHUTDOWN command. I could have closed the other command prompt window as well. 
  9. Finally, I restarted the service. Since I already had a command prompt window open, I opted for Windows’ NET START command. The SQL Configuration Manager, the Windows Services Control Panel, or the Registered Servers pane in SSMS would also have worked.
    Screen shot 2009-12-10 at 8.44.59 PM.png

And here’s my visual confirmation (I could have simply tried to reconnect using SQLCMD, but I think we’re all tired of command prompt screenshots by this point). Note that I used the system function IS_SRVROLEMEMBER to check my status. Looking at the properties of my login, or of the sysadmin role in Object Explorer would have shown this as well.

Screen shot 2009-12-10 at 8.46.47 PM.png

I hope this helps someone else in the same predicament!