by Jerod Johnson | April 07, 2020

Introducing the Query Federation Driver

Working with related data from separate sources, like a dedicated CRM service and a local database, is something that typically requires significant development time and effort. One solution to this problem is migrating your disparate data sources into a single location, such as a data warehouse, but this is not always feasible or ideal. With the new Query Federation Driver from CData Software, you can now work with related data - no matter where it's stored.

Do you need to aggregate invoices from QuickBooks Online based on Salesforce Opportunities? Do you need to map NetSuite account information based on addresses stored in a local database? Do you have data in SQL Server and MySQL databases that is related and needs to be queried together? The Query Federation Driver lets you do that and more. By combining CData drivers behind a single driver interface, you get access to all of your data from a single database-like endpoint. And you can federate & aggregate data across disparate data sources, whether those are SaaS applications, Big Data stores, NoSQL databases, flat files, and more.

In this article, we walk through a few scenarios where a user might want to work with data across multiple data sources. We also link to a technical article that shows how you can configure the Query Federation Driver to work with multiple data sources.

Example 1: Federating Relational Databases (SQL Server and MySQL)

In our SQL Server instance, we have customer data related to order information in our MySQL instance. If we wanted to know what the total cost of order freight for each company was, we would need to replicate the data from one database into the other. With the Query Federation Driver, we can query the data in place based on any existing relationships between the data "tables."

SQL Server Customers

MySQL Orders

JOINing Data Across SQL Server and MySQL

By examining our data, we can see that both sources have a customer ID field, so we JOIN our data based on that information.

SELECT 
  sqlserverdb.Customers.CompanyName, 
	ROUND(SUM(mysqldb.orders.Freight),2) AS TotalFreight
FROM 
  sqlserverdb.Customers 
JOIN 
  mysqldb.orders 
ON 
	sqlserverdb.Customers.CustomerID = mysqldb.orders.CustomerID
GROUP BY 
	sqlserverdb.Customers.CustomerID

Query Results (in DBVisualizer)

After querying the data sources (in DBVisualizer), we can see the JOINed data, ready for reporting, visualization, and other uses.

Example 2: Federating Data Across Salesforce and an Excel Spreadsheet

In an Excel file, we have opportunity data related to account information in our CRM (Salesforce). If we wanted to know what the expected revenue for our opportunities was, by state, we would need to either upload our Excel-based opportunity information to Salesforce or download our Salesforce account information to Excel. With the Query Federation Driver, we no longer have to do either.

Excel Opportunities

Salesforce Accounts

JOINing Data Across Salesforce and Excel

By examining our data, we can see that both sources have a company name field, so we JOIN our data based on that information.

SELECT
  salesforcedb.Account.BillingState,
  ROUND(SUM(exceldb.Opportunity.ExpectedRevenue),2) AS TotalRevenue
FROM
  exceldb.Opportunity
JOIN
  salesforcedb.Account
ON
  exceldb.Opportunity.Company = salesforcedb.Account.Name
GROUP By
  salesforcedb.Account.BillingState

Query Results (in DBVisualizer)

After querying the data sources (in DBVisualizer), we can see the JOINed data, ready for reporting, visualization, and other uses.

Example 3: Federating Data Across NetSuite and a MySQL Database

In this second scenario, we have inventory data in NetSuite, with related data in a MySQL database, where the MySQL database contains any pending changes for the purchase price of our NetSuite inventory items. We want to see the InternalIDs of our NetSuite items that require price updates.

MySQL Inventory Updates

NetSuite Inventory Items

JOINing Data Across MySQL and NetSuite

By examining our data, we can see that both sources have an item ID field, so we JOIN our data based on the common field and then filter further by the items that changed price.

SELECT
  netsuitedb.InventoryItem.InternalID,
  mysqldb.inventoryupdates.PurchasePrice AS NewPrice,
  netsuitedb.InventoryItem.LastPurchasePrice
FROM
  mysqldb.inventoryupdates
JOIN
  netsuitedb.InventoryItem
ON
  mysqldb.inventoryupdates.ItemID = netsuitedb.InventoryItem.ItemID
WHERE
  netsuitedb.InventoryItem.LastPurchasePrice IS NULL
OR
  mysqldb.inventoryupdates.PurchasePrice <> netsuitedb.InventoryItem.LastPurchasePrice

Query Results (in DBVisualizer)

After querying the data sources (in DBVisualizer), we can see the JOINed data and are ready to make updates to our NetSuite instance based on the changes we have discovered.

More Information & Free Trial

At this point, you can see how the CData Query Federation Driver can be used to JOIN data across data sources. Read our Knowledge Base article for a detailed walkthrough of configuring the Query Federation Driver. As always, our Support Team is available to answer any questions you might have.