On Oct 12, 2011, Microsoft have made a number of important announcements about the next version of Microsoft SQL Server. First of all, it has been named SQL Server 2012, and the codename “Denali” has been retired. Secondly, the data exploration technology that used to be called “Crescent” has been given its official name, Power View, and it becomes a prominent feature of SQL Server Reporting Services.

It is promising to be an exceedingly important SQL Server release for the Business Intelligence community. If you watched the SQL PASS 2011 Wednesday keynote, you would have been forgiven for thinking that the relational engine of the product has been left out of the source code, as all the limelight was cast on the BI capabilities. We are happy to see this focus at Project Botticelli, as it means that the technology is moving closer to the user, to the consumer of insight that BI provides. To help you get started, we are also pleased to offer our full-length training course on SQL Server 2012 BI.

Microsoft Power View

While Power View has been seeing a lot of press over the past year, since the early previews surfaced, it is only the front-end to the deep evolution of the underlying analytical engine, SQL Server Analysis Services. Power View is pretty amazing—have a look at this video, in which I show it in action, or follow through our in-depth 75-minute tutorial on Power View, part of our SQL Server 2012 BI online training.

If you have not seen it yet, imagine interactively exploring data, automatically creating relationships on-the-fly, ad-hoc reporting, without ever switching between “design” and “preview” modes. It has some gorgeous eye-candy that is also very informative to a user, such as the animated bubble charts (scatter graphs) plotting a time-line of how your data evolved, a bit the way they were introduced by Professor Hans Rosling in his very famous 2006 TED presentation. I think it is amazing, that what only a scientist, backed-up by a team of clever people, could do some five years ago, will become a technology available to a billion budding analysts, in this decade.

I still believe that before this decade is over, anyone who can use email will become an analyst of sorts. QlikView, PowerPivot, Power View, Tableu, Microstrategy and many others are rapidly paving that way.

Business Intelligence Semantic Model (BISM)

At the heart of this evolution at Microsoft is a new layer that separates the storage of analytical data from the way in which you may want to analyse it: the all-new Business Intelligence Semantic Model, or BISM.

The easiest way to think of BISM is by thinking of where it is taking Microsoft BI. For me, and for our customers, it promises to let us “Store Data Wherever We Want and Analyse It However We Like”. For example, you could store your data in an OLAP cube (which is extremely good at many things, e.g. aggregations of measures over complex hierarchies), or in the all-new SSAS Tabular Model (which works for the type of data that PowerPivot is good at), or perhaps in any other back-end system. However, no matter where you keep the analytical data, you can analyse it using the tools you prefer to use for the job in question: PowerPivot, Power View, perhaps a report, or some great analytical product from company X.

I really like the idea of BISM and of the tabular model. You no longer have to build a “quick cube” for rich reporting, for example in SharePoint Performance Point Services. Just wrap a data mart using a tabular model, and through the magic of BISM, voila, you are ready for rich reporting!

TK Anand, a really nice Microsoft guy and a key brain behind these things, calls it “One Model for all End User Experiences”. 

Time will show how much of this perfect separation of storage from analytics will ship in the first release of the technology, but being patient people, I hope it will allow for all sensible combinations of storage engines and R&A tools in the future, though perhaps beyond the release of SQL Server 2012. Microsoft plans are very ambitious, and I wish them well for the next release.

We explain BISM in detail in this online, video-based training course.

Data Analysis Expressions (DAX)

An important part of any such approach, at creating, effectively, a new interface (API) between two worlds, is the choice of the language and of the protocols. Thankfully, both of those decisions use something you may already know from PowerPivot. Rather than reinvent the wheel, Microsoft supports the usual protocols, plus a new one for pass-through access to other back-ends. From a language perspective, the star of the future is Data Analysis Expressions (DAX), that “Excel formula notation on steroids,” which has the simplicity of Excel, yet enables columnar and tabular expressions, and perhaps multidimensional expressions in the future. Introduced in PowerPivot, it is quickly becoming the key language for analytics on the Microsoft BI platform. If you have not seen it, try it. It is fairly intuitive (unlike MDX) and simple to learn.

Does it mean that MDX (Multidimensional Expressions) is about to pass into oblivion? I don’t think so. Even BISM exposes a MDX interface, so that you can query your model using older tools. In fact, that is how Excel talks to BISM.

PowerPivot for SQL Server 2012 (aka PowerPivot “2”)

Incidentally, PowerPivot is getting a nice make over. UI gets prettier, there is proper table/relationship diagraming, but above all, it now supports the new BISM features, such as ability to create calculations and KPIs inside the tabular model, and a great new way to do granular row-level security, using, surprise-surprise, DAX for role filters (when used as a tabular model in SSAS). Pretty powerful, yet simple.

Interestingly, PowerPivot 2 is both a creator, and a consumer of BISM tabular models. That means the KPI you just created using PowerPivot 2 can be consumed by some other application in your portfolio. Still, I wonder how long before we end up in a self-referencing loop situation. While this is a great feature that makes layered modelling possible, it will become necessary to start tracking of the sources and the destinations of data, perhaps by means of impact and lineage analysis. Cue-in SQL Server 2012 Data Quality Services (DQS), but that is a subject for another article.

You can study all the new features of the new PowerPivot in this full-length training course video.

Future for OLAP Cubes 

Just as MDX will be with us for a long time, I am certain that there is a fairly sound future for OLAP cubes. MDX is just too powerful, and cubes are just too good at many jobs that rely on more complex modelling, for example when you want to deal with interesting hierarchies or attribute relationships in your models, as are often found in more complex financial applications. They are safe to stay, perhaps for another decade or more.

What is also clear to me, is that as the time moves on, for every cube that someone builds there will be not hundreds, but perhaps tens of thousands of simpler, PowerPivot-style tabular data models. That is why I think Microsoft made a great decision to support tabular models natively in SSAS 2012, as an alternative to OLAP cubes. Indeed, this means you need to install two instances of SSAS: one for cubes and for my good old friend, Data Mining, and another instance for the new tabular models. While there are neat tools for importing an xlsx Excel file (that contains a tabular PowerPivot model) into SSAS, including a right-click option in the new SQL Server Management Studio, I would really like to see more technologies for moving between the two models, so that something that was authored as a tabular model could be turned easily into a cube. But, perhaps, that will not be necessary, as the advancements in memory, processing, and tabular performance might even overtake the need for cubes, one day—I am thinking of the IDC report earlier this year, which suggested an all-in-memory future, even without relational databases! Well, one could even argue, that once everything is in the Cloud, it is, kind-of, in-memory. Perhaps that is a bit of stretch of imagination for now, though.

Cloud

That chapter is yet to be written, but it is clear that we are moving towards a public and a private cloud for analytics. I am covering this subject a lot in my keynotes at the moment, and I am certain that while it is a given that the private cloud works well for analytics, some time needs to pass before public cloud analytics becomes more widespread. I think we have to get the data into the cloud first, before we can analyse it, which perhaps will only happen for pure-cloud solutions—or do you fancy ETLing on-premises transactions into a cloud cube? I don’t think so. Nonetheless, once analytical data is in the cloud, it makes sense to analyse it there. The opportunities are wonderful: I cannot wait for the days when we will not have to travel to a customer’s data centre to analyse their data. Once it is in the cloud, anyone around the world can set-up a consultancy focusing on some aspect of analytics, no matter where you are, as long as you have a good Internet connection, and as long as we get the security and privacy of that data right. Imagine being an analyst, with your feet in the swimming pool in a nice and sunny part of the world (I live in Ireland).

I hope you have enjoyed this short write-up on some of the recent announcements. There will be a video on the subject coming later this year, showing you a demo of those technologies, for our registered users. In the meantime, enjoy this very exciting time in our industry, and if you are considering getting into BI, start reading up, watching our videos, and get right into it, you won’t be disappointed.

Rafal Lukawiecki