I am always searching for tools to assist in helping me work more efficiently. I have built many myself, however why build if you can buy (cheaply) and why buy if you can find something free. Additionally many times I will find some code to use as a base for something I need or as a learning tool to code something I may be less familiar with. As such I have come across a number of tools aimed at helping SSAS developers work more efficiently. In this post, I’ll share with you some of the tools that I have found most useful.
Note: Many of these tools were released prior to Analysis Services 2012, but work without issue as aside from the major addition of the new Tabular model, not much else changed in the product! Some even have releases for SQL 2014.
This is an incredible tool with amazing features and comes as a Visual Studio add-in (free via codeplex) that has features supporting SSAS, SSIS and SSRS. It works fine with the 2012 development environment (SSDT – SQL Server Data Tools) and there is a new release for SQL 2014.
Here are but a few of the BIDS Helper’s amazing features:
- 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.
- 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.
- 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.
- 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.
some others of interest include:
- Dimension Optimization Report
- Non-Default Properties Report
- Printer Friendly Aggregations
- Printer Friendly Dimension Usage
- Roles Report
- Visualize Attribute Lattice
Another great tool is the 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.
click to zoom
One of the main benefits of this tool is its ability to capture the MDX generated by Excel. This can be installed on the developers and power users workstations. Once installed it is a simple matter to capture the MDX being generated by the pivot table. This dramatically streamlines the amount of time triaging performance issues. Some additional features are:
- Private Calculated Members
- Calculations Library
- Changing PivotTable Defaults
- Show Property as Caption
- Clear Pivot Table Cache
Next UP MDX Studio developed by Mosha Pasumansky
This handy utility was originally developed by Mosha Pasumansky. Unfortunately Mosha has moved on to other things and development has stopped. However the tool is still very much viable and remains my go-to for MDX query analysis.
Below is a screenshot where I’ve plugged in a MDX query (captured via OLAP Pivot Table Extension) and executed it…
click to zoom
Here is a very cool feature, as you can see 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 is pivotal in understanding 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.
You can get this information from Management Studio + SQL Profiler, but this tool 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
For the visual learners out there Rob Kerr has provided a short concise demo of the tool on youtube.
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.
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: WinDiff is great but you can use any tool with this functionality – 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 tool has not quite lived up to expectations and has proven to be a little problematic, so as an alternative there is always Notepad++ (with the Colin-Banfield-customization) for scripting DAX calculations and expressions.
SSAS developers. Here’s a quick summary
- 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 ServerNotepad++ (with the Colin-Banfield-customization) – DAX editor