I wanted to dedicate a series of blog posts to my good friend SQL Server Analysis Services (SSAS) Multidimensional. With the release of SSAS Tabular, it seems SSAS Multidimensional has become increasingly lonely. SO, I am going to give it some love here…
In this series I will cover some common best practice techniques in relation to scalable SSAS multidimensional cube design. From dimension design to SSAS engine internals these posts will explore SSAS multidimensional design patterns that will hopefully assist in getting your SSAS cubes better optimized to better match the needs of your organization. With the use of the posts within this series you will be able to take these performance design patters and apply them to new and existing cubes to tune your SSAS cube environment.
Flavors of Analysis
Now these posts are specifically addressing SSAS multidimensional, but there are items that do apply to the other flavors of SSAS (tabular). Where there is commonality, I will highlight them, but these posts will not be dedicated to performing a comparison and contrast between SSAS Tabular vs. SSAS Multidimensional (look for future posts on this topic).
The following flavors of analysis are present with today’s latest releases of SQL Server and Microsoft Office:
- PowerPivot – With PowerPivot, you can create your own data model from various data sources, modeled and and structured precisely to fit your needs.
- PowerPivot for SharePoint – Microsoft SQL Server PowerPivot for SharePoint 2010/2013 and Excel Services add server-side processing, collaboration, and document management support for PowerPivot workbooks that you publish to SharePoint in the PowerPivot Gallery.
- Analysis Services Tabular – Tabular models are in-memory databases in Analysis Services. Using compression algorithms and multi-threaded query processing, the xVelocity in-memory analytics engine delivers fast access to tabular model objects an data by client applications such as Excel and Power View.
- Analysis Services Multidimensional – An Analysis Services multidimensional solution uses cubes for analyzing data across multiple dimensions. It includes a query and calculation engine for OLAP data with MOLAP, ROLAP, and HOLAP storage modes.
Benefits of Using SSAS Multidimensional
Each of the previously mentioned solutions provide their unique contribution to providing benefits for reporting and analytic capabilities. The benefits of using multidimensional solutions is to achieve fast performance of ad-hoc queries against business data. A multidimensional model is constructed of cubes and dimensions and can be extended to support complex query scenarios. Cube developers create cubes to support fast response times and to provide a single data source for business reporting and analytics. Having a single source of data ensures that if one user runs a report or analytics on inventory, product sold, dollars collected, etc. all other users should receive the same results. Thus insuring discrepancies are kept at a minimum or hopefully completely eliminated.
Another important benefit to using SSAS Mutlidimensional is the integration it has with reporting tools such as Excel, Reporting Services, and Performance Point.
In the next post, we will discuss best practices in relation to the benefits a well-designed relational dimensional model can provide.