Last week Microsoft released SQL Server 2012 "Denali" as CTP3 (Community Technology Preview). With Denali, Microsoft's BI Stack became so rich
with important and new cool functionality, that we decided to blog about the 3 most important features:
- Tabular Data Model;
- Business Intelligence Development Studio (BIDS for Visual Studio) 2010;
- Columnstore index.
Of course major news is availability of Tabular Data Model, which means VertiPaq in-memory columnar Engine, similar to PowerPivot Engine but running on Server without any SharePoint and its limitations and I quote Microsoft: " In contrast with the previous release, where VertiPaq was only available via in PowerPivot for SharePoint, you can now use VertiPaq on a standalone Analysis Services instance with no dependency on SharePoint."! SSAS (SQL Server Analysis Services) has new (that may existed before, but before CTP3 - ALL who knew that were under NDA) features like
- memory paging (allows models to be larger than the physical memory of the server, means unlimited scalability and BIG Data support),
- row level security (user identity used to hide/show visible data),
- CTP3 removes the maximum 4GB file size limit for string storage file,
- CTP3 removes the limit of 2 billion rows per table (each column is still limited to a maximum of 2 billion distinct values, but in columnar database it is much more tolerable restriction!
- among other new features: KPI and Partitions;
The new version of PowerPivot is released with support of Tabular Model and I quote: "You can use this version of the add-in to author and publish PowerPivot workbooks from Excel 2010 to Microsoft SQL Server" and it means no SharePoint involvement again! USers can import existing PowerPivot workbooks into a Tabular project. It is clear now, how important new expression language DAX is for future MS-based analytical applications.
As part of CTP3 Microsoft releases new Business Intelligence Development Studio (BIDS for Visual Studio was delayed for 3 years) 2010 and that is huge for development community, I quote again: "The Tabular Model Designer ... is now integrated with Microsoft SQL Server “Denali” (CTP 3) Business Intelligence Development Studio." It means that BIDS now is not just available but is the main unified development interface for both Multidimensional and Tabular Data Models. Now we can forget about Visual Studio 2008 for BI projects and finally use more modern VS2010!
Another important for Data Visualization feature is not in SSAS but in SQL Server itself: Columnstore index is released and I a quote 1 more time again: "The ... SQL Server (CTP 3) introduces a new data warehouse query acceleration feature based on a new type of index called the columnstore. This new index ... improves DW query performance by hundreds to thousands of times in some cases, and can routinely give a tenfold speedup for a broad range of decision support queries... columnstore indexes limit or eliminate the need to rely on pre-built aggregates, including user-defined summary tables, and indexed (materialized) views. Furthermore, columnstore indexes can greatly improve ROLAP performance" (ROLAP can be used for real-time Cubes and real-time Data Visualizations).
All these cool SQL Server 11 new features are coming soon into Azure Cloud. As we know now the newly coined by Microsoft term BISM (Business Intelligence Semantic Model) was a marketing attempt to have a "unified" umbrella for 2 different Data Models and Data Engines: Multidimensional Cubes (which is the foundation for SSAS and MDX - SQL Server Analysis Services) and Tabular Model (used in PowerPivot and VertiPaq in-memory columnar Database with new DAX Language which is going to be very important for future Data Visualization projects).
New CTP3-released BIDS 2010 UI-wise will able to handle these 2 Data Models. Even more: if you need both Data Models to be present, you need to install 2 (TWO!) different instances of "Analysis Services": one with Multidimensional Engine and one with new Tabular (VertiPaq/PowerPivot) Engine. It seems basically as Dual "BI" architecture, interface-glued on surface by BIDS 2010 and on back-end by all kind of Data Connectors. Basically Microsoft can afford 2 BI Architectures and it means more demand for PCA services and expertise! We are anticipating the release of SQL Server 2011 into production in a few months
Andrei Pandre, Ph.D., VP of Data Visualization, Practical Computer Applications