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.

 

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.

How to create or edit MDX Query text in Reporting Services dataset definition

A frequently-asked question in my SSRS and/or SSAS classes goes something like this: “How do I enter my own MDX query in an SSRS report rather than use the query builder?” While the GUI doesn’t make it obvious, the answer is to use the Design Mode toggle button in the Query Designer:

Toggle Design Mode in SSRS MDX editor

WARNING: if you hand-edit the query statement, and then toggle the Query Designer button, your query text will be lost. Be careful! (Happily, the UI does provide a warning dialog box.)

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.

It just works–but why? (via Chris Webb’s BI Blog)

A good read from Chris Webb.

It just works–but why? One of the things that often confuses people when they learn MDX is the way that certain queries seem to work without them understanding why. This is because MDX tries to be helpful – and I would say too helpful – in correcting your mistakes for you by applying functions to objects, and casting objects to other objects, without you knowing to avoid raising an error. This might seem like a useful thing to do but in the long run I think it stops pe … Read More

via Chris Webb's BI Blog