Introducing our Snowflake Data Cloud Native Application: AI-Driven Data Quality built into SQL statements! Learn More

Data Virtualization, Interzoid’s Company Data Matching API, and Denodo: Find Redundant Company Names Within a Data Source, Step-by-Step

by Interzoid Team


Posted on September 22nd, 2020


Data Virtualization, Denodo, Interzoid APIs

This is Part One of a two-part series showing the power of using APIs with data virtualization solutions.

Data Virtualization enables disparate data sources across an organization to be viewed and made accessible within a single logical data layer. This allows aggregated data to be delivered to business users in real-time, as well as provides the foundation for data warehouses, data lakes, and other analytics sources.

In this walkthrough example, Denodo is used to perform a Join on a virtual database with Interzoid’s Cloud-based Company Matching API using algorithmically generated “similarity keys.” These keys will serve as the basis of a match to identify similar and inconsistently-represented company names within a database. This essentially turns Denodo into a powerful data matching tool.

If you do not have Denodo, a limited-version called Denodo Express is available as a free download here and will work with this walkthrough.

The premise of matching data is that Interzoid’s Company Matching JSON-based API generates “similarity keys” using algorithms, machine learning, and data-specific knowledge bases. These keys help identify similar company or organization names within a single database or across multiple databases as part of an SQL Join (or other similar data matching) function.

The use of similarity keys helps to overcome the issue of inconsistent alphanumeric data for representing company names within a dataset. The technique can provide significantly greater impact and value for Data Virtualization efforts and the various data initiatives that make use of a logical data layer. It can also help identify data quality challenges in underlying source data that need to be addressed.

It is important and useful not only to identify cases of redundant data records in a single dataset, but data matching via similarity keys can also significantly enhance the ability to match and join data across multiple datasets where data can be inconsistently represented. This increases the accuracy and value of data residing within the Data Virtualization layer.

For example, the following similarity keys were generated for the company names below.

Similarity key generation example for a data matching microservice API

Using the Interzoid Company Matching API, likely matches will generate the same similarity key. You can see that similar company names generate the same similarity key. This makes them easy to identify as part of a data query.

How to achieve matching results with Denodo using data virtualization:

There are five steps in this example:

Step #1: Create a Data Source and Base View within Denodo for the source data

Step #2: Configure the Interzoid Company Matching API as a Data Source

Step #3: Configure the Interzoid API Base View

Step #4: Define the Derived View using a Join function

Step #5: Execute the Join function to display results


Step #1 – Create a Data Source and Base View within Denodo for the source data

In this example, for simplicity we will use an Excel workbook file as the data source (we could have just as easily used JDBC, ODBC, or one of the several other data sources available within Denodo). It is a list of company names with a simple description. Filename: “Example 1 – Companies.xlsx

Denodo data virtualization API Example


Within the Virtual DataPort Administration Tool (VDP) of Denodo, select “Database Administration” and create a new Server for this walkthrough. Call it “Example1.” Then, from the “Example1” folder in the Server tree, create a new folder called “Data Sources” and another one called “Base Views.” Within the “Data Sources” folder, create a new Excel Data Source.

Denodo data virtualization API Example


Next, change the File Location setting to “Local” and click the “Configure” button. Provide the path of the included Excel file and click “Ok.” Name the Data Source “companylist_excel”. Check the “has headers” option and click “Save”:

Denodo data virtualization API Example


Next, click “Create base view” on the top. Once the Base View has been created and is displayed, click “Save”

Denodo data virtualization API Example


You should now have both a Data Source and the corresponding Base View in your Server tree (you might need to drag the Base View to the Base View folder):

Denodo data virtualization API Example


Step #2 – Configure the Interzoid Company Matching API as a Data Source

The API we will use is a REST based API that we will access using query parameters with a URL location. Data is returned from this API in JSON format, so we will set the data source up in Denodo accordingly.

For more technical information about the API:

https://www.interzoid.com/apis/company-name-matching


Create a new data source of type “JSON”

Denodo data virtualization API Example


Name the data source: “interzoid_companymatch_api”

For the “Data Route”, choose “HTTP Client” and then click the “Configure” button:

Denodo data virtualization API Example


The end point for this particular API is:

https://api.interzoid.com/getcompanymatchadvanced

Calling the API requires three parameters:

License: A license key issued from Interzoid (a limited key is provided for this example, additional license keys can be obtained from Interzoid)

Company: Company name from which similarity key will be generated

Algorithm: Which similarity key generation algorithm will be applied

In this example, we will hard-code values for two of the parameters in the call, “license” and “algorithm.” For the third parameter, “company name”, we will use an “interpolated value”, as this tells Denodo to use a variable when calling the API. In our case, the variable will be company name values from the company list Base View that we have already set up from the source data in the Excel file.

To achieve this, in the URL field when configuring the HTTP client, we will provide the endpoint along with the query parameters. You can see the two hard-coded values for “license” and “algorithm”, and then also how the interpolated variable is denoted within the API call URL using an @ symbol and brackets:

https://api.interzoid.com/getcompanymatchadvanced?license=YOURLICENSEKEY&company=@{company_name}&algorithm=wide

(Note: if you don't have a license key, register at https://www.interzoid.com/register)

Denodo data virtualization API Example


Click “OK”, and then “Save.” The configured Data Source will appear on the Server tree:

Denodo data virtualization API Example


Step #3 – Configure the Interzoid API Base View

On the Configuration Panel for the “Interzoid_companymatch_api” Data Source, click “Create Base View.” You will see the following:

Denodo data virtualization API Example


Click the blue edit pen and then enter the interpolation variable notation below including the brackets:

Denodo data virtualization API Example


Then click “OK”, then “Next”, and then “OK” again at the “Configure JSON Wrapper” panel without any changes.

You should then see the following:

Denodo data virtualization API Example


The “company_name” field is the input parameter to the API. The other three are all output parameters that will comprise the output results. All four data fields are listed here. Click the blue “Save” disk to save the Base View.

You should now have the new Base View available on the Server tree as shown:

Denodo data virtualization API Example


Step #4 – Create the Derived View using a Join function

Create a new folder called “Derived Views” to follow Denodo guidelines. This is where the Data Virtualization will occur and from where we can execute our Join function. This will call the API with each value in the company list data source generating similarity keys for each and storing them within a Derived View.

Denodo data virtualization API Example


In this folder, create a new Join function:

Denodo data virtualization API Example


Then, drag the two Base Views from the tree onto the Join configuration panel. The panel should look like this:

Denodo data virtualization API Example


Next, drag the “companyname_0” field of the company list Base View to the “company_name” field of the Interzoid API Base View. You should see a line connecting the two fields (defining the Join) like this:

Denodo data virtualization API Example


Next, define the output of the Derived View. Click the “output” tab. Then, check the three fields as shown below.

Denodo data virtualization API Example


The first field selected is the input parameter to the API. This will be the same as the company name field in the Excel worksheet, so we do not need to see it twice. Also, “code” and “credits” are administrative data coming from the API that are not relevant to the Join, so check those as well. After the fields are checked, click the “Remove selected” button the bottom of the panel. You should now see this:

Denodo data virtualization API Example


Click the blue disk “Save” button. The Join function’s Derived View will now appear in the Derived Views folder on the Server tree:

Denodo data virtualization API Example


Step #5 – Execute the Join function to display results

Within the configuration panel for the Derived View, click the “Execution Panel” arrow at the top. You should see the following:

Denodo data virtualization API Example


We are ready to execute the Join function that will build the Derived View by calling the API once for each record in the company list Base View. To execute, click the white arrow in the green circle “Execute” button.

You should see the “Executing query…” message. This will take a few seconds as Denodo is going to the Web to execute the Interzoid Company Matching API for the purposes of generating similarity keys.

Denodo data virtualization API Example


You will then see the results as shown. For each company name, you will see a generated similarity key (“simkey”: a string of alphanumeric characters):

Denodo data virtualization API Example


The next thing we want to do is to sort the data by “simkey” (this can also be done by adding an “order by” clause to the SQL query prior to execution of the query). To do this within the query results, click the “simkey” column header twice to sort in descending order. Sorting by “simkey” will cause similar company names to line up next to each other. This makes the potential matches easy to find. You should see something like the following:

Denodo data virtualization API Example


Where from here?

Now that matches have been identified, there are several potential courses of action to take, depending on business requirements.

For example, data can be exported for further analysis. Within Denodo, results can be saved/exported to a delimited file for review or additional processing.

If this is a raw marketing list, redundant data could be eliminated to save outreach costs.

If this is a customer or prospect list, logic can be created to collapse redundant records in a single instance while maintaining important data from each of the records to maintain in the surviving record.

Not only can similarity keys be used to identify redundant records in a single Base View within Denodo, but multiple Base Views can have similarity keys appended, and then be used as the basis for matching across these Base Views. This provides much higher match rates when comparing and combining multiple data sources, rather than depending on exact matches as the basis of a Join.

This walkthrough is only one example of integrating Interzoid APIs within Denodo. There are also matching APIs that include specific matching algorithms for other types of data such as individual names (Bob = Robert, Johnson = Jonsen). Also, there are data enrichment APIs available to add additional data elements via the Cloud to existing data that can enhance various data initiatives.

The possibilities are limitless.

For more information, contact support@interzoid.com or visit https://www.interzoid.com

For a PDF version of the walkthrough, click here.



See our Snowflake Native Application. Achieve Data Quality built-in to SQL statements.
Identify inconsistent and duplicate data quickly and easily in data tables and files.
More...
Connect Directly to Cloud SQL Databases and Perform Data Quality Analysis
Achieve better, more consistent, more usable data.
More...
Try our Pay-as-you-Go Option
Start increasing the usability and value of your data - start small and grow with success.
More...
Launch Our Entire Data Quality Matching System on an AWS EC2 Instance
Deploy to the instance type of your choice in any AWS data center globally. Start analyzing data and identifying matches across many databases and file types in minutes.
More...
Free Usage Credits
Register for an Interzoid API account and receive free usage credits. Improve the value and usability of your strategic data assets now.
Automate API Integration into Cloud Databases
Run live data quality exception and enhancement reports on major Cloud Data Platforms direct from your browser.
More...
Check out our APIs and SDKs
Easily integrate better data everywhere.
More...
Example API Usage Code on Github
Sample Code for invoking APIs on Interzoid in multiple programming languages
Business Case: Cloud APIs and Cloud Databases
See the business case for API-driven data enhancement - directly within your important datasets
More...
Documentation and Overview
See our documentation site.
More...
Product Newsletter
Receive Interzoid product and technology updates.
More...