Top FREE tools for SSAS 2012 Developers

Over the years, I’ve come across a number of tools aimed at helping SSAS developers work more efficiently.  In this post, I’ll share with you the ones I’ve found the most useful and give you a brief explanation of why.

Note: Most of these tools were released prior to Analysis Services 2012, but fortunately, aside from the major addition of the new Tabular model, not much else changed in the product…so these tools still work!

For a nice list of the “minor” changes in SSAS 2012, see this blog post by Chris Webb (b | t)

 

BIDS Helper

This incredible tool comes as a Visual Studio add-in (free via codeplex) with a ton of cool features targeting all areas of the Microsoft BI stack – not just SSAS.  And despite its name referencing the pre-2012 development environment (BIDS – Business Intelligence Development Studio), it will work just fine with the 2012 development environment (SSDT – SQL Server Data Tools).

Instead of rattling off the entire list of features, I’ll just name a few that I’ve found to be very helpful for SSAS development projects.

1. Deploy MDX Script: making changes to the MDX Script is more common than you think.  Whether you’re adding a new calculated measure or performance-tweaking an existing calculated measure, this feature allows you to deploy MDX Script changes without impacting other objects (dimensions, partitions, etc) that might require processing.
2. Dimension Health Check: configuring attribute relationships can be complicated and the UI in BIDS/SSDT isn’t always intuitive (wait, do I drag this attribute on top of that attribute, or is it the other way around…grrrr!).  This feature will help you workout any kinks in the attribute relationships you’ve defined to make sure they jive with the underlying data.
3. Many-2-Many Matrix Compression: if you’re not already familiar with the concept of Many-2-Many Matrix Compression read this.  If you are (rock on!) …then you’re probably also aware that it doesn’t always make sense to implement.  This BIDS Helper feature will help you make that decision more easily by querying the tables involved and determining the level of compression that could potentially be achieved.
4. Similar Aggregations: aggregations are at the heart of cube performance.  But like many things in life, aggregations are subject to the law of diminishing returns.  Once that threshold is crossed, gains in query performance pale in comparison to the cost of processing.  This feature will help you identify similar aggregations which make great candidates when looking for ways to reduce the number of aggregations to decrease processing time.

In addition, there are also a handful of features, such as:

• Dimension Optimization Report
• Non-Default Properties Report
• Printer Friendly Aggregations
• Printer Friendly Dimension Usage
• Roles Report
• Visualize Attribute Lattice

…that will generate information needed to properly document the cube.  If you’re doing any BI development in BIDS/SSDT, this one is a must-have.

 

OLAP Pivot Table Extensions

OLAP Pivot Table Extensions (free via codeplex) has been around for a few years now, but it wasn’t until a recent engagement that I started to grasp the true value of this free Excel add-in.  The standout feature, from a SSAS developer perspective, is that it allows users to capture the MDX generated by Excel via pivot tables.

Below is a screenshot of what that looks like:

click to zoom

Imagine a client site where there’s a single cube, a handful of power-users, and you, in the in the middle, to troubleshoot query performance issues.  That’s the exact scenario I was in and this tool saved my ass.

When we (the power-users and myself) first started working together they would send emails with a workbook attached and a generic message like “the pivot table in this workbook is slow to refresh“.  This inevitably led to more emails (possibly a phone call and a screenshare session) to confirm the exact steps to reproduce the issue (which attributes were on rows and columns, any filters, etc) … a huge waste of time.  Clearly this wasn’t going to work long-term.

The solution we came up with was to install OLAP Pivot Table Extensions on the power-users’ workstations and showing them how to capture the MDX being generated by the pivot table.  This dramatically streamlined our triage sessions.  As an added bonus, the power-users started to read-up on and leverage a few of the other features available in OLAP Pivot Table Extensions such as:

• Private Calculated Members
• Calculations Library
• Changing PivotTable Defaults
• Searching
• Show Property as Caption
• Clear Pivot Table Cache

For other client tools in the MS BI stack (ex. PerformancePoint) you’re still stuck (as far as I’m aware) with good ole’ SQL Profiler for capturing MDX queries generated via user interaction.  And you’re still going to need other tools, like MDX Studio (next on the list), for drilling in on the execution of the MDX queries once they are captured.

 

MDX Studio

This handy utility was originally developed by Mosha Pasumansky (one of the founding the fathers of MDX) and is my go-to for MDX query analysis.

Note: Unfortunately, now that Mosha has moved on from SSAS (and Microsoft), it appears development has stopped on this app.  However, Marco Russo (b | t) and Alberto Ferrari (b | t) were gracious enough to give this tool a home on their site (free download) and I’m happy to report that the SSAS 2008 version works with SSAS 2012.

To give you an idea of the primary use case, let’s continue on from the scenario above where our power-users have captured an MDX query generated by a pivot table in an excel workbook they are working on.  The next step in the troubleshooting processes is to run the query in MDX Studio and capture the details of how it’s being processed.

Below is a screenshot where I’ve plugged in a MDX query (captured via OLAP Pivot Table Extension) and executed it…

click to zoom

On the left side of the screen, there’s a Perfmon window displaying details that indicate how the query was processed by the SSAS instance:

click to zoom

This information can help you understand things like the duration, number of cells calculated, cache utilization, and whether the query is executing in block-computation mode or cell-by-cell.  If the original query is executing in cell-by-cell, perhaps there’s a way to rewrite it in a way that enables block-computation.  Another common task is to perform cold-cache vs warm-cache comparisons.

Yes, all of this information and these types of tasks can be carried out via Management Studio + SQL Profiler, but when you have a stack of queries that you’re tuning, this can greatly increase your efficiency.

AS Performance Workbench

Most SSAS developers are probably familiar with performance testing individual SSAS queries…especially ones generated by widely-used SSRS reports.  And while this tool can help with that, you’re probably better off sticking with MDX Studio. On the other hand, if you’re interested in evaluating your cube’s performance under heavy concurrent load (and you should be!) then this is the tool for you.

AS Performance Workbench, created by Rob Kerr (b | t) and made available for free via codeplex, is an outstanding tool that allows you to simulate a load of 10’s to 1,000’s of queries across a configurable number of “concurrent users”.  During the test, relevant PerfMon counters are displayed:

click to zoom

Note: even though the Product Version dropdown only lists AS 2005 and 2008 as options, it will work with AS 2012 multidimensional and tabular!  

And there’s even a feature to automatically export the results to an HTML report for easy documentation of each load testing session:

click to zoom

Rob’s also provided a short concise demo of the tool on youtube showing how easy it is to use.

BISM Normalizer

BISM Normalizer is a free Visual Studio extension, developed by Christian Wade (b | t), and targeted at SSAS developers working with Tabular models.  More specifically, it allows the comparison between multiple tabular models as well as the controlled merging of objects from one model to another.  And while I haven’t used this tool (yet) in a real-world scenario, I find its value proposition extremely appealing.

A tool like this greatly facilitates the organic growth from self-service PowerPivot models into enterprise Tabular models…as Chris Webb alludes to in this post.

Furthermore, if you buy into the technical architecture framework where chunks of the data warehouse are spun off into individual tabular models (instead of a single massive tabular model on top of the DW), this tool will be indispensable for managing the various models and ensuring standardization across any overlapping bits.

 

WinDiff

This is a very basic tool for comparing the differences between two text files.  As a SSAS developer (with a touch of OCD) I like to use this utility to compare the SSAS instance configuration file (msmdsrv.ini) with a copy containing all default settings so that I know which (if any) settings have been changed on the SSAS instance to which I’m deploying.

Note: I use WinDiff, but you can use any tool with this functionality – there are many flavors: CompareIt, WinMerge, etc

 

DAX Editor for SQL Server

This is a free add-in for Visual Studio that claims to provide a number of helpful DAX-related features for SSAS developers working on Tabular models such as IntelliSense, auto-complete, etc.  This is one tool I was really looking forward to using, but it has been nothing but trouble for me 😦

So for now, I’ll continue using Notepad++ (with the Colin-Banfield-customization) for scripting DAX calculations and expressions.

 

Cliff Notes

As you can see, there are quite a few tools to choose for the various tasks faced by SSAS developers.  Here’s a quick summary for those with short attention spans 🙂

• BIDS Helper – development, documentation (codeplex)
• OLAP Pivot Table Extensions – performance troubleshooting, excel power-user features (codeplex)
• MDX Studio – performance troubleshooting (sqlbi.com)
• AS Performance Workbench – load testing with configurable concurrency (codeplex)
• BISM Normalizer – comparing and controlled merging of Tabular models (visualstudio gallery)
• WinDiff – SSAS instance configuration file (msmdsrv.ini) comparisons (google)
• DAX Editor For SQL Server Notepad++ (with the Colin-Banfield-customization) – DAX editor

If there’s any that I’ve missed, please let me know.