SQL Server 2012: good DV backend and BI stack

I said on this blog many times that 80% of Data Visualization (DV) is ... Data.

SQL Server 2012 is here.

And technology and process of how these Data collected, extracted, transformed and loaded into DV backend and frontend is a key to DV success. It seems to me that one of the best possible technology for building DV backend is around the corner as SQL Server 2012 will be released soon - Release Candidate for it is out...

And famous Microsoft marketing machine is not silent about it. SQL Server 2012 Virtual Launch Event planned for March 7, 2012 and real release probably at the end of March 2012.

Columnstore Index.

I already mentioned on this blog the most interesting feature for me - the introduction of Columnstore Index (CSI) can transform SQL Server into Columnar Database (for DV purposes) and accelerates DV-relevant Queries by 10X or even 100X of times. Oracle does not have it!

.

Some reasonable rules and features applied to CSI: each table can have only one CSI; CSI has Row grouping (about million rows, like paging for columns); table with CSI cannot be replicated. New (unified for small and large memory allocations) memory manager optimized for Columnstore Indexes, supports Windows 8 maximum memory and logical processors.

Power View.

SSRS (Reporting Services) got massive improvements, including new Power View as Builder/Viewer of interactive Reports. I like this feature: "even if a table in the view is based on an underlying table that contains millions of rows, Power View only fetches data for the rows that are visible in the view at any one time" and UI features (some of them are standard for existing Data Visualization tools, like multiple views in Power View reports (see gallery of thumbnails in the bottom of screenshot below):

.

"2 clicks to results", export to PowerPoint etc. See also video here:

.

.

PowerView is still far behind Tableau and Qlikview as a Visualizer, but at least it makes SSRS reports more interactive and development of them easier. Below are some thumbnails of Data Visualization samples produced with PowerView and presented by Microsoft:

Support for Big Data.

SQL Server 2012 has a lot new features like "deep" HADOOP support (including Hive ODBC Driver) for "big data" projects, ODBC drivers for Linux, grouping databases into Availability Group for simultaneous failover, Contained Databases (enable easy migration from one SQL Server instance to another) with contained Database users.

Parallel Data Warehouse, Azure, Data Explorer.

And don't forget PDW (SQL Server-based Parallel Data Warehouse;  massive parallel processing (MPP) provides scalability and query performance by running independent servers in parallel with up to 480 cores) and SQL Azure cloud services with it high availability features...

.

New Data Explorer allows discover data in the cloud and import them from standard and new data sources, like OData, Azure Marketplace, HTML etc. and visualize and publish your Data to the cloud.

LocalDB.

LocalDB is a new free lightweight deployment option for SQL Server 2012 Express Edition with fewer prerequisites that installs quickly. It is an embedded SQL Server database for desktop applications (especially for DIY DV apps) or tools. LocalDB has all of the same programability features as SQL Server 2012 Express, but runs in user mode with applications and not as a service. Application that use LocalDB simply open a file. Once a file is opened, you get SQL Server functionality when working with that file, including things like ACID transaction support. It’s not intended for multi-user scenarios or to be used as a server. (If you need that, you should install SQL Server Express.)

BIDS.

SQL Server 2012 is restoring a very desirable feature, which was missing in Visual Studio 2010 for 2+ years - something called BIDS (BI Development Studio was available as part of Visual Studio 2008 and SQL Server 2008). For that a developer needs VS2010 installed with SP1 and then install "SQL Server Data Tools" (currently it is in the state of CTP4, but I guess it will be a real thing when when SQL Server 2012 will be released to production).

SSAS, Tabular Mode, PowerPivot, DAX.

Most important improvement for BI and Data Analytics will be of course the changes in SSAS (SQL Server Analysis Services), including the addition of  Tabular Mode, restoration of BIDS (see above), the ability to design local multidimensional cubes with PowerPivot and Excel and then deploy them directly from Excel as SSAS Cubes, the new DAX language shared between PowerPivot and SSAS, and availability of all those Excel Services directly from SSAS without any need for SharePoint. I think those DV tools who will able to connect to those SSAS and PowerPivot Cubes will have a huge advantage. So far only Tableau has it (and Omniscope has it partially).

Backend for Data Visualization.

All of these features making SQL Server 2012 a leading BI stack and backend for Data Visualization applications and tools. I just wish that Microsoft will develop an own DV front-end tool, similar to Tableau or Qlikview and integrate it with Office 201X (like they did with Visio), but I guess that DV market ( approaching $1B in 2012) is too small compare with markets for Microsoft Office and SQL Server.

Pricing.

Now is time for a "bad news". The SQL Server 2012 CAL price will increase by about 27%. New pricing you can see below and I predict you will not like it: