Monkey Tools is an Excel add-in containing a set of tools to help you build better data models more quickly and using data modelling best practices. They also help you audit what has been done in your data model.

The philosophy of Monkey Tools is simple: Build better, faster. It is targeted at modelers and analysts who work primarily in Excel, but also push their models into Power BI, as we do here at Excelguru.

Our aim is to provide you with a good set of tools that help you, as a business intelligence author, to:

• Build models more rapidly
• Follow recommended practices
• Document your work
• Audit files that you receive

Monkey Tools Features

Monkey Tools adds a broad set of new features collected into two general collections: Monkeys and Sleuths. As you'll see below, the Monkeys make things happen, and the Sleuths figure out what has happened. You'll find the tools on a new Monkey Tools ribbon in Excel, and include:

• QueryMonkey (for inserting useful new queries into your data model)
• DestinationSleuth (to provide information on query load destinations)
• QuerySleuth (helping understand your actual queries)
• TimeSleuth (to benchmark query refresh times)
• PivotSleuth (helping you diagnose Pivot Table field issues)
• DAXSleuth (tools especially for working with DAX measures)
• ModelSleuth (reporting on the properties of your queries and data model)
• And more!

A high level view of each of these tools is detailed below.

DAXSleuth (Also works on Power BI models!)

We believe that measure dependencies are just as important as query dependencies, and this is the reason we built the DAXSleuth. This form:

• Displays a dependency/precedent tree view of your DAX measures
• Provides a full colour display of Implicit and Explicit measures (with or without children), as well as Calculated Columns
• Shows your DAX measures with colour highlighting and in an indented format
• Allows you to indent, un-indent, flatten, duplicate, and even update measures without leaving the DAXSleuth
• For Excel-based models, it also exposes all locations a DAX measure has been used (PivotTables, Pivot Charts, OLAP Formulae, and Named Ranges), and even allows you to select those objects right from the DAX Sleuth!

It sounds like a lot, and it truly is. Have a look at the Amount measure for the model below. Not only can you tell it has two precedent measures, but you can see the formula and know just how many times it has been used:

And don't forget that you can select those Excel objects and be taken right to them!

QueryMonkey

The Query Monkey gives you the ability to insert key queries like:

• The famous “fnGetParameter” query and table (from Chapter 24 of M is for Data Monkey)

• A “From Folder” setup that works with local and/or SharePoint hosted files

• Dynamic calendar tables based on your data (for custom calendars, it even provides the option to insert the "periodicity" columns for Rob Collie's GFITW DAX pattern!)

• Insert a Measures table, and more!

Heard enough? Download the free trial!

DestinationSleuth

If you've worked with Power Query in Excel, you'll know that you can load queries to an Excel table, to the Power Pivot data model, to both the data model and a table, or as "Connection Only". The challenge is that when you mouse over the preview pane, you're either told that the query loads to Connection Only, or as "x Rows Loaded". But it doesn't tell you WHERE they've been loaded. Well guess what? DestinationSleuth knows, and will even let you filter the list to show only queries that load to specific destinations!

Not only that, but DestinationSleuth will even let you change multiple load destinations at once, unlike the default Excel user interface for Power Query.

QuerySleuth (Also works on Power BI models!)

The QuerySleuth is a must have for every Power Query user. This form is packed with useful information and features such as:

• A dependency/precedent tree view layout
• Full colour display based on load destination
• Colourful and INDENTED M code
• The ability to modify the M code and write it back to the editor WITHOUT LOCKING YOUR EXCEL User Interface!
• A variety of configurable options so that you can display the code the way you want to see it

Developed based on a real business need, we believe this form will add magic to your ability to trace query dependencies and precedents. Have a look at the form in action:

As you can see, it provides a TON of useful information. It's also worth noting that everything except for the ability to do query writeback works on Power BI models as well!

TimeSleuth

Have you ever hit the refresh button in Excel and then waited... and waited... and waited for your Power Queries to refresh? Want to know which query is causing the bottleneck? Then maybe you should check out the TimeSleuth. Not only will it time individual queries, it will even let you perform multiple runs, optionally with privacy enabled/disabled, in order to compare performance results.

If you've ever asked yourself the question of which query version will refresh faster, or timed queries manually with a stopwatch, you'll love this tool.

Of course, there's still more, but if you want to go straight to the free trial, click here!

PivotSleuth

Have you ever seen that irritating “Relationships May Be Needed” error when building a Power Pivot-based PivotTable, and wondered why? PivotSleuth's job is to help you with that. And not only will the PivotSleuth identify problems that currently exist, but it will also provide you advice on things that could lead you to error states in the future!

• See the real, fully qualified names of the fields used in your PivotTables
• Highlight potential or current issues in PivotTable configurations
• Debug cross filtering issues, “Relationships May Be Needed” errors, and errors where grand totals are returned for all rows on the PivotTable

Here's a sample of a PivotTable showing that frustrating error message:

And you can see that not only is the problematic field shown in red, but two of the other fields have issues that could lead to problems in the long term!

ModelSleuth (Also works on Power BI models!)

Have you ever had to provide documentation for your model? Or picked up a model from someone else and had to review it? The ModelSleuth provides reports and utilities such as:

• A full model summary report showing key statistics about your tables, relationships, columns, measures, and queries. (Trial and Free licenses are limited to every other record in this report.)

• A model memory usage report, complete with how much memory is recoverable (for Excel-based data models)

• An unused columns report (for Excel-based data models)
• A DMV Explorer (for those who want to build their own reports.)
• And more!

Are There Plans for Updates?

Yes, we have big plans for many more features!

Our intended model is to deliver features (and bug fixes) as we develop them. That means that there could be periods with no updates as we work on something big, or periods with multiple updates delivered in a single week. We know that some people love frequent updates and some people don’t, so we give you complete control over how often you receive them:

See it in Action!

Check out this Monkey Tools demo Ken did for our Vancouver Power BI & Modern Excel User Group, and see for yourself how it can help with building better data models, faster. (The Monkey Tools part starts at about 18:30.)

Software Specifications

Monkey Tools is an Excel-based add-in that works in Excel 2016 and higher. It can read from Power Query and Power Pivot models in:

• The active workbook
• Any other open workbook
• Monkey Tools backup files
• Power BI Desktop files

Still want to read more?

Check out the Monkey Tools Installation & Feature Guide for the complete list of features, as well as how to use them.

Ready to try or buy?

We have a FREE 2-week trial version and subscription options starting as low as $7.99USD/month! Click here check out the various options.

Feature Summary

Check out which features are available with the different versions of Monkey Tools:

I'm ready! How do I get my copy of Monkey Tools?

Free Trial Offer

Still not sure if Monkey Tools is right for you? Our FREE 2-week trial version allows you to try it before you buy it!

Subscription Options

Ready to skip the trial and get the full Pro version right away? Choose the subscription option that works best for you: