topic Re: Functional Dependency Analysis in Data Quality

Functional Dependency Analysis

Anonymous — Sat, 16 Nov 2024 13:31:53 GMT

I'm trying to compare 2 columns in a table containing customer information: Zip Code and City. First example you learn in training...
In the "Left Columns" I choose Zip Code and City, in the "Right Columns" I have chosen City and Zip Code... from the same table. No data filter. (Using MS SQL Server as a database)
I get an error when trying to Analyse: "Query not executed for indicator: 'Functional Dependency' SQL query: select count(*) as nb, count(distinct a) as nbdistinct from (select distinct postalcity as a, postalcode as b from customer_tb c) t"
The query works fine for me when executed directly in db, so what's wrong in Talend?
Any ideas?
/G

Re: Functional Dependency Analysis

Sebastiao_Qlik — Mon, 01 Mar 2010 08:57:24 GMT

Hi bluepile,
could you have a look at the error log, please?
http://talendforge.org/wiki/doku.php?id=bugtracker#attach_the_error_log_to_the_bugtracker

Re: Functional Dependency Analysis

Anonymous — Mon, 01 Mar 2010 09:53:10 GMT

Ah yes! Looking at the error log told me a few things... Thanks for that! I probably hadn't setup the user account and database connection 100% correct since Talend complained about not finding "dbo" in sysdatabases and thus didn't find the table, etc. I corrected the setup so the user has the correct user rights in the right database. Now when I execute I get another error:
My table: TBL_Customer
My Columns (varchar 20 and 27): COL_PostalCode, COL_PostalCity
2010-03-01 10:37:21,007 INFO org.talend.dq.analysis.ColumnAnalysisSqlExecutor - Executing query: SELECT COUNT(*) AS NB, COUNT(DISTINCT A) AS NBDISTINCT FROM (SELECT DISTINCT "COL_PostalCode" AS A , "COL_PostalCity" AS B FROM "TBL_Customer" C ) T
2010-03-01 10:37:21,085 ERROR org.talend.dq.analysis.AnalysisExecutor - java.lang.ClassCastException: java.lang.Integer cannot be cast to java.lang.Long
java.lang.ClassCastException: java.lang.Integer cannot be cast to java.lang.Long
at org.talend.dataquality.indicators.columnset.impl.ColumnDependencyIndicatorImpl.getNB(Unknown Source)
at org.talend.dataquality.indicators.columnset.impl.ColumnDependencyIndicatorImpl.storeSqlResults(Unknown Source)
at org.talend.dq.analysis.FunctionalDependencyExecutor.executeQuery(Unknown Source)
at org.talend.dq.analysis.FunctionalDependencyExecutor.runAnalysis(Unknown Source)
at org.talend.dq.analysis.AnalysisExecutor.execute(Unknown Source)
at org.talend.dq.analysis.AnalysisExecutorSelector.executeAnalysis(Unknown Source)
at org.talend.dataprofiler.core.ui.action.actions.AnalysisExecutorThread.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
2010-03-01 10:37:21,117 INFO org.talend.dataprofiler.core.ui.action.actions.RunAnalysisAction - Analysis "MY_TABLE_FunctionalDependency" execution code: KO: null. Duration: 0.12 s.
When I run the query in SQL the answer is 2 numerics:
NB NBDISTINCT
----------- -----------
2934 2662
Any ideas about why the application needs to convert Integer to long?
/G

Re: Functional Dependency Analysis

Sebastiao_Qlik — Mon, 01 Mar 2010 10:30:29 GMT

thanks for your feedback.
This now looks as a bug. Could you report it in our bugtracker, please?
We'll do our best to fix it quickly.

Re: Functional Dependency Analysis

Anonymous — Mon, 01 Mar 2010 13:47:47 GMT

Ok, will do.
I now did a "simple" test against both the Northwind and pubs databases which are shipped with MS SQL Server. I wanted to test zip and city columns with a functional dependency analysis on a database/table that I didn't create myself I got some other error now and I just want to post it so that maybe somebody can point out if I'm doing something obviously wrong.
1. Created a connection to my local ms sql server 2000 using sa account and db type = ms sql server. (Additional JDBC params: zeroDateTimeBehavior=convertToNull&noDatetimeStringSync=true&characterEncoding=UTF-8 and Url: jdbc:jtds:sqlserver://localhost:1433;DatabaseName=;zeroDateTimeBehavior=convertToNull&noDatetimeStringSync=true&characterEncoding=UTF-8)
2. Create Table Functional Dependency Analysis using above created connection on database Northwind and table Customers (same test done with pubs database, table stores (columns zip city))
3. Choose City and PostalCode as "Left Columns" and PostalCode and City as "Right Columns"
4. Run the analysis
Results in an error message:
Query not executed for indicator: "Functional Dependency" SQL query: SELECT COUNT(*) AS NB, COUNT(DISTINCT A) AS NBDISTINCT FROM (SELECT DISTINCT "PostalCode" AS A , "City" AS B FROM "Customers" C ) T

!ENTRY org.talend.libraries 1 0 2010-03-01 14:29:00.549
!MESSAGE 2010-03-01 14:29:00,549 INFO org.talend.dataprofiler.core.ui.editor.analysis.ColumnDependencyMasterDetailsPage - Success to save connection analysis:/PROJECT_CSC/TDQ_Data Profiling/Analyses/dGVzdA==20100301022753_0.1.ana

!ENTRY org.talend.libraries 4 0 2010-03-01 14:29:09.659
!MESSAGE 2010-03-01 14:29:09,643 ERROR org.talend.dq.analysis.ColumnAnalysisSqlExecutor - Problem when changing trying to set catalog "dbo" on connection. SQLException message: Could not locate entry in sysdatabases for database 'dbo'. No entry found with that name. Make sure that the name is entered correctly.

!ENTRY org.talend.libraries 1 0 2010-03-01 14:29:09.690
!MESSAGE 2010-03-01 14:29:09,659 INFO org.talend.dq.analysis.ColumnAnalysisSqlExecutor - Executing query: SELECT COUNT(*) AS NB, COUNT(DISTINCT A) AS NBDISTINCT FROM (SELECT DISTINCT "City" AS A , "PostalCode" AS B FROM "Customers" C ) T

!ENTRY org.talend.libraries 4 0 2010-03-01 14:29:09.737
!MESSAGE 2010-03-01 14:29:09,721 ERROR org.talend.dq.analysis.FunctionalDependencyExecutor - java.sql.SQLException: Invalid object name 'Customers'.
!STACK 0
java.sql.SQLException: Invalid object name 'Customers'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:525)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:487)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:664)
at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1114)
at org.talend.dq.analysis.ColumnAnalysisSqlExecutor.executeQuery(Unknown Source)
at org.talend.dq.analysis.FunctionalDependencyExecutor.executeQuery(Unknown Source)
at org.talend.dq.analysis.FunctionalDependencyExecutor.runAnalysis(Unknown Source)
at org.talend.dq.analysis.AnalysisExecutor.execute(Unknown Source)
at org.talend.dq.analysis.AnalysisExecutorSelector.executeAnalysis(Unknown Source)
at org.talend.dataprofiler.core.ui.action.actions.AnalysisExecutorThread.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
!ENTRY org.talend.libraries 4 0 2010-03-01 14:29:09.831
!MESSAGE 2010-03-01 14:29:09,768 ERROR org.talend.dq.analysis.ColumnAnalysisSqlExecutor - Query not executed for indicator: "Functional Dependency" SQL query: SELECT COUNT(*) AS NB, COUNT(DISTINCT A) AS NBDISTINCT FROM (SELECT DISTINCT "City" AS A , "PostalCode" AS B FROM "Customers" C ) T

!ENTRY org.talend.libraries 4 0 2010-03-01 14:29:09.940
!MESSAGE 2010-03-01 14:29:09,924 ERROR org.talend.dq.analysis.ColumnAnalysisSqlExecutor - Problem when changing trying to set catalog "dbo" on connection. SQLException message: Could not locate entry in sysdatabases for database 'dbo'. No entry found with that name. Make sure that the name is entered correctly.

!ENTRY org.talend.libraries 1 0 2010-03-01 14:29:10.112
!MESSAGE 2010-03-01 14:29:09,956 INFO org.talend.dq.analysis.ColumnAnalysisSqlExecutor - Executing query: SELECT COUNT(*) AS NB, COUNT(DISTINCT A) AS NBDISTINCT FROM (SELECT DISTINCT "PostalCode" AS A , "City" AS B FROM "Customers" C ) T

!ENTRY org.talend.libraries 4 0 2010-03-01 14:29:10.315
!MESSAGE 2010-03-01 14:29:10,143 ERROR org.talend.dq.analysis.FunctionalDependencyExecutor - java.sql.SQLException: Invalid object name 'Customers'.
!STACK 0
java.sql.SQLException: Invalid object name 'Customers'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:525)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:487)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:664)
at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1114)
at org.talend.dq.analysis.ColumnAnalysisSqlExecutor.executeQuery(Unknown Source)
at org.talend.dq.analysis.FunctionalDependencyExecutor.executeQuery(Unknown Source)
at org.talend.dq.analysis.FunctionalDependencyExecutor.runAnalysis(Unknown Source)
at org.talend.dq.analysis.AnalysisExecutor.execute(Unknown Source)
at org.talend.dq.analysis.AnalysisExecutorSelector.executeAnalysis(Unknown Source)
at org.talend.dataprofiler.core.ui.action.actions.AnalysisExecutorThread.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
!ENTRY org.talend.libraries 4 0 2010-03-01 14:29:10.487
!MESSAGE 2010-03-01 14:29:10,346 ERROR org.talend.dq.analysis.ColumnAnalysisSqlExecutor - Query not executed for indicator: "Functional Dependency" SQL query: SELECT COUNT(*) AS NB, COUNT(DISTINCT A) AS NBDISTINCT FROM (SELECT DISTINCT "PostalCode" AS A , "City" AS B FROM "Customers" C ) T

!ENTRY org.talend.libraries 1 0 2010-03-01 14:29:10.502
!MESSAGE 2010-03-01 14:29:10,502 INFO org.talend.dataprofiler.core.ui.action.actions.RunAnalysisAction - Analysis "test" execution code: KO: Query not executed for indicator: "Functional Dependency" SQL query: SELECT COUNT(*) AS NB, COUNT(DISTINCT A) AS NBDISTINCT FROM (SELECT DISTINCT "PostalCode" AS A , "City" AS B FROM "Customers" C ) T. Duration: 0.88 s.

Re: Functional Dependency Analysis

Anonymous — Mon, 01 Mar 2010 15:46:13 GMT

Update!
I was using TDQ version 3.2.2. I have now updated and I am using TOP version 3.2.3. And all my tests works, both using my own dbs and also microsofts. Sorry for wasting your time! (I must make sure to use the latest versions available and not be too quick to call for help )
/G

Re: Functional Dependency Analysis

Sebastiao_Qlik — Mon, 01 Mar 2010 16:24:13 GMT

great thanks!
I love such bugs that are already resolved 😉

Re: Functional Dependency Analysis

Anonymous — Tue, 09 Mar 2010 12:37:48 GMT

great thanks!
I love such bugs that are already resolved 😉

Will this bug also be solved in TDQ 3.2.2 (r33000)? I noticed that it has been fixed for TOS 3.2.3 ( https://community.talend.com/t5/Archive/Error-with-Mapping-component/td-p/172646) but we are currently using team edition of TDQ 3.2.2 and as far as I know there is no later version of TDQ?
Rgds/G

Re: Functional Dependency Analysis

Sebastiao_Qlik — Tue, 09 Mar 2010 14:18:13 GMT

TDQ 3.2.3 is released and is the current latest version. Contact Talend in order to obtain the download link.
It is strongly advised to upgrade your TDQ version.