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:
- 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
...
