Microsoft BI 2012: A Year In Review

With the year drawing to a close, I thought it would be the perfect time to recap the major developments in Microsoft Business Intelligence throughout 2012. Unsurprisingly, the launch of SQL Server 2012 proved to be the focal point of this year’s releases, although we had a few extra surprises along the way:

SQL 2012 (Denali) launches

The launch of SQL 2012 in March (RTM) brought a raft of new features to both the DB platform and the BI products built on top of it. Fortunately, my organisation adopted the technology immediately, meaning I was well placed to get a chance to play with the new features from the off. Advances in Master Data Services, new T-SQL functions such as the amazing TRY_PARSE and new storage/indexing advances such as column-store indexes (thanks to the xVelocity engine) bring some interesting advances to keep the SQL crowd interested.

Integration Services (SSIS)

The biggest change to SSIS in 2012 is the addition of the new Integration Services Catalog and Project Deployment Model. This new way of deploying packages to the database server allows project-level configurations and detailed, integrated logging, which reduces developer workload and redundancy.  Oh, and the new Visual Studio 2010-based shell for SQL Server Data Tools (formerly BIDS) is very pretty…

Analysis Services (SSAS)

There have been very few changes to the SSAS Multi-Dimensional model itself, something that has upset many core BI professionals. Instead, Microsoft has focused its efforts on implementing their new BI Semantic Model (BISM), which allows users to combine SSAS data models with PowerPivot to create a semantic layer for reporting. A large part of the BISM is the new SSAS Tabular model, a new way of creating SSAS data models which does away with facts and dimensions and instead focuses on tables and relationships, using in-memory storage (the aforementioned xVelocity engine) to create a model that can perform complex queries and calculations extremely quickly, provided your server has the required grunt.

By combining PowerPivot with the SSAS models, Microsoft has made a huge push towards self-service BI and has opened up the SSAS stack to power users, as well as hardcore BI pros. This remains a heated topic for debate in the BI community, and Chris Webb has written a great article discussing the pros and cons of Microsoft’s approach. SSAS has definitely been made more accessible to non-BI professionals, but not necessarily to the detriment of the seasoned expert.

Reporting Services (SSRS)

Standard SSRS reporting hasn’t changed much since 2008 R2. In fact, the same XML schema from the previous release is still in operation. The major development on the reporting front is the launch of a little something called PowerView.

The Silverlight-powered, SharePoint-hosted PowerView essentially gives any user the ability to create their own reports in a matter of seconds. If the BISM gives power users access to MS BI, PowerView extends that reach to even end-user level. A simple drag-and-drop interface allows users to swiftly slice and dice data and create visualisations with a couple of clicks. The neat ability to export to PowerPoint for live presentations, or embed the control in web pages (with some jiggery-pokery) means that PowerView can be made portable.

It’s not perfect. Not by a long shot. But it’s a solid start, and a signal of intent from MS as to what they intend to do with SSRS in the long run. Putting reporting control (at least dashboard/prototyping for now) into the user’s hands can free up the BI pro/power user to put their time into analysis and innovation, rather than fiddling with UI design. I’ll be watching Microsoft’s moves in 2013 with great interest.

Excel 2013

I couldn’t write this post without commenting on the direct integration of PowerPivot/PowerView into Excel 2013. Removing the SharePoint dependency for PowerView has squarely put control into the user’s hands, and the fact that a data model and UI can now be produced solely in Excel (the analyst’s tool of choice) cannot be understated.

The Year Ahead

It’s hard to predict what’s going to happen in the coming year, but it looks certain that Microsoft will continue to push Self-Service BI and add more functionality to PowerView. Microsoft’s Mobile BI offering “Project Helix” should be properly revealed, bringing MS BI to mobile devices and Windows 8. SharePoint 2013 will be out soon, allowing direct publishing from Excel 2013 and this is almost certainly only the tip of the iceberg. Microsoft is looking committed to the Self-Service route, but it will be interesting to see what they do in response to the BI professional community’s concerns.

It’s been a busy year here on picnicerror.net, and I’ve got a number of posts lined up to bring in 2013 (mostly ones that I’ve not had time to finish during 2012). I’ve got an alternative to the ForEach File Enumerator in SSIS, an experiment attempting to embed traditional SSRS reports into PowerView and an in-depth examination on the future of SSRS.

For now though, let me sign off for the year by wishing everyone a Happy New Year when it comes, and all the best for 2013.