by • June 1, 2023
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.
Data Mesh, introduced by Zhamak Dehghani, is a decentralized approach to data architecture that advocates for a self-serve, domain-oriented model. It aims to address the challenges associated with centralized data platforms and traditional monolithic data architectures. By distributing data ownership and management to individual domain teams, Data Mesh embraces the principles of autonomy, alignment, and product thinking.
Data Mesh offers improved data quality and reliability by empowering domain teams to take ownership of their data products. This decentralized approach enhances accountability and reduces the burden on centralized data teams.
Data Mesh can promote enhanced data democratization, enabling domain teams to access and analyze data independently. This empowers teams to make faster and more informed decisions, fostering a data-driven culture across the organization. Data Mesh architecture can also ensure scalability and flexibility, accommodating the diverse needs of different domains.
Data Mesh also reduces bottlenecks and dependencies by distributing responsibilities and fostering autonomy among domain teams. This improves agility and minimizes reliance on a single point of failure, leading to more efficient data processes.
Data marts are subsets of data warehouses that are designed to serve the analytical needs of specific departments, teams, or business functions within an organization. They are tailored to provide relevant and easily accessible data for decision-making and analysis within a specific domain.
The primary purpose of data marts is to provide optimized and streamlined access to data for specific user groups. Some key benefits of data marts include:
A video-based version of this tutorial is available at https://academy.dreamfactory.com. Or just click on the video below!
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 you’d like to follow along in your own environment, you’ll need to configure two database APIs within your DreamFactory instance.
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:
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:
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:
Add Virtual Relationship
In this screenshot, I’ve defined the fields as follows:
Many to Many
After defining these fields, save the changes and you’re ready to begin using the new 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:
After querying Data Mesh, the results look like this:
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!
From Data Lake to Data Mesh: How Data Mesh Benefits Businesses
Jason is the author of almost a dozen books on web development, including most recently Easy Laravel 5, and Beginning PHP and MySQL, 4th Edition. He’s the co-founder of the CodeMash Conference, one of the largest software conferences in the Midwestern United States.
Jason serves as a technical advisor to the boards of several technology startups. His free time is spent playing with his kids and reading.
Join the DreamFactory newsletter list.