Month: July 2014

Bill Fellows and I presented Somebody Got BIDS Helper in My Data Tools at Mile High Tech Con in Denver last weekend, and it reminded me how much I love BIDS Helper. I use it to develop all of my SSIS and SSAS projects, but I realized I haven’t blogged much about it. So here are my favorite BIDS Helper features for SSAS development. If you haven’t tried BIDS Helper yet, I highly recommend it. It’s free and there is great documentation for it, so it’s easy to get started.

Printer Friendly Dimension Usage for SSAS Multidimensional

Printer Friendly Dimension Usage provides you with a nice Bus Matrix for your documentation or design reviews. You can choose between a summarized view and a detailed view. When I have clients that I know will do a lot of ad hoc reporting in Excel using their cube, I like to create an Excel workbook with a data glossary, summary bus matrix, and a few pre-populated pivot tables that can be used to answer the most common questions. With BIDS Helper 1.6.5 or later, I no longer have to create my bus matrix by hand. When you click the Printer Friendly Dimension Usage button, it will ask if you want the detailed or summary version.

The report will open up in a new window, and you can choose to print it or export to Word, PDF, or Excel.

The Tri-State Perspectives and Deploy Perspectives go together nicely. Tri-State Perspectives put a red box around any dimension or measure group in which not all children are included in the perspective. This can be very helpful as you update and enhance your cube. New measures or dimension attributes are not added to perspectives by default when they are added to a cube, so this is a great way to double-check that you have added any new fields to the appropriate perspectives before you re-deploy.

If you need to make changes only to your perspectives (perhaps you realized you forgot to add a new field to a perspective), the Deploy Perspectives functionality allows you to deploy only the perspectives rather than the entire cube. This feature will delete any perspectives on the destination database that aren’t in the source in addition to deploying perspectives.

Tabular Actions Editor

Tabular Actions Editor fills a feature gap in the Tabular SSAS world. There is a nice UI for creating drillthrough actions for Multidimensional Analysis Services. By default, tabular models have drillthrough actions that simply contain the fields in the underlying fact table for that measure, which isn’t very useful or friendly to your users since it is mostly dimension keys and a few aggregatable values.

Without BIDS Helper, the only way to alter that behavior is the manually change the .bim file, which can completely corrupt your tabular model if you aren’t careful. The Tabular Actions Editor provides a nice UI to create actions in a manner similar to multidimensional SSAS development so you can create a helpful detail data set that is analytically relevant. With BIDS Helper you can also add actions other than drillthroughs, such as go to Url or go to report. With drillthrough actions, you choose:

action type

target type

target measure group

default setting

maximum number of rows

method of invocation

perspectives in which it should be available.

There are lots of good examples in the documentation. You will also notice a warning in the documentation:

Warning: While actions work in Tabular models, they are not officially supported by Microsoft. If you encounter a bug in how Tabular handles actions and open a support case, Microsoft may not provide support.

I have not run into a situation where this has been an issue, but I wanted to make sure I called this out so you knowingly accept the risks.

I love BIDS Helper

If you can’t tell, I’m a fan of BIDS Helper. I like it so much that I recruited a friend to help me create and deliver a presentation about it. If you are developing in SSAS or SSIS and you aren’t familiar with BIDS Helper, I encourage you to check it out. I believe is it one of the best free tools for MSBI developers available today.