Easier Data Marts with DreamFactory Data Mesh

Today’s IT teams are struggling to make sense of organizational data that has been compiled piecemeal and often stored within disparate storage solutions. Often this information needs to be aggregated and presented in a unified format, yet pulling data from multiple data sources and displaying it in a coherent way can be onerous and error-prone. The challenge is compounded when the data resides in different databases, and possibly within different clouds. To remedy this, companies often embark upon costly and time consuming data lake, data mart, and data warehouse projects. In many cases though, the IT team is simply looking for an effective solution to combine data within a single unified interface! In this tutorial I’ll introduce you to a powerful and very popular feature of the DreamFactory platform called Data Mesh. Using Data Mesh you can create virtual relationships between two databases much in the same way you can create foreign key relationships between two database tables. We’ll walk through an example in which a MySQL database running on Amazon RDS is meshed with an IBM DB2 database running on IBM Cloud, merging the data together so it can be retrieved via a single API endpoint.

Want to Watch a Video Instead?

Configuring the MySQL and IBM DB2 APIs

For the purposes of this tutorial I’ll assume you’re already familiar with DreamFactory fundamentals, including how to generate database-backed REST APIs. If not, I suggest taking a few minutes to watch our introductory video at https://academy.dreamfactory.com.

Configuring Data Mesh

After generating your APIs, enter DreamFactory’s Schema component and select the API and table that will serve as the relationship parent. In the following screenshot I’ve chosen the MySQL API and the employees table:
Schema
Once selected, you can scroll down to the table’s “Relationships” section. This section warrants a bit of explanation. When DreamFactory generates a database API, it analyzes all tables, stored procedures, views, table columns and datatypes, and foreign key relationships. This section contains a list of join aliases that you can use to easily join tables via the API:
Relationships
However you’re not limited to these aliases; by clicking the Add Virtual Relationship button you can create new relationships where they didn’t previously exist, including relationships between two databases. Click on the Add Virtual Relationship button and you’ll be presented with an interface for defining the relationship between two databases. See the following screenshot:
Defining the Datamesh
In this screenshot, I’ve defined the fields as follows:

* Always Fetch: This field enables the virtual relationship. You can also optionally enable the relationship on demand via the API.
  • Type: This field determines the relationship type. You can choose from Belongs To, Has One, Has Many, and Many to Many.

  • Reference Service: This field identifies the related service. It’s set to DB2 because the relationship pertains to the previously configured IBM DB2 API.

  • Reference Table: This field identifies the related table. Recall we selected the MySQL service’s employees table, so we’re going to relate the employees table to the DB2 service’s employee_status table.

  • Reference Field: This field identifies the foreign key field found in the related employee_status table.

After defining these fields, save the changes and you’re ready to begin using the new relationship!

Querying the Relationship

Now that the relationship has been defined, let’s execute a query and view the combined results. We’ll begin by showing what a query to the employees table looks like prior to configuring Data Mesh:
MySQL results
After querying Data Mesh, the results look like this:
MySQL and DB2 combined results

Conclusion

DreamFactory’s Data Mesh feature offers an incredibly straightforward, point-and-click solution for creating sophisticated and transparent unified queries. You’re certainly not limited to meshing two databases together; try meshing two, three, or more databases together and marvel over the time and aggravation savings!