New job at Microsoft: time to make the donuts…

After many years of teaching, tech reviewing, and occasionally writing courses for Microsoft, I’ve made the leap! Effective mid-May, I joined Microsoft, specifically the Learning Experiences (LeX) team, with a mission to create and deliver great data platform training.

I’m excited: much of my career has been spent delivering LeX’s donuts; now it’s time to make them! Even better: instead of reaching a dozen learners at a time in a classroom, or maybe a hundred in a conference session, I’ll be able to reach thousands through Microsoft Virtual Academy and our partnership with EdX.

I’ver already got a couple of brand-new courses on my plate. Watch this space for more info!

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