In the previous part of this series, we covered a detailed comparison between the three implementations of the SQL Server Analysis Services (SSAS) Business Intelligence Semantic Model (BISM). In this 3rd and last part of the series, we will look at the Considerations, Recommendations, Best Practices, and few Tips and Tricks.
Here are few considerations while choosing between Multidimensional and Tabular Models. Use these as guidelines while choosing between the different implementations of BISM apart from the comparison matrix in the previous article.
SSAS Tabular Model feature is available only in Business Intelligence and Enterprise editions of SQL Server 2012.
An SSAS instance can be either in Multidimensional or Tabular modes but not both at the same time. If you have both Multidimensional and Tabular models in your organization, then you need to install two different instances of SSAS – One in Multidimensional (Traditional OLAP) mode and another one in Tabular mode.
Both Multidimensional and Tabular Models are different and once the development has been started, one cannot switch from one model to another model.
There is no straight forward way to transfer/sync data between Multidimensional and Tabular Models.
PowerPivot has a limitation of 2GB on the workbook primarily due to the limitation enforced by SharePoint file upload size when the PowerPivot for Excel workbooks are uploaded to SharePoint.
Tabular models use DAX for Calculations and Querying, which is very easy to learn and implement compared to MDX.
Here are few recommendations on choosing between Multidimensional and Tabular models.
SSAS Multidimensional Model: Use Analysis Services Multidimensional and Data Mining Models if any of the following, but not limited to, criteria are met:
- You have a large amount of data with complex requirements.
- You need the data mining capabilities.
- You need features like Actions, Translations etc.
- You need security at the most granular level possible (Cell level security).
SSAS Tabular Model: Use Analysis Services Tabular Models if any of the following, but not limited to, criteria are met:
- You have a short development cycle. Want to ship something really quick and get a feedback from the field (end users).
- Your data model is relatively simple. Also, the tables do not necessarily need to be Facts and Dimensions.
- End users are querying large amounts of detailed data and query performance is a critical factor (Though this can be achieved to a certain extent using ROLAP storage mode in multidimensional models but since Tabular Models work in-memory, better speed and performance can be achieved using Tabular Models).
- If the data is huge and cannot fit into memory, consider using the DirectQuery Mode, which is equivalent to ROLAP storage mode in traditional multidimensional models. With DirectQuery mode, data is queried directly from underlying relational database every time it is accessed. However, DirectQuery mode has various limitations like underlying source can be either SQL Server or SQL Server PDW (Parallel Data Warehouse) etc. More Information on the limitations of DirectQuery mode.
PowerPivot: Use PowerPivot if any of the following, but not limited to, criteria are met:
- The size of data that you need to work with is less than 2GB. If you have started building a PowerPivot model and after a period of time, your data grows beyond 2GB then you can build a SSAS Tabular Model by importing PowerPivot into SSAS Tabular (Refer this MSDN KB Article for more information: http://msdn.microsoft.com/en-us/library/gg492155.aspx).
- You do not have SQL Server license but still want to be able to build tabular models with smaller datasets. In this scenario, you can go with PowerPivot for excel which is a free add-in for excel. However, if you want collaboration, like in SharePoint, then you need to have SharePoint license (with additional components like Excel Services).
Following are some of the Best Practices while working with Tabular Models.
Hide the Surrogate Key columns from client tools (Set the “Hide from Client Tools” in Tabular & PowerPivot models).
Hide columns which are part of user-defined hierarchies.
Do not bring the metadata columns like Creation date of the record, Modification date of the record etc. unless there is any very specific reporting requirement, which needs those columns.
Hide columns used in calculation of Measures like Reseller Sales Amount is derived from Sum of Sales Amount, and hence Sales Amount can be hidden from client tools.
While developing Tabular Model in SSAS, try to play with it in Excel at regular intervals. This will give a feel of how the solution, that is being built, will look like for the end users.
Mark the Date Dimension (Table) as Date Type. This will help in date related calculations like YTD, MTD etc.
Tips and Tricks
While working with Tabular Models in SQL Server Data Tools (SSDT), whenever any change is made in the model, it takes a while for the change to be applied to the workspace tabular database and the screen gets locked during this interval without allowing the user to perform any other action. This is somewhat a limitation and consumes more time in building the model. Though we cannot do anything much about this as this is by design, here are few tips and tricks to do things smartly and save time to a certain extent.
While hiding columns from the client tools, if you need to hide multiple columns from client tools, select multiple columns by holding “Ctrl” key and then hide them all in one go (This can be done in Diagram View).
While creating a hierarchy, select all the columns which need to be part of the hierarchy and then select “Create Hierarchy”. Later these columns can be re-arranged in the hierarchy if required.
Before adding calculated columns, set the “Calculation Options” to “Manual Calculation” (SQL Server Data Tools –> Model –> Calculation Options –> Manual Calculation). After the calculated columns are added, click on “Calculate Now” (SQL Server Data Tools –> Model –> Calculate Now).
This concludes the 3 part series on BISM. Hope this gives a fair idea about what are the different implementations of BISM and which one to choose based on various parameters.