Five Observations From SQL PASS

 
Wow, what a trip.  I barely slept.  Let’s get right down to it:  summary of my thoughts on the whole thing.

1) The Future of MS BI is (Basically) PowerPivot

As was widely anticipated, MS announced that the VertiPaq engine will no longer just be part of PowerPivot – it will be added to the “core” SQL Server Analysis Services (SSAS) product in the next release, codenamed Denali.

The VertiPaq engine, aka “the engine of the devil” in AmirNetzSpeak, is the technology under the hood in PowerPivot that makes the crazy compression and query speed possible.  The omission of VertiPaq from PowerPivot’s “big brother,” SSAS, in SQL Server 2008 R2 was due only to insufficient time to do both.

So… the MS team going back and rectifying the omission is hardly a surprise.  What IS a surprise, however, and a wonderful surprise at that, is that the core of the PowerPivot design and modeling experience is ALSO moving into core SSAS.

It may not be immediately obvious what that means to us, the budding PowerPivot pros of the world, so I will try to line it out clearly:

1. Going forward, the DAX formula language will be supported (and encouraged) in the core SSAS product.  You will not have to learn a new formula language in order to take advantage of the heavier-weight tools, in other words.
2. Similarly, the table-oriented modeling approach of PowerPivot will also be moving into SSAS core.  In PowerPivot, you see tables of data that look like Excel, and you relate them to each other, add calc columns, etc.  In SSAS vNext, you can still do exactly the same things.  They now are calling this table-oriented model “BISM” but I have already forgotten what it stands for 🙂
3. On net, this means that all of the most important PowerPivot concepts will now be useable in core SSAS.  So you will be able to “graduate” from the Excel addin experience to the core SSAS modeling environment, if that appeals to you.  The big barriers that prevented people like me from mastering core SSAS in the past (namely the MDX formula language) will no longer stop us 🙂
4. PowerPivot as it exists today will continue to exist, and WILL be improved as well.  If you want to stay in Excel, you absolutely can, and you WILL get new features (just not as many as you will get in core SSAS).
5. Everything, or almost everything, the SSAS team does going forward will be available to us, the PowerPivot/Excel pros.  Examples:
   1. Larger data volumes – PowerPivot caps out at 2 GB filesize thanks to SharePoint’s file size limit.  Core SSAS with Vertipaq, DAX, and BISM will have a MUCH larger data capacity.
   2. Dimension Security – today, if you want employees in each department to only be able to see data pertaining to their department, you have to create separate PowerPivot workbooks for each department, which is a maintenance hassle.  In core SSAS, that is not the case – you can secure things in one place, no need to make copies.
   3. More – there will be more of course, but for now those two jump out at me as the most important that will ONLY be available in core SSAS.

2) Vanishing Separation Between Excel and BI

Because of these developments, at some point in the future – maybe 5 years, maybe 10 – I predict the line between “Excel Pro” and “BI Pro” will have all but vanished.  The range of skill levels will be as broad as ever – there will still be Excel pros who use VLOOKUP but never use DAX – but the sharp divide we see today will be gone.

Which is, of course, a very rational thing to have happen.  Long overdue in fact.

Another way to say this:  the top-end career possibilities of Excel pros are about to be expanded massively.  Here’s how I visualize it in my head:

Hey Excel Pros – No More Career Walls 

3) “Project Crescent” Finally Gives Us Modern Visuals!

If you are an MS BI pro, you’ve long had the following visualization choices in the Microsoft platform:

Excel is easy and approachable, but limited to basically the same chart types that existed in 1985.  Excel charts DID get a facelift in 2007, and I’d argue that conditional formatting has improved so dramatically in 2007 (data bars, icons, gradients) and 2010 (sparklines) that it warrants mention as a visualization type of its own.  But…  no map visualizations.  No gauges.  No time-lapse animations.  Nothing like Pivot Viewer “card views.”  Nothing remotely “web 2.0” or otherwise modern.

Reporting Services and PerformancePoint offer things like maps and gauges, but still, no animations, no card views.  Oh, and hooking those up to PowerPivot isn’t exactly hard, but I wouldn’t say it is trivial either.

Brace yourself for Crescent, and the enthusiasm of the one and only Amir Netz:

Super modern – card views, time lapse animations, interactive exploration…  it’s like we stepped into a time machine and landed in, um, 2010.  Visualization has long been SUCH a weakness of the MS platform that I’d kinda grown used to it, and now suddenly it feels like a TRIUMPH rather than “they’re just doing what they should be” – delaying this long was a clever marketing ploy 🙂

Oh, and it connects directly to PowerPivot models.  Super simple, just give it a URL.  I’m hooked.  This will never replace Excel for us as a reporting and visualization tool, but I’m sure we will use it a LOT as a supplement.

4) The SSAS and SSRS Teams Actually ARE Cooperating!

The SQL team at MS has long had three teams in the BI space:  SSAS, SSRS (Reporting Services), and SSIS (Integration Services).  Both AS and RS got along great with IS, but in all honesty, AS and RS have behaved more like rivals for as long as I can remember.

Well, a number of the personalities behind the scenes that were responsible for that competitive vibe have departed.  And the results are resoundingly positive – the two teams are now cooperating.  Fully.

A few examples:

1. Crescent Itself – is a product of the Reporting Services team, but it demos like a front end that was designed 100% with PowerPivot in mind.  Which it was.
2. Blobs! – Since Crescent is so big on images, PowerPivot vNext is being expanded to support columns of type “blob,” which is a weird way of saying “your PowerPivot models can now contain images.”  Got a Products table?  Now you will be able to embed a picture of each product as a new column.  Just one example.
3. Shared Data Model (BISM) – this is less interesting to Excel pros than BI pros, but to be brief:  AS and RS have long promoted competing data models (cube vs. semantic layer over flat tables).  There has never been a super good reason for that.  Moving forward, both teams are adopting BISM, which PowerPivot pros can translate to mean “the PowerPivot way.”

5) Other Goodies On the Way

Just a random grab bag of other fun things I saw coming our way:

1. New DAX Functions – for instance, a *simple* RANK function…  and a fast one at that.
2. KPI Design Tools – think of this as “conditional formatting defined in the model” that applies by default to every pivot you create – less repetition.
3. Number Formats – similarly, you will be able to set number formatting in the model and have it automatically carry through to all pivots.
4. Hmmm…  I think that may be all that I am allowed to talk about.  (I saw a number of other things but under NDA)

All in all, I can’t wait.  I hear it’s about a year before this stuff hits the market, minimum.  But the betas will be fun 🙂