User Community Service Desk Downloads
If you can't find the product or version you're looking for, visit support.ataccama.com/downloads

Join Tables for Cross-Entity Checks

It is possible to join two or more tables from any compatible sources to compare data via cross-entity checks. For example, if the content of two or more columns in different tables needs to be the same and it should be monitored, you can apply rules on the joined data to achieve this.

In general, comparing data from different sources entails the following:

  1. Create a virtual catalog item (VCI) in ONE Desktop. See Virtual Catalog Items.

  2. Join the tables from the different sources which can be merged. For more information about these steps, see Frequently Used Steps and Plans.

  3. Sync the virtual catalog item with ONE.

  4. Create a rule to compare data across different tables. See Create DQ Evaluation Rule.

  5. Create a monitoring project to periodically compare values from the two tables. See Monitoring Projects.

Example

In the following example, we are comparing the customernumber and customername columns from catalog items Customers and Orders to make sure these values are the same in both catalog items.

Prerequisites

Steps

  1. Launch ONE Desktop.

  2. In the File Explorer, find the ONE server you’re connected to and right-click Catalog Items.

  3. Select New > Virtual Catalog Item.

    Create new virtual catalog item
  4. Enter a name and description for the virtual catalog item and select Next.

    Configure virtual catalog item
  5. Select Add to add an existing catalog item.

    Add catalog items
  6. Find and select the catalog items you want to compare and select OK. In this example, we are using the catalog items Customers and Orders.

    Select catalog items to compare
  7. Select the columns that should be in the virtual catalog item and then Next.

    Select columns
  8. Select Next.

  9. Define the folder (workspace) name or select an existing one and select Finish.

    You have now created the virtual catalog item.

    Configure workspace folder
  10. In the VCI plan that opens, add a Join step and connect it with the catalog items and the Integration Output step.

    Virtual catalog item plan - Add the Join step
  11. Configure the Join step:

    • On the General tab, select which columns from the two catalog items should be the Left Key and the Right Key respectively. In our example, the Left Key is customername, the Right Key is customernumber, converted to string.

    • Next, select the Join Type (INNER) and the Match Strategy (MERGE).

      The Join step - General tab
    • Switch to the Columns tab and specify which columns should be present after the join is performed. Delete any duplicates.

      The Join step - Columns tab
    • Select OK.

  12. Publish the virtual catalog item. In ONE Explorer, find the virtual catalog item in Components and right-click it. Select Publish.

    Publish virtual catalog item
  13. Make sure the Implementation State is set to Implemented and select Publish again.

    Virtual catalog item published

    The virtual catalog item is now available in Data Catalog in ONE:

    Virtual catalog item in ONE
  14. Run profiling on the table.

  15. Create a rule to compare whether two attributes have the same values (rule logic: Attribute1 is the same as Attribute2). For more detailed instructions, see Create DQ Evaluation Rule.

    Create DQ rule
  16. Publish the rule. See Publish Changes.

  17. Create a new monitoring project using the virtual catalog item as input.

    Create monitoring project
  18. Configure the monitoring project to apply the DQ rule you created to the attributes you want to compare.

  19. Run the monitoring.

    The comparison results are now available on the Configuration & Results tab of your monitoring project.

Was this page useful?