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.

What is Data Mesh?

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.

Key Principles of Data Mesh

Domain-Oriented Ownership: In a Data Mesh, data is treated as a product, and each domain team becomes responsible for their own data products. This approach promotes accountability, as the teams have a better understanding of the data’s context, quality, and usage within their domain.
Self-Serve Data Infrastructure: Data Mesh empowers domain teams by providing them with self-serve data infrastructure. This infrastructure includes data pipelines, storage, processing, and governance tools that enable teams to manage and deliver their data products independently.
Federated Computational Governance: Unlike a centralized data team, Data Mesh embraces the concept of federated computational governance. This means that governance responsibilities are distributed among domain teams, who understand the specific context and requirements of their data. It fosters a culture of collective ownership and accountability.
Product Thinking and Data Products: Data Mesh encourages teams to think of data as a product and treat it accordingly. This involves defining clear data product boundaries, establishing product-specific APIs, and ensuring data quality through automated testing and monitoring. By adopting a product mindset, teams can focus on delivering value and fostering innovation.

Benefits of Data Mesh

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.

What are Data Marts?

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.

Benefits of Data Marts

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:

Improved Performance: Data marts are designed to cater to the specific analytical requirements of a particular business function. By focusing on a specific subset of data, they can deliver faster query response times and improved performance compared to querying the entire data warehouse.
Business-Focused Insights: Data marts are structured to align with the needs of specific departments or teams. By providing tailored data sets, they enable users to gain insights that are directly relevant to their business functions, leading to more informed decision-making and improved operational efficiency.
Enhanced Data Governance: Data marts allow for greater control and governance over data access and usage. Since they focus on specific domains, data stewards can ensure data quality, integrity, and compliance within the context of those domains, facilitating better data governance practices.
Agile and Scalable Analytics: Data marts enable agility and scalability in analytics efforts. They can be built incrementally, adding new data sources or refining existing ones to meet evolving business needs. This flexibility allows organizations to adapt their analytics capabilities as requirements change over time.

Want to Watch a Video on this Topic Instead?

A video-based version of this tutorial is available at https://academy.dreamfactory.com. Or just click on the video below!

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 you’d like to follow along in your own environment, you’ll need to configure two database APIs within your DreamFactory instance.

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:

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:

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:

After querying Data Mesh, the results look like this:

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!