cancel
Showing results for 
Search instead for 
Did you mean: 

A Rule Validating Uniqueness within a Single Table

Former Member
0 Kudos

I'm having a tough time trying to get a uniqueness rule working within Information Steward 4.0. My scenario is this:

I have a field or a combination of fields that exist in Table A and I want to validate with a data insight rule that they are unique within Table A.

All the code / references that I have seen up to this point have been using the exist() function with a lookup table, ie two tables involved. My uniqueness rule is only evaluating one table.

If anyone has solved this, it would be awesome to get some example syntax. Thanks so much!!

Tom

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Tom,

There are a few different aspects to consider here depending on what is your end goal.

When the field or combination of fields are not unique, if you want all the non-unique records to fail the validation rule so that you can then review all different values to determine which one is correct, then the problem becomes tricky.

Before you can implement this rule in Information Steward 4.0, you would need to prepare the data appropriately. For example, you can build a 'View' at the database layer using GROUP BY and COUNT syntax to determine how many distinct values exist for the given field or combination of fields and have it as a column in the view. Within your rule logic then, you would simply lookup into the view to determine if there are multiple values (>1) for the given field or combination of the field.

You can in theory build the similar logic within the rule using SQL statement but executing SQL with GROUP BY and COUNT for every record validation would be extremely inefficient. So preparing the data beforehand is a better option.

On the other hand if you would want to implement relatively complex logic to identify duplicates that may include fuzzy matching and so on, you can implement it in Data Services using 'Match' functionality and then use outcome of 'Match' within Information Steward rule to determine uniqueness.

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi, i still have a question.

I wanna use the uniqueness rule to take them in the scorecard.

I have Customer Data and i wanna do a check if i have duplicate entries, the duplicate entry should be checked on around 4 fields, if they all are the same then its a duplicate and if near then i should have a score-> like i can do in the cleaning advisor, but i wanna have this info in the scorecard.

I have a complex alternative, to do this in the advanced editor in the uniqueness rule and do a fuzzy search...

Could somebody give me a hint... i only wanna do this in information steward...its a business request...

thanks a lot

kind regards

Alex

amish1980_95
Participant
0 Kudos

Hello,

Did you find the right approach to follow for your issue?

Thanks,

Indu.

Former Member
0 Kudos

Hi Tom,

We're exploring Information Steward to understand how it can be used in a traditional enterprise data warehousing setup. I was trying to setup something similar to your question where I need to report on the uniqueness of a combination of fields within a single table.

I see that we could do this using data profiling for uniqueness. But when I looked at your question about rules for the same, I couldn't understand why would one need a rule to be setup, instead of using profiling for uniqueness. Can you please share your thought behind the same? This would help us understand the usage of the tool better.

Cheers

Mohan

Former Member
0 Kudos

Hi Mohan,

My use case for evaluating uniqueness as a rule is to include uniqueness measures into the Information Steward data quality scorecard and manage/trend uniqueness over time. I don't think you can include profile results in a scorecard at this time. That is why I wanted to create the rule.

Hope this helps.

Tom

former_member182007
Active Contributor
0 Kudos

Tom

Were you able to answer for your above query ? Please share.

-Deepak

Former Member
0 Kudos

Deepak et al.,

To solve this problem I did use a view as suggested by Hemant.

Even though it works, this solution is a little clunky and I would advocate that this functionality be built into IS as a uniqueness function so that you don't have to create a view.

Tom

Former Member
0 Kudos

Agree on the suggestion to improve the product in order to make this task easier. You can always submit such suggestions in Information Steward Idea Place Community at https://cw.sdn.sap.com/cw/community/ideas/businessanalytics/sboinformationsteward

In this case there is already an idea around 'Uniqueness Rule Support'. Feel free to add your comments and vote on the idea.

Idea Place is one of the channels to influence the future product direction or roadmap of the product. I would highly encourage you to use it as you come across the new ideas / suggestions around product capabilities.