Blog Archives

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.

SSRS – SQL Server Reporting Services Report Manager Tips and Tricks

In the last post, we saw SQL Server Reporting Services Tips and Tricks to Improve the End-User Experience. In this post, we will see few tips and tricks on SSRS Report Manager.

Report Manager is a pretty decent interface and offers the necessary functionality for managing reports. In this post, let’s take a look at few very less known uses/features of SSRS Report Manager. In this post, we will take a look at the following tips and tricks:

  • Customizing Report Manager Header/Title
  • Uploading Custom File Types to Report Manager
  • Enable My Reports Feature

To continue reading, catch the full article here: SQL Server Reporting Services Report Manager Tips and Tricks.

Related Articles:

BI Basics – Business Intelligence and Cloud Computing

The term “Cloud Computing” has been “Buzz Word” for quite some time and it is continuing to gain popularity for the benefits it offers.

In this post we will define what is Cloud Computing, Understand different Cloud Computing Models, and what is the connection between Clouding Computing and Business Intelligence.

What is Cloud Computing?

Wikipedia defines it as “Cloud computing is the use of computing resources (hardware and software) that are delivered as a service over a network (typically the Internet)

In simple terms, “Cloud Computing is the delivery/offering of a part or all of a set of Hardware, Memory (RAM), Storage, Network, Software, and Applications etc. for Use as a Service On-Demand over Internet”.

Cloud Computing Models

Cloud Computing is delivered in 3 different models as:

  • Software as a Service (SaaS)
  • Platform as a Service (PaaS)
  • Infrastructure as a Service (IaaS)

Cloud Computing Models

In the above representation, the components mentioned in each of the circles represent the components managed by the vendor as part of each of the cloud computing models. As we move from the inner most circle to the outer most circle:

  • The amount of things we manage reduces
  • The amount of things managed by vendor increases
  • The amount of control we have over the system decreases
  • The amount of technical expertise required to manage the system reduces

Cloud computing offers various advantages. Following are some of the major advantages:

  • Makes it very convenient to provision hardware, software, and infrastructure in a short time as the Procurement, Maintenance, and Management of infrastructure is taken care by vendor.
  • Offers sufficient flexibility/room for changes in the system during the implementation phase as provisioning is a quick and simple process.
  • Ability to scale up/down rapidly on-demand. It’s highly elastic!

Why Cloud Computing is Suitable for BI?

Many BI Projects fail due to various reasons including Lack of Vision, Overly Complex Design, Inappropriate Choice of Tools/Technologies, Poorly Defined Metrics/KPIs, Inadequate Business User/Decision Maker Involvement, Lack of Room/Flexibility for change during implementation phase, Insufficient Funding/Budget Allocation, Increased Time to Market, and many others.

Of these reasons, Lack of Flexibility for change, Insufficient Funding/Budget Allocation, and Time to Market are few major reasons for failure. Organizations often fail to realize the importance of BI System and hence allocate inadequate funds. Procuring Hardware, Software, & Setting up Infrastructure takes up a lot of time due to processes (approvals etc.) involved at the organization level, thereby increasing the Time to Market, and often does not offer enough flexibility for change, and hence lead to Project failure.

There is a need for a system/offering which allows sufficient flexibility during the phase of implementation of a BI Project, allow sufficient agility, affordable hardware/software, and faster procurement of Hardware/Software and Setting up of Infrastructure. These features are offered by Cloud Computing offerings like Windows Azure. In fact, those are some of the attractive features offered by Cloud Offering, making Cloud most appropriate for hosting BI Applications in various scenarios.

Related Articles: