Monkey Tools

Connection Monkey

Article Overview

The Connection Monkey is your one stop shop in order to connect to the various Excel workbooks or Power BI models that Monkey Tools can analyze.

While many of Monkey Tools forms will automatically connect to the active workbook by default when opened, there are times you may wish to connect Monkey Tools to a different (open) workbook, Power BI file or Monkey Tools backup.  Or you may want to force Monkey Tools to do perform a full refresh of its model analysis if you have been making changes.

Each of these items can be accomplished via this menu. Upon selecting the appropriate item, Monkey Tools will:

  • Prompt you for a file (if you chose Power BI or Monkey Tools Backup)
  • (re) Analyze the file for you
The Connection Monkey menu

The Connection Monkeys

Excel Files

There are two different options for connecting to Excel files:

This feature allows you to connect (or refresh the connection to) the Power Query and Power Pivot model in the current workbook. While most forms will connect to the active workbook by default upon initial launch, this feature allows you to switch back to the active workbook if you have chosen a specific model to connect to via the other options on this menu.

This menu will allow you to connect Monkey Tools to any other workbook that is currently open in Excel. The benefit of this feature is that you can have one workbook active but can analyze a different workbook if needed for any reason

Power BI Desktop Files

When launching this option, you will be asked to browse to locate the .pbix file you wish to open. Once you have selected to it, Monkey Tools will launch Power BI Desktop, and then perform its analysis of the .pbix file.

Please note:

  1. Monkey Tools requires all Power BI files to be closed before launching. (Yes, we could do better here, but that is how the feature currently works.)
  2. Monkey Tools needs to wait for the Power BI file to launch before it can connect to the model inside the file. Naturally, this can take some time.
  3. Unfortunately, unless your Power BI launches on a different monitor, it will launch in front of the Excel window, obscuring the message that pops up once Excel has received the information it needs (Shown at right.) Our recommendation is to let Power BI launch until it opens the report canvas. At that point – even if the visuals have not all loaded – switch back to Excel where you should see the confirmation message that your data is loaded an ready for use.
Confirmation that the "Data model has been loaded"

It is also worth knowing that not all features of Monkey Tools currently work on Power BI files.  As a general rule, you’ll find that our Sleuths will work on Power BI models, but our Monkeys will not.  Check the features page in our Knowledge Base for your specific Sleuth or Monkey of interest for confirmation.

Errors Connecting to Power BI Models

When Power BI starts, it creates a folder on your computer and writes a bunch of files to spin up an Analysis Services Workspace (server).  Within that folder is a file that contains the port number that we need in order to connect to the file.  To complicate things, the version of Power BI you use puts the folder in one of 3 different places:

  • Power BI Desktop (Direct Download):
    • C:\Users\<username>\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces
  • Power BI Report Server (Direct Download):
    • C:\Users\<username>\AppData\Local\Microsoft\Power BI Desktop SSRS\AnalysisServicesWorkspaces
  • Microsoft Power BI Desktop (Store Version)
    • C:\Users\<username>\Microsoft\Power BI Desktop Store App\AnalysisServicesWorkspaces

When Monkey Tools attempts to connect to a Power BI file, the first thing we do is check to see which Analysis Services folders you have on your machine.  If we can’t find any one of these folders, our monkey will throw the following error message:

Sorry, an error was encountered while connecting to the Analysis Services Path.
Message stating that an error was encountered while connecting to the Analysis Services Path.

The most likely cause of this error is that your Power BI Analysis Services folder is rendering under a different username than what our Monkeys see.  (To check, you can run a full error log while trying to connect to the Power BI file, then read the log to see which folders we are attempting to connect to.)  Should this be the case, you are able to add your custom path via the following steps:

  • Press Windows key + R to launch the Run dialog
  • Type regedit and press Enter
  • Navigate into the following registry folder: HKEY_CURRENT_USER\Software\VB and VBA Program Settings\XLG Data Monkey Tools\SysInfo
  • Create a new String value called ASWorkspacePath
  • Modify the value and enter the path to your AnalysisServicesWorkspace folder (without quotes or trailing slashes)
  • Restart Excel

Monkey Tools Backup Files

Monkey Tools contains an Monkey who will Export Model Components into a Monkey Tools Backup file. The Monkey Tools Backup connector will allow you to import those exported model components so that you can run an analysis on the backup file. 

Reload Current Model

If you ever find yourself needing to reconnect to your active model to refresh the data, this is the feature that will allow you to do so. (Its primary focus is to re-load a Power BI file should you make changes to it.)

Share:

Facebook
Twitter
LinkedIn

Related Posts

Global Options controlled by the Options Monkey include legacy PivotTable buttons, show the user interface in Free mode, Disable Enhanced Query Metadata Analysis and Error Logging

Options Monkey

Article Overview What is an application with Options? Monkey Tools is no difference, and we have a specific Options Monkey to help you control them

Export Model Data

Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time, we did feature this

Export Model Components

Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time – until we get

Import Monkey

Coming Soon! Sorry, but this article isn’t quite ready yet. We promise that we’re working on it! In the mean time – while you wait

DAX Table Monkey

Article Overview While PivotTables and PivotCharts are a useful and valid way to summarize your data, sometimes you want a good old table. While it

Copyright Excelguru Consulting Inc.