Moving On From SQL Server Analysis Services Multidimensional – Part 1
SQL Server Analysis Services Multidimensional (SSASm) was first offered in 2000, and soon became the dominant cube server product on the market. It got a ground-up rewrite in 2005 and further enhancement in 2008 that greatly increased its capabilities, performance and proliferation. SSASm uses the expression and query language MDX (multi-dimensional expressions), known for its power and steep learning curve. SSASm grew into an amazingly powerful and widely-used product, the more impressive given it is bundled for free with a SQL Server license (sometimes you get more than you paid for). Because of this bundling it is difficult to know how many production instances of SSASm are running, but even a small fraction of the number of SQL Server licenses would be significant.
In 2010, Microsoft introduced a new in-memory, columnar database technology named variously xVelocity and Vertipaq. This new technology clearly overlapped the capabilities of SSASm, but for some time it was unclear, seemingly even to Microsoft, whether it was a replacement for SSASm or something else. Its architecture and conceptual model were completely different from SSASm, yet there was that overlap.
The new technology was initially embedded in the Power Pivot add-in for Excel. 2012 brought a stand-alone server version called SSAS Tabular (SSASt), and a few years later, a native Azure service called Azure Analysis Services. Like SSASm, the server version of SSASt is bundled free with a SQL Server license, and has its own distinct expression and query language named DAX (data analysis expressions). Microsoft initially marketed SSASt and DAX as simpler and easier to use than SSASm – but today it is recognized that these have at least as steep a learning curve. See Appendix 1 for more on why Microsoft shifted focus to SSASt.
The advent of SSASt – along with the simultaneous growth and power of Azure – fueled a major parallel product evolution for Microsoft that we know over the last several years as Power BI (PBI). Unlike SSASm, which is not designed for the cloud and relies on Excel and third-party tools to visualize and share its data, PBI uses SSASt as its data store and also includes data visualization authoring, sharing and related functionality one would expect in a comprehensive business intelligence suite in one seamless, cloud-enabled product with simple licensing.
How Does This Matter to SSAS Multidimensional ?
For the last 10+ years, Microsoft has focused all its data analytics investments on both versions of SSASt and Power BI. SSASm has not had significant enhancement since the SQL Server 2008R2 release. Microsoft maintains that SSASm and SSASt/Power BI are complementary offerings aimed at different use cases. When used as stand-alone server products, this claim has credibility at the edges (e.g. extreme data volumes, certain complex calculations, extreme query performance), but for the vast majority of cases, either could be used with satisfactory results. This overlap, the comprehensive all-in-one capabilities of Power BI, and the glaring disinvestment in SSASm suggest that its days may be numbered, whatever Microsoft periodically says to the contrary (think Silverlight). It is true though that SSASm can handle virtually unlimited data, while SSASt is limited to what can fit in memory – which even with its highly efficient compression is orders of magnitude less data than SSASm could handle. This may provide a niche that extends SSASm’s lifetime.
Sidebar: SSASm has always been a 3rd-class citizen as a data source for SQL Server Reporting Services (SSRS). According to Wikipedia, Power BI was the brainchild of the Microsoft SSRS team. It is interesting to speculate whether these observations and the disinvestment in SSASm are not unrelated.
With the long-term future of SSASm thus possibly in doubt, if you use it, you are well advised to consider “modernization” options available under Microsoft offerings. There are essentially two:
- Port your SSASm implementation to a server-based SSASt implementation, either on-prem, in a VM, or Azure Analysis Services. This would be appropriate if, for whatever reason, you prefer to maintain a server-based implementation and/or do not wish to use Power BI. This path gives you maximum control of SSASt configuration and monitoring if on-prem. Later use of Power BI remains possible as well, since it can consume SSASt both on-prem (via the Power BI gateway) and from Azure Analysis Services. Under this option, the modeling tool, as for SSASm, would be SQL Server Data Tools (SSDT), a Visual Studio framework.
- Port your SSASm implementation directly to Power BI. This path should be similar to the prior option, as either way the data model and calculations would need to be ported to SSASt and DAX. Under this option, the modeling tool would be Power BI Desktop. The effort, though the tool used differs, should be either the same or slightly less, since Power BI manages the configuration and running of a hidden version of SSASt that you would have to manage yourself with the prior option.
Because the research, design and implementation tasks to port the data model and calculations under either option would be about the same, for simplicity the remainder of this series will treat the conversion effort as not affected by which path is selected, even though this may not be strictly true in all cases. “SSASt” will represent the target for both porting paths. I will not have anything to say about the visualization side of Power BI. I will also focus only on the conceptual concerns, not on how to use SSDT or Power BI Desktop to do the work, for which countless other resources are available. I will assume that the reader has at least basic familiarity with SSASm and preferably also SSASt.
Comparing SSASm to SSASt for Porting Purposes
As noted earlier, the majority of SSASm implementations likely overlap with SSASt capabilities in terms of data volume and economics (even allowing for the cost of sufficient memory for SSASt). Thus, most of the time those factors will not be the deciding ones when assessing the viability of porting one to the other – and even more so when deciding for new development (always use SSASt). But there are functional differences between the two that are critically important to both the practicality and effort involved in porting. In some cases these differences may be such that, if you do believe Microsoft’s claims of the continuing viability of SSASm – and you do not have an overriding need to move to the cloud (beyond running SSASm in a VM) – remaining on SSASm may be the most sensible option.
Assuming that neither data volume nor economics are deciding factors, the following are broad areas for comparison and assessment:
- Data and computation models supported
- Capabilities and limitations in terms of:
- data modeling techniques and query/expression language
- data volumes
- ancillary features such as partitioning
- Implicit behavior when querying
- Security and Entitlement
- Skill sets and/or external help available
The difficulty in porting will be directly proportional to the amount of SSASm and MDX functionality that is leveraged in the existing application. On the other hand, much (though not all) SSASm capability that would be difficult or impossible to port is not often used.
General Considerations in Porting
SSASm and SSASt, and their respective query/expression languages MDX and DAX, implement very different abstractions and implicit behaviors. The mental models required to maximally leverage each are quite different.
SSASm virtually requires a Kimballesque star-schema data model as its source data, and for this reason most installations will already have one – which is great news for porting to SSASt because it is generally easy to model such in SSASt, at least to a first approximation. This does not mean, however, that the port is simple – this would be only the first step. NOTE: while not necessarily relevant to conversion scenarios, I must point out that SSASt is much more flexible than SSASm in the data models it can support in a practical manner – it can work well with non-star-schema data models too. In the context of porting, this might open new opportunities, e.g. integration of new data sources.
I hope you are wondering what impact porting would have on continued use of consumers, whether explicit MDX queries, Excel, SSRS or 3rd party tools. If so, read on.
If you are running SQL Server Analysis Services Multidimensional, there are good reasons to at least consider porting the data model and calculations to SQL Server Analysis Services Tabular, Azure Analysis Services or Power BI. This is both because there is reason to doubt the long-term support of SQL Server Analysis Services Multidimensional by Microsoft, and because the latter two options enable leveraging the ever-growing functionality available in Azure, including minimal need to manage infrastructure, access to machine learning enrichment, monthly upgrade cycles (Power BI) and so much more.
In Part 2 (and possibly a Part 3) of this series I will look in more detail at differences between the two platforms as they pertain to porting one to the other, with a view to highlighting difficulties, opportunities and workarounds. I will also address the question of porting’s impact on your consumers. This will give you a start at assessing the challenge of doing so, should you be convinced it is worth considering.
If you have a short-term need and can’t wait for Part 2, feel free to reach out via the Comments section and I will help if I can.
When SSASm was originally designed in the mid-late 1990’s, the per-Gb cost of disk was a small fraction of that for memory. In addition, CPU and OS architectures (32-bit limitations were still prevalent), along with memory economics, limited total memory to a small fraction of what was, as a practical matter, unlimited disk storage. SSASm was architected with these realities of the time in mind, so while it leverages memory and compression as much as possible, its design is and had to be fundamentally disk-oriented. While this has potential query performance implications, it also means SSASm can handle huge amounts of data – the largest documented example being 24 Tb, compressed from a 2 Pb source at Yahoo.
In contrast, SSASt leverages the realities of when it was born, namely columnar/bitmapped database technology, much cheaper memory, and much higher limits on address space size in Windows Server, and implements a fully in-memory architecture. Since all the data must fit in memory at once, this imposes an upper limit, even with today’s economics, on the data volume SSASt can handle which is still orders of magnitude lower than SSASm. At the same time, SSASt has possible query performance orders of magnitude faster than SSASm could ever achieve, enabling applications previously not practical.
Microsoft’s positioning of the two products as complementary is thus credible, but in practice this may only really apply to edge cases so few in number as to not impact product direction.