Understanding Analysis Services Deployment Modes

SQL Server 2012 Analysis Services (SSAS) supports three different deployment modes: Multidimensional, Tabular, and SharePoint. You can choose the deployment mode during Setup, as the following figure illustrates. The SharePoint deployment mode is implicitly chosen on the Setup Role page when you install PowerPivot for SharePoint. Multidimensional and Tabular modes, on the other hand, can be explicitly selected on the Analysis Services Configuration page.

The need for these deployment modes isn’t necessarily obvious. Why have separate deployment modes in the first place? Wouldn’t it be easier to have a single SSAS server instance capable of hosting Multidimensional, Tabular, and PowerPivot databases together? It would certainly help to reduce deployment and configuration overhead.

Most SSAS experts agree that it does make sense to keep Multidimensional separate from the other two deployment modes. The Multidimensional workload is just so very different from Tabular and SharePoint. At its core, Multidimensional uses an OLAP formula engine to process queries and a storage engine to read and write data from and to disk, while Tabular and SharePoint modes rely on the xVelocity in-memory analytics engine for calculating and storing data. If you are interested in all the technical details, check out the article “Comparing Tabular and Multidimensional Solutions (SSAS)” at https://msdn.microsoft.com/en-us/library/hh212940.aspx.

But why have separate Tabular and SharePoint modes if both of these use the same xVelocity in-memory analytics engine after all? Well, the first important reason is security. Tabular databases allow users to connect if they belong to at least one database role that grants them read access, while PowerPivot databases have no role-based security. In SharePoint mode, only SSAS server administrators can establish connections. In order to understand how security works in SharePoint mode, take a look at the following figure.

The screenshots show a SharePoint user (SPUser) successfully working with a workbook data model in the browser, but even if that user knew the SSAS server and database name that Excel Services chose for loading the model, the user cannot connect directly, such as by using the following PowerShell script. The error message clearly states, “Either the user, CONTOSO\SPUser, does not have access to the SharePoint20Images_0a816ebc5aab45ecba8c349efccc8e7c_8a3ac029cae74916a88547f7b61afd09_SSPM database, or the database does not exist. ”

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient")

$server = "AS2012SP1\POWERPIVOT"
$adomd = new-object Microsoft.AnalysisServices.AdomdClient.AdomdConnection
$adomd.ConnectionString = "Data Source=$server; Initial Catalog=SharePoint20Images_0a816ebc5aab45ecba8c349efccc8e7c_8a3ac029cae74916a88547f7b61afd09_SSPM"
$adomd.Open()
$cmd = $adomd.CreateCommand()

$cmd.CommandText = "SELECT NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({[Gifs].[SizeBucket].[All]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,MEMBER_VALUE ON COLUMNS FROM [Model] WHERE ([Gifs].[FileType].&[config],[Measures].[Count of FileType]) CELL PROPERTIES VALUE, FORMAT_STRING, BACK_COLOR, FORE_COLOR, FONT_FLAGS"

$rdr = $cmd.ExecuteReader()

if($rdr) {$rdr.Close();}
$adomd.Close()

So, how is SPUser able to interact with the data model in the browser without access permissions? A quick check of a SQL Profiler trace reveals the magic. The trace shows that Excel Services connects by using its own service account (SPService) to load the model. This works because SPService is a SSAS server administrator. Having loaded the model, Excel Services then sends a SetAuthContext command to tell Analysis Services who the actual user is. Yet, SetAuthContext isn’t an authentication command. It simply switches the user context (see the NTUserName column). SPService is still the authenticated user for the connection. It’s just not displayed. Essentially, Excel Services tells Analysis Services, “I am SPService, who you fully trust as a SSAS admin, but for this session, please pretend that I am SPUser.”

Obviously, this way of connecting to a PowerPivot database is very different from connecting to a Tabular database, but the differences between Tabular and SharePoint modes go even deeper than that because the databases themselves are very different in nature. Tabular databases are permanent databases. PowerPivot databases, on the other hand, are temporary. They can be deleted from the SSAS server and reloaded from their workbooks at any time.

Specifically, SQL Server 2012 SP1 CTP3 (or later) Analysis Services takes full advantage of the temporary nature of PowerPivot databases for certain performance optimizations. Among other things, SSAS in SharePoint mode does not persist all database transactions on disk. With less disk I/O, performance increases, but there is an impact on durability. For Tabular databases, such an optimization would be a violation of core design principles, which require transactions to be atomic, consistent, isolated, and durable. But if transactions against PowerPivot databases are not durable, what happens when you restart the SSAS instance? Do you lose any transactions? As a matter of fact, you not only lose the transactions, SSAS goes even one step further and drops the databases altogether. Imagine a SSAS server would do that for Tabular databases! Starting with SQL Server 2012 SP1 CTP3, Analysis Services operating in SharePoint mode prefers to start from a clean slate. Again, this is OK because Excel Services can reload any data models from the workbooks. Note also that it usually isn’t required to restart the SSAS server to drop PowerPivot databases because Excel Services during normal operation automatically unloads data models that are no longer needed.

In summary, it is good to have three separate deployment modes for Analysis Services. Multidimensional is very different from Tabular and SharePoint, and you certainly don’t want to host Tabular models on a SharePoint-mode instance because the databases would be dropped and lost every time you restart the server. A subsequent blog post will go deeper into SSAS engine improvements for Tabular and SharePoint modes in SQL Server 2012 SP1. Stay tuned!