Tag: mysql

Fixing “Value ‘preferred’ is not of the correct type” Error in Power BI and MySQL

Luke Johnson

When using MySQL with Power BI Desktop, you may come across this error:

Value 'preferred' is not of the correct type

 

It seems that this is caused by an incompatibility between Power BI Desktop and a later MySQL Connector Net version. First, check your version:

If you are running the MySQL Connector Net 8.0.11, you may experience the error described above. To resolve the issue, install the older version:

  1. Close Power BI Desktop
  2. Uninstall MySQL Connector Net 8.0.11
  3. Install MySQL Connector Net 6.10.7 (from here)
  4. Open Power BI Desktop
  5. The error “Value ‘preferred’ is not of the correct type” should be resolved, and you can use MySQL with Power BI again

How to Migrate an Existing On-Premises Data Gateway for Power BI

Luke Johnson

A few years back, everyone was excited about the 3D and visualisation aspect of BIM. It seems that more and more, people are getting excited about BIM Data. Many companies are building and adapting business intelligence solutions that somehow connect to or wrap-around the BIM world. One of the most popular tools at the moment is Power BI. It is extremely flexible and easy to set up and use for data analytics.

When you connect the Power BI web service to your own database, you also have to provide some mechanism for Power BI to periodically update the base dataset. The common answer to this question is the “On-premises data gateway”.

When you install the gateway on your own hardware, there are two types of gateway to choose from:

On-prem-data-gateway-install-powerbi

If you are already running a data gateway for Power BI, what happens if you want to transition the ‘gateway’ role to a new machine? Well, you need one key piece of information: the Recovery Key from the gateway when you set it up originally. If you do have this, you can simply ‘take over’ the role from an old machine and apply it to a new machine:

  1. Download the PowerBIGatewayInstaller.exe gateway installer from this page
  2. Choose your gateway type
  3. Choose to “Migrate, restore or takeover an existing gateway”

  4. Enter the information, including the recovery key

  5. Done

If you don’t have a recovery key, you will have to:

  1. Install a completely new gateway:

  2. Switch the gateway used on the web app side (Scheduled Refresh page). You will probably have to do this for every dataset.

Feel free to reply with any of your cool BIM data workflows 🙂

The Most Important Tables When Rebuilding WordPress Database after a Recent Restore

Luke Johnson

If your domain host happens to crash and they go ahead and restore MySQL data that is a few days old, which database tables are likely to be most important to incrementally rebuilding your WordPress data?

Here’s what I discovered recently when I had to do that – these are the key tables for posts, media, comments, categories and tags:

wpu8_commentmeta.sql
wpu8_comments.sql
wpu8_postmeta.sql
wpu8_posts.sql
wpu8_terms.sql
wpu8_term_relationships.sql
wpu8_term_taxonomy

I found it quite easy to use the ARI Adminer plugin from WordPress for this. You could also use phpmyadmin or mySQL Workbench or similar:

Note to self: Remember to keep regular backups of your entire database (export via phpmyadmin or similar) and your content (FTP the /public_html/wp-content/uploads/ folder, or ZIP it via cPanel and then download)

Escape Characters For Revit and External Database Connections

Luke Johnson

If you try to build a connector to get some Revit data to a database like MySQL, you may hit an issue where some strings that are allowed in Revit will do funny things to your SQL instructions, like this:

What to do? We need to ‘escape’ these characters during our data export or INSERT to create something that the database engine can consume. There are some exotic ways to get around this (like encode your text in base-64), but in my case I just wanted to replace the problem character with the escaped version. Ideally, we would use characters that we know will never be found in Revit Type or Family Names.

Revit does not allow these characters in most type and family names:

Looks like a backslash could work, as this is an escape character in MySQL, and it is not allowed by Revit as above.

You can do this a lot of ways in various coding languages, but an easy way is with a String Replace in Dynamo, like:

This workaround doesn’t handle every situation – like if you are pulling parameters out of Revit that contain all kind of characters, you may have to look at a more reliable text cleanup or encoding solution. But this helps with the Type and Family names at least 🙂

Revit gaining popularity as FM tool?

Luke Johnson

FM, or Facility Management, is sometimes thought of as the sixth dimension of BIM, or 6D BIM.

Mario Guttman, creator of Whitefeet tools, reposted an interesting video / case study by David Mettler showing a potential workflow from Revit into an FM scenario using MySQL:


The process:  “I exported the Revit tables out to Access using the DBlink. From Access I had to setup a ODBC export using SSH Tunneling to get the remote host to show up as a  localhost. It took a lot of time to figure out the connection issues get the data across to MySQL. Once I got the bugs work out on the ODBC connection it’s a piece of cake post the information into MySQL. I used phprunner with the SSH Tunnel to create the pages.”
via
Revit FM Tool | WhiteFeetTools

Slingshot for Revit – connecting Revit to relational databases and Rhino

Luke Johnson

Nathan Miller has been working on a number of cool things for some years now.  In one recent post about Slingshot for Revit, he states “the tool will provide different means of connecting Revit (and Vasari) to relational database management systems, including MySQL. More importantly, I am interested in establishing common database schema that will allow for a more fluid workflow between Revit and external design tools like Rhino and Grasshopper. This includes sharing parameters and geometry.“

He has been “prototyping some functionality via the RevitPythonShell plug-in in Vasari. Python is giving me a more fluid workflow than the usual Revit development process. Eventually, the tools will become true add-ins…”

via
The Proving Ground by Nathan Miller: Slingshot!… for Revit?

Image from The Proving Ground – “A point cloud in Vasari being created using a MySQL database…”