SQL Server 2012 has finally arrived. This is a significant release that packs a lot of new features. Some are brand new, groundbreaking parts of the stack—Power View, Analysis Services Tabular models, column store indexes, Data Alerts, Data Quality Services— and others have matured significantly— Database Mirroring with AlwaysOn Availability Groups, SQL Server Data Tools, PowerPivot, Master Data Services. I’ve mentioned only a handful, but the full list of new features is quite extensive. Check it out here. The following details some of the features that are most useful for the work that we do.
Analytics & Self-Service BI
- Power View is an innovative interactive data discovery tool that delivers a great combination of high quality data visualizations in an extremely easy to use tool.
- Power Pivot has started to close the feature gap with Analysis Services multi dimensional model. It has added key components such as: KPIs, hierarchies, and perspectives.
- Analysis Service Tabular model brings the PowerPivot engine to the server, adding a few key features to make a server side solution more maintainable and scalable.
- Column store indexes will bring the same in-memory engine that powers PowerPivot and the Tabular model to the database engine, where you can query the table(s) using T-SQL.
- While not part of SQL Server 2012, Microsoft is also joining the Hadoop ecosystem. It will be possible to run Hadoop on Windows Server and in Azure. This is the official (Apache) Hadoop distribution, with enhancements for the Azure and Windows Server Platforms.
- Microsoft contributions in this space, especially with the ODBC driver for Hive, blow the doors open to the capabilities of the platform. Data from Hadoop and Hive can be directly imported into PowerPivot and the Tabular model and combined other sources to produce high quality visualizations. More from me on this later…
- AlwaysOn Availability Groups take database mirroring to the next level. By adding multiple and readable secondary instances, it allows for more highly available solutions.
Self-Service BI and Big Data
With the release of SQL Server 2012, Self-Service BI and Analytics has truly become self-service. The three prior versions have incrementally added pieces to improve the self-service experience of users. While improved in the prior versions, the self-service experience has been challenging. ReportBuilder for Reporting Services has evolved to give consumers the ability to build their own reports. Generating a pixel perfect report that renders on a physical page presents its own challenges. Even with report parts, creating visualization takes a bit of effort and training. Likewise, with Performance Point, the Dashboard Designer allows anyone to create dashboards, but any complicated dashboard still needs to be designed by a power user or developer. Excel gives the ultimate flexibility for self-service analytics and is considered THE most widely used BI tool. Its not a coincidence that Microsoft has made it a centerpiece of its self-service BI message. However, the presentation of content is typically not high quality.
PowerPivot opened the doors as pure self-service data discovery tool. It gives anyone with Excel the ability to import data from a variety of sources and create a personal data model. The data model can be immediately reported on using pivot tables inside that Excel workbook making it an extremely versatile Personal BI tool. Just by deploying this workbook to a PowerPivot Gallery in SharePoint (that has PowerPivot for SharePoint properly configured), the same workbook can be accessed by any reporting product that issues MDX queries. This includes Reporting Service, Performance Point, Excel pivot tables, and other third party visualization products. Nothing about the workbook has to change to make that possible. If you are a PowerPivot enthusiast, you already know that these features were all available in SQL Server 2008 R2.
SQL 2012 takes it to another level. With the Tabular model, the same PowerPivot workbook can be imported into a Analysis Services with a few clicks and is immediately ready to scale beyond workbooks deployed to a SharePoint library. This is a really great message for self-service data discovery and data modeling. This same workbook integrates seamlessly with all the traditional reporting tools.
While PowerPivot unlocked the ability for ad-hoc data modeling and analysis, Power View (new in SQL Server 2012) brings the self-service capabilities of the platform full circle with its unique blend of high presentation quality graphics and extremely easy to create data visualizations. I can’t emphasize enough how extremely easy Power View is to use. This is the main point I communicate to our customers when talking about the benefits of the SQL Server 2012 BI platform. There are a number of visualizations in Power View that are able to clearly demonstrate patterns in your data. Just by adding them to your canvas, the visualizations are automatically connected to each other for cross filtering. Trying to do this in any other product requires a bit of work and is not for the faint of heart. It is really a fantastic data discovery tool.
Another fantastic feature of Power View is that is exports its data views directly to PowerPoint. Typically, a user trying to put together a presentation based on a set of reports and other visualizations will take screen shots and paste them into their slides. When you export the views to PowerPoint, you can render the reports live from the presentation. It is the same interface you would get through your web browser and it is fully interactive. A user making the presentation can clearly articulate the story the data is telling and be able to answer questions directly inside of the presentation. And the best part is that the data is maintained outside of PowerPoint so that the next time you open your presentation, you get the latest data!
I have spent most of this post discussing SQL Server 2012. How does Big Data play into this? As discussed in a previous post, one of Microsoft’s contributions to the project is an ODBC driver for Hive. If you have a very large warehouse running in Hadoop, you can issue a query to Hive via the ODBC driver and pull the strategic and probably aggregated data into either PowerPivot or the Tabular model directly. This kind of analysis is extremely powerful and definitely self-service.
SQL Server 2012 brings a lot to the table. Key to what’s new is the true self-service nature of the tools in this platform. Data modeling, exploration, and data visualization in the platform is truly self-service and extremely easy to use. Now that Microsoft has put their chips in the Hadoop ecosystem, it really can deliver an extremely robust platform for ALL data.
This post can’t do justice to the amount of new features that are in this release. Please keep visiting www.bluemetal.com as we dive into some more specifics in future posts!