Between sessions at the PASS Summit, I had the privilege of interviewing Alberto Ferrari and Marco Russo; SSAS Maestros and BI industry thought leaders. Alberto and Marco are revered as expert trainers and practitioners of SSAS and Power BI solutions. They are also well known for evangelizing best practices using the DAX calculation and query language. We spoke about some of the challenges and learnings they have gained from years of large-scale project implementations.
Both SSAS Tabular and Power BI Desktop share the same characteristic in that the semantic data model definition is described in a single file. Compared with older multidimensional model projects, the single file approach simplifies much of the development effort but it can be challenging to coordinate projects with multiple developers. They shared tool recommendations to assist with project integration, version management and development. Marco and Alberto recommend a number of community supported add-ins and other freely available software to enhance Visual Studio and Power BI Desktop.
I sincerely appreciate these two gentlemen taking the time to share their insights in this short interview, and their many contributions over the years that continue to improve Microsoft’s industry-leading Business Intelligence platform and the community that supports it. A comprehensive library of resources, training, tools and articles are available at: SQLBI.com.
During the interview we talked about several different tools that they adamantly endorse for any SSAS developer to have at their disposal. From my own experience, in rough order of priority, I recommend :
- BIDS Helper / BI Developer Extensions – This Visual Studio add-in is an important tool for anyone developing Business Intelligence solutions in any version of Visual Studio or SQL Server. It includes several essential features and capabilities for SSIS, SSRS and SSAS projects. It has been a community-developed and community-supported tool for many years and is considered by most experienced BI developers to be a core extension of the Visual Studio SSDT (formerly called “BIDS”) add-in.
Community developed tools like this help to spur future enhancements to the officially-supported capabilities in later Microsoft development tooling. I suggest that you carefully read the documentation for SSAS Tabular features in this tool because some of the options only apply to certain versions of SSAS. Some of the BIDS Helper features for SSAS 2012 and 2014 have since been added to the officially-supported designer for SSAS 2016 and 2017. BIDS Helper was initially maintained on the Microsoft CodePlex site (here: http://bidshelper.codeplex.com/) and has since been moved to GitHub here: https://bideveloperextensions.github.io/. It can now be installed in the Visual Studio Tools > Extensions and Updates menu by search for “BI Developer Extensions” in the Online section. - DAX Studio – This is a stand-alone application and the most comprehensive DAX, SSAS and Power BI calculation script and query editing and performance tool available. It is an open source project managed and maintained by a collaboration of several community MVPs and industry leaders (coordinators are Darren Gosbell & Marco Russo). The latest version and codebase have recently been moved from CodePlex to http://daxstudio.org.
- Tabular Editor – An open source, stand-alone SSAS Tabular model design tool that runs outside of Visual Studio. It is distributed through a GitHub repo at: https://tabulareditor.github.io. The source code is maintained at: https://github.com/otykier/TabularEditor. It is described as a lightweight editor for SSAS Tabular Models built in .NET/WinForms and utilizes the Analysis Management Objects (AMO) library.
- DAX Editor – an extension to SSDT that makes it easier to edit DAX measures in a Tabular project. It includes: DAX Syntax Highlighting, DAX Formatting and Textual representation of measures defined in a Tabular project
Extracting/importing measures from/to a Tabular model file (.BIM). This add-in is developed and maintained by SQLBI. It can be found in the Visual Studio > Tools > Extensions and Updates menu or downloaded from SQLBI at: https://www.sqlbi.com/tools/dax-editor. - BISM Normalizer – An add-in for Visual Studio/SSDT that can be installed from the Visual Studio Tools > Extensions and Updates menu by search for “BISM Normalizer”. This is an essential tool if you need to compare multiple SSAS Tabular projects and databases (including workspace databases) and then manage the steps to selectively merge additions and differences between objects. I have found this useful for integrating parallel development work on the same SSAS Tabular project but I will warn you that if used routinely to merge multiple changes to a single project, it can be a complex undertaking and not something I recommend on a daily basis.
BISM Normalizer is developed and maintained by Christian Wade, formerly an independent consultant and now a Senior Program Manager on the SSAS product team. Although Christian works for Microsoft, this is still considered a community-supported, third-party tool not officially supported by Microsoft. It is, however, the only tool designed specifically to perform schema comparisons and object merging in SSAS.
In our discussion, we also refer to these tools and resources:
- VertiPaq Analyzer – An older tool (actually an Excel Power Pivot workbook) created by Kasper de Jonge, Program Manager from the Power BI/SSAS product team, that imports SSAS DMV query results and reports object size, object data compression rates and optimization statistics. It has since been updated and enhanced. This is a convenient way to get information quickly about your SSAS or Power BI model. The same information can be obtained, with a little more effort, by running DMV queries in DAX Studio or SSMS. Links to the original and updated versions are available on the SQL BI site at: https://www.sqlbi.com/articles/data-model-size-with-vertipaq-analyzer/.
- DAX Formatter – An online tool for optimizing and reformatting DAX expressions, available from SQLBI, at: http://www.daxformatter.com/
- Tabular Modeling in Microsoft SQL Server Analysis Services – is a Microsoft Press book written by Alberto and Marco that applies mainly to SSAS Tabular 2016 and 2017 projects. This is an absolutely invaluable reference and tutorial on SSAS design best practices and design techniques. Previous books from our Italian friends include DAX Patterns and The Definitive Guide to DAX; all excellent learning resources that I have in my book library.
Shortly after this conversation, Marco and Alberto delivered two sessions at the PASS Summit conference about DAX best practices and performance optimizations. A little later, Marco sat with Guy In A Cube hosts Adam Saxton and Patrick Leblanc to chat about these sessions. You can find that interview here. I also had a chance to catch-up with Adam and Patrick in an earlier interview during the conference, which I will share in a later blog post.