Best Of

@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.

@stevenmckinnon If you are using the Validate Addresses step within Data Studio, and the address is valid, it will format the postcode correctly.

However, if you want tor reformat a string in this way, you can simply concatenate the last three characters of the string with the first part and a space.

To extract the outcode (the last three characters of a postcode) we can simply take the substring of the last three characters.

For the incode, we do the same thing, but for all characters but the last three, and remove any trailing spaces if there are any.

We then concatenate these two together with a space in the middle.

Finally, you only want to apply this rule if the postcode meets the format expected for a UK postcode, so would add a check for that.

The example workflow and test data are below

A note on postcode formats

"AB12 3CD" is not a valid UK postcode because the letter C never appears in the inward part of a postcode. This is also true of I, K, M, O and V.

Also, "A1" is not valid out code, "AB10" is the first valid out code. The example I will use here require valid postcode checks. The in build UK Postcode Format regex in Data Studio will not match these two examples, but if you wish to allow for these, you can use a simpler regex, which will match those invalid patterns.

^[A-Z]{1,2}[0-9][A-Z0-9]? ?[0-9][A-Z]{2}$

This bit also caught my eye:

It looks like the postcode column has some unusual numeric values. Scale in this case is the number of digits to the right of the decimal point, so a number with scale 32,531 suggests some massive number of decimal places.

Most likely this is a result of parsing a value that looks like a scientific number in 'e' notation. For example the value 1E-32531. Such a value will be parsed as 0.00000...00001, with 32,530 zeros after the decimal point. In fact this is a number too large for Data Studio to handle sensibly, and you will see it represented in a grid by 0, with length 1. However, there appears to be an issue where, despite the length being reported a 1, the "in length range" check fails to return the expected result.

Here's an example - My input data includes:

We can see that the length for the value in row 13 is reported as being 1, but when I do a check on the value to see if the length is between 1 and 6 the check fails. Compare with row 19, which behaves as expected

We'll investigate this issue as a priority in our engineering team, and updated you when we have more.

I would suggest turning of the Auto-parse scientific numbers standardisation rule for the postcode column in this case, to ensure that these types of values are treated as strings