Best Of

Hi @Clinton Jones ,

We do have that for v2. Its not yet as extensive as v1, but the API to trigger workflows is available:

You can access the SwaggerUI at http://<server>/api/docs/index.html (Default local host installation: http://localhost:7701/api/docs/index.html)

The easiest way of doing this currently would be to create a template workflow which contains the map to target and transform, then clone that for each new workflow you want to create.

Go to the workflow list, select options then clone.

@Clinton Jones These ship with the product. If you go into the installation folder, usually c:\program files\experian\aperture data studio x.x.x, you will find the detaults in the matchDefaults folder. The files are named {Country}_{Scenario}_Default.{JSON/EXPR}

Scenarios are one of:

• Location: Only considers physical address information
• Household: Looks for people with the same last name at the same address (uses address and surname information)
• Individual: Looks for individuals, using Name and Address

JSON files are the blocking keys, since they follow the json specification.

EXPR are the custom Find Duplicates match rules. If you want to make it easier to read these expr files, you can use the language addon for Notepad++

Yesterday @MiteshKhatri @Akshay Davis @Katya Jermolina and I were having a discussion about approaches for matching product name information using Data Studio, so I've put together the below summary of the approach in case it helps anyone else.

Before I go through the steps I want to call out that there are a variety of different approaches you can take depending on the dataset you're looking at including: (1) using the rules-based fuzzy-matching engine Find Duplicates step, (2) building your own 'blocking' keys and then doing a self-join on records that 'block' together from which scoring can be done (e.g. edit-distance calculations) on candidate pairs or (3) using a transform step to build a 'match key' which can be used to group together records.

Either way, I'd encourage a focused approach to discovery/profiling of the data before diving into any matching tasks.

Product Matching Example

In the above example we're focusing on a field which has some consistent information held within it, but the order of these values and presentation of them alter from one record to another. Also this same approach is useful if your data appears in different fields in one source (e.g. product, size, quantity) but you want to match it to a dataset which has this information stored within a single string.

For this article, I'm using an illustrative example featuring the below fizzy drink products:

Define match elements

The approach we discussed yesterday was to identify common features from within the data which i would want to match on. In this case it would be product name, pack size & volume per unit (but in your data it might be: colour, dimensions, material etc).

Extract match elements

Once these 'matching' characteristics are defined we then want to build transformation logic which extracts the relevant information from the input string into a standardised form:

In this example a combination of techniques are used for each element, based on what I learnt about the variance of the data from profiling activity. Some of these rules include: standardisation of special characters (e.g. 7-up and 7 up), lookup tables to extract values known to be products (or you might do the same for 'colours') and regular expressions to pull out values that conform to specific patterns (e.g. pack size and volume per unit.)

Note: this may take a bit of iterating, so profiling the output of this is also a good idea to get to a point where you're comfortable with your definitions.

Create a 'matchkey'

Next simply concatenate the elements together to form a match key (in my case I used a 'remove noise' function on this key to help standardise this key a little further):

Review results

Next I then created another field to count the number of matches in each cluster (i.e. where they shared the same matchkey).Then sorted my table on the count and matchkey fields:

As you can see, the above approach has helped me cluster together records which share similar characteristics and I can then take this list through further steps to 'Harmonise' them to a common value (or define a new one using a Transform step), extract unmatched information to perform further profiling or even to output this list (and the workflow report) for review before committing any changes.

Disclaimer

Note, as with all matching, the best approach to take will vary depending on the data you're reviewing and the unique set of challenges presented in the way that the data varies in format, structure or consistency.

@MiteshKhatri The documentation you're looking for is here: https://www.edq.com/documentation/aperture-data-studio/find-duplicates-step/advanced-config/#groups

What you're looking to do is:

[
 {
  "description": "Width"
  "elementSpecifications": [
   {
    "elementType": "GENERIC_STRING",
    "elementGroups": ["Width"],
    "includeFromNChars": 1
   }
 }
]

Hello,

Think Scenario 2 would work best in this example. Especially, if new data is loaded frequently into the Data Warehouse but the analysis is done on a dataset 'as of' previous date.

For example, create a small workflow that extracts data into a snapshot like this, then execute the workflow. Feel free to apply a filter before taking a snapshot if you are only interested to analyse the subset of the data in future.

Create a new workflow, and you will see snapshots appear on the left hand side under 'Available Data Sources':

It is then possible to reuse the same snapshot in multiple workflows instead of direct database connection. Just expand the list and drag the snapshot onto the workflow builder.

Hope it answers your question.

Katya

No, they don't. Depending on your IT setup, ODBC connections can sometimes be a bit tricky, but as long as the port is accessible to the users, then this is a good way of providing up-to-date metrics to 'the business' without having to splash out on a Data Viz tool.

If you need any help with ODBC setup / config, I'd suggest raising a ticket with our support team.

A couple of other thoughts on this @stevenmckinnon...

All combinations?

In your example you state you'd like the records with DOB in years 1980 and 1981 to match, but not 1986. My question is would you also want to know that 1986 and 1981 are within 5 years? If so, I think the approach you need to go down will be to do an inner join on the dataset to uncover all of the pairs of potential matches, so you can then discover the scores yourself, e.g. see below (note I've drawn a black line through records that match to themselves for clarity, but these could also be filtered out after the join):

In this scenario, I've simply used a hash match (based on the first 6 characters of the DOB and the name). However an alternative approach could also be to use a loose matching rule in the Find Duplicates step (so as to benefit from nickname matching etc).

But then the resulting match key was then used as the join key (to itself) and the result gives me all the combination of records so I can compare the 2x DOBs to calculate the difference:

Valid / Invalid Clusters

Alternatively, you might instead want to simply flag clusters where the dobs are all within a 5 year period so you can easily spot those which are within your criteria. To do this you could use a couple of lookup aggregate functions to extract the minimum and maximum years from the DOB values where a common ClusterID is shared, something like this:

However, the challenge here is that if you have a subset of the cluster which are within the 5 year range, then they will be lost as any outliers will cause this to fail (e.g. 1980, 1981 and 1986 would result in a FALSE but if it were just 1980 and 1981 then it's evaluate TRUE)

I hope this helps and provides an alternative view/approach.

Hello,

Sure, there are some additional options that can configured when creating a database connection. To include the views, click edit the existing database connection and click 'Show advanced options".

The window will expand and there will be an option to 'Include views'.

Click apply, and the database views will appear under the JDBC connections.

Hope that helps!

Katya

A great question Sami.

At this point in time relationship discovery is not considered as a feature for Aperture Data Studio.

There are a couple of reasons for this. One of them is the fact that we have actually found this capability to be rarely used. Secondly, once relationship discovery has been done once, how relevant is it for execution a second time if the source system schemas haven't changed?

From a technical standpoint, one of the reasons that Pandora is able to do this, is because of the way the data that is cached is handled as one large repository with cross-object searching. In fact in Data Studio v1.x this is more compartmentalised and in v2.0 even more so. This is to avoid a number of issues including, but not limited to : defragmentation of the data store, privacy, compliance and expectations around data and workspace segregation.

When you're a single user on a single system connecting to many sources, these concerns are less prevalent but when you're one of many users on a server based product this becomes critical.

In order to reintroduce a cross object search would require a fair amount of new development work and this would be resource intensive when processing with value potentially limited to just that point in time.

The more appropriate approach we feel, is leveraging the auto-tagging to determine the contents of columns and then at some point making ML based proposals for reshaping the columns so that they can be combined with similar columns from other data sets so that they can all feed into the fuzzy matching engine.

When you combine disparate sources, run find duplicates and then harmonize, you move to a model where there is actually great potential to create a new best possible nominal record with lineage back to those sources that may ultimately prove more useful.