Why do we need SSAS (2016 or in general)?

SQL Server 2016 is coming (SQL 2016 GA) in June!

There are many new features and improvements across the platform that will undoubtedly open new doors for companies to manage, secure and analyze data at high scale, availability and resource efficiency levels.  In many ways SQL Server 2016 both responds to the modern data environment (e.g. cloud, big data), workload and business requirements while also builds toward a unified, symmetrical toolset between on-premise SQL Server and Azure.

This blog is of course focused on business intelligence so I won’t be reviewing new features like Stretch Database (hybrid storage between on-prem and Azure), Always Encrypted or AlwaysOn improvements but I do look forward to exploring the nonclustered and clustered columnstore indexes and opportunities and considerations for Real Time Operational Analytics – essentially the application of columnstore indexes to OLTP tables (disk or in-memory)to simultaneously support analytics and transactional processing. Increasingly BI and DW teams will need to partner with other data teams on providing ‘real time’ and streaming analytics while not disrupting transactional processing.

Before diving into the new features of SSAS 2016, here are 5 top reasons why SSAS is important and often essential to business intelligence projects and initiatives:

• Usability: An Analytical Layer (BI Semantic Model)  to Client Tools

Without SSAS databases, the business users, BI developers and client tools are left to deal with many source tables, often in multiple systems, and have to implement their own logic, filters, relationships, and update processes to make this data useful. This dramatically increases the time and cost of delivering BI, limits self-service ‘data exploration’ benefits, and also increases the risk of incorrect results.

An SSAS database, even if the data stays in the source system such as with Direct Query mode, is a simple interface for browsing – it allows users to ‘work with’ the data as they see it rather than just consume an output or apply a few pre-set filters/parameters.

• Version Control

Business logic, definitions, and metadata can be embedded into the SSAS database to provide a curated, central source of ‘truth’. Once the definitions are agreed to, business users, client tools and reports can leverage the same metrics, dimensions and attributes of the SSAS database. Business logic is often complex enough that a reporting or visualization tool alone would struggle or fail to meet this demand but SSAS can provide KPI values and target definitions, detailed formatting, and default behaviors and descriptions to further drive understanding and consensus on the meaning of the data.

• Centralized Security and Perspectives

Although an integrated, curated data store for many users to access is a great thing, it’s also necessary to define which users can see what data. Role-based and row-level security in SSAS meets this need and perspectives, though distinct from security, also greatly assist with larger SSAS databases by allowing users and developers to focus on the objects of the database most relevant to them.

• Performance and Scalability

An SSAS database is designed as a product and by the developers of the solution to support fast query response times and large datasets. Many factors contribute to this – for SSAS Tabular it’s high bandwidth RAM data storage, columnar layout and compression and for SSAS Multidimensional it’s aggregations, partitions, and Solid State Drives for data storage.

• Path from Self Service to Corporate

Whether the business user/team built their own Power Pivot or PBI Desktop model or one of these tools was used as a pilot or prototype, it’s important to be able to ‘scale up’ this self-service solution to support more data and quickly and easily. It’s been possible to import a Power Pivot for Excel file to an SSAS Tabular model since SQL Server 2012 and, as I understand it, it will soon (later in 2016) be possible to import a Power BI Desktop file implying some level of Power Query support. SSAS (and SSRS and SSIS) haven’t supported Power Query (M functional language) so this would significantly impact the lifecycle of BI projects.

*There are other benefits related to the manageability and processing but I hope these five reasons convince you that SSAS still has a very important role to play in BI delivery and architecture

Next week I’ll go into the features and enhancements of SSAS 2016 that I’m most interested in.  Here’s a quick preview/overview:

• Performance and Scalability Improvements for SSAS Tabular
• Enhanced and Extended Direct Query Mode
• Variables and Calculated Tables
• New DAX Functions
• Design and Development Experience in Visual Studio
• Administration and Monitoring (Tabular Model Scripting Language, Powershell and SSIS)