SQL Server 2012 Analysis Services – An Overview
Microsoft SQL 2012 Analysis Services or SSAS has proved to be a major step for Microsoft for the company has been able to project itself as a leader in the field of voluminous data. It promises new, unique and customized solutions for the clients. Microsoft has released a ODBC driver for MS SQL Server that will be capable of running in a Linux environment, allowing the new version to deal with large amounts of data. PowerView with SSRS allows the users to create and consolidate BI reports from all through the Enterprise. The new version is also loaded with distributed relay that allows capturing of workload on the production server and playing the same on a different machine. This allows testing of the schema forming the basis, the support packs and hardware under specific conditions.
SQL Analysis Services
SQL Analysis Services are a layer of meta data or a semantic model that resides on the top of a data warehouse in a relational database. This is like an extra layer that stores information about how the fact and the dimension tables in SQL Analysis Services are to be joined and how measures are to be aggregated. It can also be said to be a cache that results in speedy reporting. Before we discuss the Analysis Services features available in SQL Server 2012 in detail let us briefly look at what SQL Server 2012 SP1 has to offer.
SQL Server 2012 SP1
It supports Power Pivot MS Excel 2013 and SharePoint and it comes with a brand new architecture based on utilization of Excel utilities for querying, refreshing, loading and saving of the data, which are supported outside the SharePoint 2013 farm. The experience of the user is further enhanced through spPowerpivot.msi which is nothing but an installation package for the windows environment.
This is the result of additional features like the PowerPivot Gallery (SSAS) and the management dashboard. It also incorporates latest Analysis Services in the Tabular mode which includes KPI storage and measures; categorization of data; character extensions and hierarchy annotation. SP1 allows specifying the level of compatibility at the time of creating new Tabular model projects.
SQL Server 2012 SSAS in addition to the earlier features is loaded with new features that help in decision making and data analysis. A prominent feature is introduction of enhanced PowerShell Support and replacement of the Analysis Services Unified Dimensional Model with a hybrid BISM model (BI Sematic Model) permitting BI to be experienced in the SQL 2012 Server environment. The article discusses new features of Analysis Services in detail. For achieving better understanding the new features have been clubbed under various headings.
The new features available with SQL Server 2012 SSAS can be clubbed under seven heads:
- SQL Server 2012 Instance/Monitoring
- Analysis Services Tabular Modeling
- SSAS Multidimensional Modeling
- PowerPivot for Excel
- PowerPivot with SharePoint
- SQL Server SSAS 2012 Programmability
- Design Tools
Discussing each of the heads and the features that can clubbed under them in detail:
SQL Server 2012 Instance and Server Monitoring
When it comes to new features in Server Instance and Server Monitoring, one of the features is – Availability of 3 analysis services modes. Three server modes Multidimensional and Data Mining which is the default mode, PowerPivot for SharePoint, and Tabular are available for analysis services. The type of solution that is created and deployed depends on the type of server mode. The snapshot illustrated below shows the three deployment modes.
Figure 1: Snapshot illustrating the three deployment modes
In addition to this the xVelocity In-Memory Analytics Engine is available for Tabular Modeling model DBs. Using the in-memory storage it does calculations for collecting the data and manipulating it the moment it is requested.
The next feature that has been added is making available new schema rowsets and a DMV query interface to support the xVelocity engine for data warehousing and latest architecture of SQL Server 2012 PowerPivot service pack 1.
The feature to trace events is yet another important aspect of this version which makes it supportive of the SQL Server Extended Events Framework.
This can be enabled with the help of an XMLA create object script as shown in figure 2 below. For stopping tracing objects, the object has to be deleted using the XMLA delete object script as shown in figure 4 below.
Figure 2: XMLA create object script for enabling SQL 2012 Sever Extended Events Framework.
Figure 3: XMLA delete object script for stopping SQL Sever Extended Events Framework.
Analysis Services Tabular Modeling
The second head of features are clubbed under Analysis Services Tabular Modeling. The features that are available under this head are – Integration of the Tabular Designer Model with SQL Server 2012 Data Tools, availability of administrative support for tabular model databases that can be independently on an Analysis Services server. This version also introduces the SSAS Tabular Model Designer Diagram View which displays inter related tables using a graph and allows filtration of columns, hierarchies and measures. Another feature that can be discussed under this head is partitioning of tables into logically partitioned objects allowing processing of each partition independently. There are some excellent features related to security. While allowing definition of the security roles by defining the permissions of various members included in a tabular model it also allows row level security restricting the access to data.
KPIs can be used for analyzing the performance of a variable by using base and target values. Hierarchies that exist in the form of metadata can be used for determining the relationship between columns. These can be made to appear separately from the rest of the columns. This particular version has no restriction in terms of number rows that a table can contain.
Two important features in this version are – The DirectQuery Mode and the DAX. The DirectQuery Mode that is available in this version provides with an option to deploy in tabular projects. This allows the users and the reporting clients to retrieve the data right from a MS SQL 2012 Server. DAX can be used with both PowerPivot Multidimensional Modeling and Tabular Modeling allowing use of statistical functions, search functions and table functions.
SSAS Multidimensional Modeling
The next head under which features of this SSAS release can be clubbed are multidimensional modeling. The release carries a new option where it is possible to remove a limitation on file storing strings allowing the files to grow as big as they can. In the earlier versions the maximum size of this file could be 4 GB at the most. Any file beyond this size would face an error. It does not mean though that there is no limit on the file now but fortunately it is not regarding the size any more but only limited by the count of strings that could be place in this file.
Other important features available in the release is collection of metrics related to resource usage right at the cmd level while processing a query & is known as Resource Usage Reporting for SSAS Multidimensional Databases.
Two more enhancements over the earlier versions that can be clubbed under this head are – the ability to trace events for the purpose of troubleshooting lock related query or problems related to processing and using DistinctCount in order to help in running the process much faster for the operation gets over to the relations DB engine. Queries can run faster in the DistinctCount process if the criterion that has been laid down is fulfilled. The performance improves as a result of the fact that the operations are offloaded and placed on the relational database engine. The optimization process is not available as a default action. Optimization requires that the following conditions be fulfilled – Presence of one partition for every measure group; absence of random shapes in queries and the relational DB engine should be on SQL Server 2005 or a later version.
PowerPivot for Excel
SQL Server 2012 SSAS introduces PowerPivot for Excel. Authors can be added and workbooks published using Excel 2010 in SQL Server 2012 and as mentioned above this release also introduces the DAX functions that allows introduction of new statistical functions.
The next two screen shots display more info about SQL PowerPivot
PowerPivot with SharePoint
For the release version 2012, a group of features can be clubbed under the heading PowerPivot SharePoint. This version provides an excellent tool for the purpose of installing PowerPivot SharePoint. The first step that the tool undertakes is to perform a scan and determine if PowerPoint configuration is there so that if not, the necessary steps are provided for deployment of operational server. The release also introduces PowerShell Cmdletss for configuration of PowerPivot SharePoint. By using a combination of SharePoint Cmdletss with the newly introduced PowerShell Cmdletss, a PowerPivot SharePoint can be configured. Another feature under this heading is known as a BI Semantic Connection which provides a HTTP endpoint to Analysis Services DBs deployed on a server in the standalone mode. This release provides an improvement in the administration of PowerPivot SharePoint deployment by addition of extra settings. These help detection of problems in advance.
Another feature that is available is the ability to configure automatic upgrade in PowerPivot SharePoint which ensures that the workbooks in SQL 2008 R2 are always up-to-date in tune with the most recent version. This allows data to be refreshed for SQL Server 2012 PowerPivot SharePoint Server workbooks.
Here is the architecture of PowerPivot SharePoint with relation to SQL 2012.
SQL Server 2012 SSAS Programmability
As discussed above the release version 2012 provides DAX functions in both tabular projects and PowerPivot workbooks that are built in the Microsoft SQL Server Data Tools. The release has introduced more than thirty new functions in various categories like statistical, table and search along with security at the level of rows.
Another distinctive and important feature of this version is introduction of PowerShell Cmdlets for AMO enabling navigation, the ability to connect via cmd line, and addition of Analysis Services DBs/databases running on a server in either mode namely Multidimensional Modeling or Tabular Modeling.
This release version also introduces PowerShell Cmdlets for the purpose of configuration of Power Pivot for SharePoint. This can be done by using both SharePoint PowerShell Cmdlets and new ones. A PowerPivot SharePoint installation can be installed through the PowerShell script.
Version 2012 also provides extensions for Automation Management Objects as well as XML for Analysis which are made available in order to provide support for Tabular Modeling and both these types of models use the united BI semantic modeling schema. It helps to utilize different kinds of Application Programming Interfaces. Servers running in either mode can be connected using this feature with the help of Object Linked Database or ADO Multidimensional Modeling.NET.
Last but not least yet another feature which falls in this category of programmability is the CSDL or Conceptual schema definition language which is always useful for representing the tabular model as a response to a query initiated by a client. The nature of the query is such that it can use the schema and the representation for creating visualizations like the Power View. An additional feature to Conceptual schema definition language is the bi: extension which includes various entities, data types and relationships for the tabular models.
In addition to the above, the SQL Server 2012 provides updated provisions SQL Data Tools that can be run in the Visual Studio shell. Tabular and Multidimensional projects are created in the SQL Server 2012 Data Tools and the Visual Studio shell incorporates many IDE enhancements like better readability and maintenance of more than one monitors.
Related links to SQL Server 2012 SSAS