SSAS – Choosing between Analysis Services Multidimensional and Tabular Models – Part 3

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.

Considerations

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.

Recommendations

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).

Best Practices

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.

Different Ways to Add Custom Colors to SSRS Reports

SQL Server Reporting Services (SSRS) has evolved as a strong reporting tool over last few years. One of the common needs in SSRS or any reporting tool for that matter is the ability to apply different colors of our choice to match the Theme/Color Combination/Corporate Branding. SSRS offers 12 different in-built color palettes (at the time of writing this article) for charts which is pretty large number. However, it does not always meet the needs or suite the Theme. To address this need, we need to be able to apply custom colors, basically any color represented by any valid HTML Color Code, to charts. There are various approaches to apply custom colors to charts in SSRS and we will see those approaches in this article with Pros and Cons of each of the approaches. Follow this link to read full article: Different ways to create Custom Colors for Charts in SQL Server Reporting Services.

Related articles

• Handling Large Number of Categories in a SSRS Pie Chart (dattatreysindol.com)
• SQL Server 2012 – Power View (Project “Crescent”) – A New Self Service BI Tool – Learning Resources (dattatreysindol.com)

SQL Server – Implementing a DO-WHILE Loop in Transact-SQL

Last day there was a T-SQL Session going on with a bunch of folks attending the session and most of them were familiar with T-SQL. During the session, the topic of Transact-SQL WHILE Loop Construct came up and we started discussing about it. Suddenly I asked everyone, How to Implement DO-WHILE Loop in T-SQL. To my surprise, many folks were not able to answer this question. So just thought that this can be good learning for folks who are not familiar with the implementation of DO-WHILE in T-SQL and also this tip is worth adding to the T-SQL Interview Questions and Answers List.

A DO-WHILE Loop is a Looping Construct, similar to WHILE Loop. The difference between WHILE Loop and DO-WHILE Loop is that, statements inside a WHILE Loop are executed only if the condition specified in the WHILE Loop is satisfied, whereas the statements inside a DO-WHILE Loop are executed at least once irrespective of whether the condition specified in the DO-WHILE Loop is satisfied or not.

As most of you, who are reading this article might already be aware and those of you who are new to this, there is no DO-WHILE Looping Construct available in T-SQL Language. However, we can achieve the DO-WHILE Looping functionality in T-SQL by combining a couple of T-SQL Control Flow Clauses/Constructs/Statements.

Let us say that you want to achieve a DO-WHILE Loop functionality and the statements inside the loop should execute specified number of times based on the condition but should execute at least once. Below is an example of the same.

DECLARE @LoopCounter TINYINT = 1
DECLARE @LoopMaxCount TINYINT

SET @LoopMaxCount = 0    /* Intentionally set to zero */

WHILE (1 = 1)    /* Give an expression, outcome of which is always true */
BEGIN
    PRINT 'Loop Counter Value: ' + CAST(@LoopCounter AS VARCHAR(3))
    /*
    Your list of statements to be executed here.
    */

    SET @LoopCounter = @LoopCounter + 1 ;

    IF (@LoopCounter > @LoopMaxCount)
        BREAK ;

END
GO

Following screenshot shows the output of the above query.

As you can see from the above output, even though the condition specified in the IF Statement at the end of WHILE Loop is false during the first time check itself, still the statements inside WHILE Loop Executed Once.

We were able to achieve the DO-WHILE Loop functionality using just the WHILE Loop, IF Statement, and the BREAK Statement.

Have you ever come across a need to implement DO-WHILE Loop in T-SQL? If yes, how did you implement it? Do let me know by leaving a comment below.