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 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.

FileListing

  • 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 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

itemdata

  • 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)

extractedandrenamed

  • 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.)

RestoreMenu

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

restore

  • 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.)

Refresh

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

ScriptedRestore

  • 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.

SSDTImport

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 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.


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.

 


Patch Tuesday strikes the Microsoft Surface – added firmware update goodness!

While showing my Microsoft Surface RT to someone attending this week’s SQL Server 2012 class, I noticed that I had some pending Windows Updates. Among them was a Firmware update – the updates screen even suggested I make sure the battery was charged before updating:

Clicking into the update list for more information shows the firmware patch, plus some assorted Windows 8 patches which are a mix of RT-specific and general Windows 8:

Note: I got this from the Charms sidebar Settings item. When I went to the Desktop mode Windows Update interface (get there fastest using the Winkey+X shortcut menu), the other patches were offered, but the firmware item was not shown (note the change in number of updates offered):

What’s conspicuously absent is any link to more detail on the firmware patch. No KB number, links, nothing. What’s does it patch? You got me. It does show up in the Desktop mode Windows Update History list, but again, there’s no additional information provided.

While exploring links in the KB articles accompanying the various patches, I did stumble upon a Support page that I didn’t know existed, for info on how to handle Surface Updates. I’m not sure how useful it is, but it’s worth a quick look.


More Microsoft SurfaceRT stuff: How to take a screenshot

Happily, I found how to take a screenshot on my Microsoft Surface RT, which I still haven’t found out how to do on my Windows 7 phone. How?

Press and hold the Windows button on the bottom of the screen (in landscape mode), and while you hold it, press the volume down rocker switch on the left. The screen should flash, and your screenshot will be saved as “Screenshot (n).png” at C:\Users\<your account name>\Pictures\Screenshots

Nifty!

Why am I posting this? See my next posts :)


Trying Disk2vhd to back up my laptop’s drive

I’m about to leave on another trip to teach a couple of classes, and I usually back up my laptop before heading to the airport. I usually use Acronis True Image. However, this was a replacement hard drive (see the account of my previous drive’s fate here) and I hadn’t reinstalled Acronis yet.

One more thing to do before I could then back it up and pack it for the trip, I guess. Where’s that serial number?

But while I was staring at Windows Explorer and remembering which virtual hard disks (VHD) I needed to bring for my demos, a dim memory bubbled up to the surface. I thought I’d seen, somewhere, a utility that could do a live migration of a drive from physical to virtual. A quick Binging with Google later, I rediscovered Disk2vhd, which I’d read about a while back, but never tried.

From the description on the utility’s page:

Disk2vhd is a utility that creates VHD (Virtual Hard Disk – Microsoft’s Virtual Machine disk format) versions of physical disks for use in Microsoft Virtual PC or Microsoft Hyper-V virtual machines (VMs). The difference between Disk2vhd and other physical-to-virtual tools is that you can run Disk2vhd on a system that’s online. Disk2vhd uses Windows’ Volume Snapshot capability, introduced in Windows XP, to create consistent point-in-time snapshots of the volumes you want to include in a conversion.

I’m trying it as I write this quick post. I’m creating the VHD on an external USB drive. If I need it later, I can boot to it, I can mount it in a virtual machine, or since I’ve got Windows 7 and Windows 8 machines around, I can mount it directly as a drive to recover files from it.

(I’ll still install Acronis when I’m done, and capture an image as I usually do. We DBA types are paranoid that way.) Results to follow.

Have you backed up to VHD? What’s your experience been? Leave me a comment.


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.

GET TO THE POINT

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.

Epilogue:
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!


Follow

Get every new post delivered to your Inbox.

Join 800 other followers