topic Re: Business Rule tests via sql patterns in Open Profiler in Data Quality

Business Rule tests via sql patterns in Open Profiler

_AnonymousUser — Sat, 16 Nov 2024 14:02:50 GMT

Hi,
I'm evaluating the Talend Open Profiler and cannot seem to locate documentation that indicates how I might perform a business rule test across multiple columns in multiple tables in a schema. I'm would ideally like to perform this test as a SQL Pattern test since I think the regular expression patterns might confuse potential users. This probably comes under the heading of data quality testing rather than data profiling.
Is what I've tried to describe above possible with this product?
Regards,
Perry.

Re: Business Rule tests via sql patterns in Open Profiler

Sebastiao_Qlik — Tue, 10 Mar 2009 08:54:44 GMT

Hi Perry,
which version of TOP are you using? The 1.1 version is limited to single column profiling.
The 1.2.0M4 development version offers a few functionalities for cross-table analyses, but I'm not sure that it is what you want.
is what you need something like:

select count(*) from city
join labels on (city.label_id = labels.label_id)
where labels.label like 'F%'

If yes, then it's not possible yet and we are going to put it in the next milestone release.
If no, then I did not understand what is your need. Could you give me more details, please?

Re: Business Rule tests via sql patterns in Open Profiler

_AnonymousUser — Wed, 11 Mar 2009 04:24:28 GMT

Thanks for your prompt reply to my query.
Yes, the test that you described is close to what I would be attempting.
IE. If column a from table a1 = 'X' and column b from table b1 = 'Y' then valid else invalid.
I'll look forward to this type of test being available in your product.
Thanks again,
Perry.

Re: Business Rule tests via sql patterns in Open Profiler

Sebastiao_Qlik — Fri, 10 Apr 2009 21:40:31 GMT

you should try it now with the latest release candidate of Talend Open Profiler : 3.1.0RC2.
There you can define a DQ Rule (in the "Libraries" folder) such as
where clause =
a = 'X' and b = 'Y'
Join clause =
a1.pk = b1.pk
This rule must then be used in a "table analysis" on the table a1.

Re: Business Rule tests via sql patterns in Open Profiler

Anonymous — Tue, 12 Jan 2010 20:11:47 GMT

Hi folks,
I did try to define a business rule and run it as a part of a table analysis. However, it did not run. I get the error message stating that the table does not exist.
Here is what I attempted:
--------------
DQ Rule:
where clause -

Join Clause -

Then the rule was used in a table analysis on Table A.
--------------
This does not seem to work. Would anyone be able to direct me to any site/material wherein I can better understand how/when to perform a table analysis using DQ rules? Thanks.

Re: Business Rule tests via sql patterns in Open Profiler

Anonymous — Wed, 13 Jan 2010 02:25:09 GMT

Hi Perry,
Did you set the join clause in "Join Condition" section of DQ Rule Settings?

DQ Rule:
where clause -

Join Clause -

Then the rule was used in a table analysis on Table A.

That's right for a DQ Rules applied on table analysis according to your steps.
A DQ Rule Settings image:
A Table analysis image: