Dear Experts,
One of other obseravation is - when i try to access the NAKISA DataQualityConsole from the Portal.
When i try to access Audit - > DataQuality OrgChart
This is the error message it was showing
Cannot find the root of your orgchart. The orgchart box may have been deleted or incorrectly specified, or no valid org structure can be found for the selected effective date. Please change the root of the chart or select another effective date.
Can you please help me on this.
Regards
Dileep P
Hi Dileep,
What does the cds.log say? Did you configure the extractor keywords and if so, is the configuration still there? I found a bug where it didn't save the extractor keywords.
Best regards,
Luke
Dear Luke,
In this NAKISA DataQualityConsole, we have two Extrators
1. SAP Extraction
2. Error Extraction & Run Audit
Configuration done for both of these Extrators and SAP Extraction is done Sucessfully.
I had a problem with the Error Extraction - Here i am facing the Issue - While Extacting - It shows Processing.....
Which never ended.
Just now i checked the CDS.LOG
102. 01 Mar 2012 09:10:23 ERROR com.nakisa.Logger - com.nakisa.framework.data.commandProcessor.impl.OracleCommandProcessor : getDataTables : Problem executing query. Data Element Name: CompanyCodeListingDataElement
Can you please help me on this issue.
Regards
Dileep P
Hi Dileep,
Your cds.log error relates to the Company Code hierarchy and not the extraction. Can you enable Database Tracing and then re-run the extract?
1. Open the AdminConsole and load your build
2. Navigate to Application-wide Settings > Enable/Disable Logs
3. Select the checkbox for Enable Database Tracing (also ensure that logging level is set to the highest level - all errors, warnings and messages)
4. Press Submit
5. Save and Publish
Best regards,
Luke
Dear Luke,
After enable the Database Trace, i got this Error messages
11081. 01 Mar 2012 15:05:43 ERROR com.nakisa.Logger - org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE OR REPLACE VIEW ERRORS_TREND_DISTRIBUTION (
11301. 01 Mar 2012 15:05:43 ERROR com.nakisa.Logger - org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [CREATE VIEW ERRORS_COMPONENT AS
11469. 01 Mar 2012 15:05:44 ERROR com.nakisa.Logger - : org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE VIEW ERRORS_BY_COUNTRY AS SELECT FIELD_VALUE AS COUNTRY, GROUPNAME, SUM(ANALYTICS_RESULT.RESULT_VALUE)100 AS AVERAGE FROM RULES_ANALYTICS, ANALYTICS_RESULT, RULES*
11214. 01 Mar 2012 15:05:43 ERROR com.nakisa.Logger - : org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [DROP VIEW ERRORS_COMPONENT]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
11554. 01 Mar 2012 15:05:44 ERROR com.nakisa.Logger - : org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [DROP VIEW ERROR_LISTING_BY_COUNTRY]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
11634. 01 Mar 2012 15:05:44 ERROR com.nakisa.Logger - : org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE VIEW ERROR_LISTING_BY_COUNTRY AS SELECT RULES_ANALYTICS.EXTRACTID, RULES_ANALYTICS.RULEID, RULES.CATEGORYID, RULES.GROUPNAME, RULES.SUBGROUPNAME, ANALYTICS_RESULT.FIELD_VALUE AS COUNTRY, ERRORS_DAILYTABLE.OBJECTID, ERRORS_DAILYTABLE.ORGUNITID, ERRORS_DAILYTABLE.POSITIONID,(ANALYTICS_RESULT.RESULT_VALUE100) AS SCORE*
11725. 01 Mar 2012 15:05:44 ERROR com.nakisa.Logger - org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [DROP VIEW ERRORSCOUNTOUGROUP]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
11856. 01 Mar 2012 15:05:44 ERROR com.nakisa.Logger - org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE VIEW ERRORSCOUNTOUGROUP
Regards
Dileep P
Hi Dileep,
Are these the only errors that you get?
Best regards,
Luke
Are you using Oracle?
If so, please use the user has the following permission granted (even if they are the owner of the tablespace, they don't get this by default!)
GRANT CREATE VIEW TO user_name ;
Stephen
Dear Friends,
After that GRANT CREATE VIEW TO UserName; Also, I am facing the same issue in my CDS.log file.
Repetedly i am getting these issues.
**16653. 01 Mar 2012 16:52:42 ERROR com.nakisa.Logger - org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE OR REPLACE VIEW ERRORS_TREND_DISTRIBUTION (**
**16873. 01 Mar 2012 16:52:42 ERROR com.nakisa.Logger - org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [CREATE VIEW ERRORS_COMPONENT AS**
**16961. 01 Mar 2012 16:52:43 ERROR com.nakisa.Logger - : org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [DROP VIEW ERRORS_BY_COUNTRY]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist**
**17041. 01 Mar 2012 16:52:43 ERROR com.nakisa.Logger - : org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE VIEW ERRORS_BY_COUNTRY AS SELECT FIELD_VALUE AS COUNTRY, GROUPNAME, SUM(ANALYTICS_RESULT.RESULT_VALUE)100 AS AVERAGE FROM RULES_ANALYTICS, ANALYTICS_RESULT, RULES***
**17212. 01 Mar 2012 16:52:43 ERROR com.nakisa.Logger - org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE VIEW ERROR_LISTING_BY_COUNTRY AS SELECT RULES_ANALYTICS.EXTRACTID, RULES_ANALYTICS.RULEID, RULES.CATEGORYID, RULES.GROUPNAME, RULES.SUBGROUPNAME, ANALYTICS_RESULT.FIELD_VALUE AS COUNTRY, ERRORS_DAILYTABLE.OBJECTID, ERRORS_DAILYTABLE.ORGUNITID, ERRORS_DAILYTABLE.POSITIONID,(ANALYTICS_RESULT.RESULT_VALUE100) AS SCORE***
*17295. 01 Mar 2012 16:52:43 ERROR com.nakisa.Logger - : org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [DROP VIEW ERRORSCOUNTOUGROUP]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist**
**17375. 01 Mar 2012 16:52:43 ERROR com.nakisa.Logger - : org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE VIEW ERRORSCOUNTOUGROUP**
**17593. 01 Mar 2012 16:55:33 ERROR com.nakisa.Logger - com.nakisa.framework.data.commandProcessor.impl.OracleCommandProcessor : getDataTables : Problem executing query. Data Element Name: ErrorsPositionHierarchyDataElement**
**17594. 01 Mar 2012 16:55:33 WARN com.nakisa.Logger - ChartingProcessor : StartChart : Could not create nodes. Error message returned:Index: 0, Size: 0**
**17595. 01 Mar 2012 16:55:33 ERROR com.nakisa.Logger - java.lang.IndexOutOfBoundsException: Index: 0, Size: 0**
Can you please help me on this issue.
Regards
Dileep P
Dear Friends,
Now all the above Errors are gone, only these error remains in the CDS.Log file.
24. 02 Mar 2012 14:15:20 ERROR com.nakisa.Logger - com.nakisa.framework.data.commandProcessor.impl.OracleCommandProcessor : getDataTables : Problem executing query. Data Element Name: ErrorsPositionHierarchyDataElement
25. 02 Mar 2012 14:15:20 WARN com.nakisa.Logger - ChartingProcessor : StartChart : Could not create nodes. Error message returned:Index: 0, Size: 0
26. 02 Mar 2012 14:15:20 ERROR com.nakisa.Logger - java.lang.IndexOutOfBoundsException: Index: 0, Size: 0
Can you please help me on this issue.
Regards
Dileep P
Hi Dileep,
Do any of the orgcharts visualize data (Data Quality Org Chart & Company Code Org Chart)? This error appears to be for the Position Org Chart.
Did you configure the root ID for the Position Org Chart?
Best regards,
Luke
Dear Luke,
YES... we have configured the root ID for the Position Org Chart. But still i am getting the same Error message.
Once agian i checked the CDS.LOG file
10680. 05 Mar 2012 09:39:29 ERROR com.nakisa.Logger - : org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE VIEW ERROR_LISTING_BY_COUNTRY AS SELECT RULES_ANALYTICS.EXTRACTID, RULES_ANALYTICS.RULEID, RULES.CATEGORYID, RULES.GROUPNAME, RULES.SUBGROUPNAME, ANALYTICS_RESULT.FIELD_VALUE AS COUNTRY, ERRORS_DAILYTABLE.OBJECTID, ERRORS_DAILYTABLE.ORGUNITID, ERRORS_DAILYTABLE.POSITIONID,(ANALYTICS_RESULT.RESULT_VALUE100) AS SCORE*
10771. 05 Mar 2012 09:39:29 ERROR com.nakisa.Logger - org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [DROP VIEW ERRORSCOUNTOUGROUP]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
10902. 05 Mar 2012 09:39:29 ERROR com.nakisa.Logger - org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE VIEW ERRORSCOUNTOUGROUP
11067. 05 Mar 2012 09:47:50 ERROR com.nakisa.Logger - com.nakisa.framework.data.commandProcessor.impl.OracleCommandProcessor : getDataTables : Problem executing query. Data Element Name: ErrorsPositionHierarchyDataElement
11068. 05 Mar 2012 09:47:50 WARN com.nakisa.Logger - ChartingProcessor : StartChart : Could not create nodes. Error message returned:Index: 0, Size: 0
11069. 05 Mar 2012 09:47:50 ERROR com.nakisa.Logger - java.lang.IndexOutOfBoundsException: Index: 0, Size: 0
Regards
Dileep P
Dear Luke,
Still i am facing the same issue.
This is the error message it was showing
Cannot find the root of your orgchart. The orgchart box may have been deleted or incorrectly specified, or no valid org structure can be found for the selected effective date. Please change the root of the chart or select another effective date.
CDS.Log says same as above reply.
Best Regards
Dileep P
Hi Dileep,
Is this still an issue? Can you confirm that those views exist and that your database tables have the strucure data in them?
Best regards,
Luke
Dear Luke,
This issue still there.
Today i have the SAP Extraction for this - Extraction Successful.
When i try to do the Error Extraction - This shows continuously Processing... Finally Unsuccessful
I just checked the CDS.log
5. 12 Apr 2012 13:58:41 WARN com.nakisa.Logger -
class com.nakisa.framework.utility.Strings : convertStringToDate : No date is
specified to convert. The today's date will be used.
6. 12 Apr 2012 13:58:41 WARN com.nakisa.Logger - class
com.nakisa.framework.utility.Strings : convertStringToDate : No date is
specified to convert. The today's date will be used.
485740. 12 Apr 2012 18:09:20 ERROR com.nakisa.Logger -
: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL
grammar [DROP VIEW ERROR_LISTING_BY_COUNTRY]; nested exception is
java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not
exist
485741.
485742. 12 Apr 2012
18:09:20 ERROR com.nakisa.Logger -
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL
grammar [DROP VIEW ERROR_LISTING_BY_COUNTRY]; nested exception is
java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not
exist
Repeatedly this error. Please suggest.
Best Regards
Dileep P
Dileep the two error entries you listed are failing SQL statements:
DROP VIEW ERROR_LISTING_BY_COUNTRY
It is trying to remove the ERROR_LISTING_BY_COUNTRY view from your database. The error indicates the view does not exist. Since you were trying to remove it anyway the action is moot.
Usually these drops are in place because the view is to be (re-)created. So Luke's query still holds. After the job completes are the views(/tables) in place?
e.g. can you do:
and get results sets back?
(these are the two views listed in your post of 05-Mar-2012 04:35)
Dear Stephen,
Yes!!! I just checked this perticular schema.
Basically i run these two statement
Both the tables are displayed - no rows selected.
For this i have done - once again the SAP Extraction this got Sucessful.
But when i try to do the Error Extraction for Audit. This shows continuously Processing... Finally Unsuccessful.
When i see the CDS.Log file.
-----------------------------------------------------------------
122. 18 Apr 2012 15:19:34 ERROR com.nakisa.Logger - com.nakisa.framework.data.commandProcessor.impl.OracleCommandProcessor : getDataTables : Problem executing query. Data Element Name: OrgUnitHierarchyDataElement
123. 18 Apr 2012 15:19:34 WARN com.nakisa.Logger - ChartingProcessor : StartChart : Could not create nodes. Error message returned:Index: 0, Size: 0
124. 18 Apr 2012 15:19:34 ERROR com.nakisa.Logger - java.lang.IndexOutOfBoundsException: Index: 0, Size: 0
276. 18 Apr 2012 15:26:48 WARN com.nakisa.Logger - class com.nakisa.framework.utility.Strings : convertStringToDate : No date is specified to convert. The today's date will be used
489. 18 Apr 2012 15:28:13 ERROR com.nakisa.Logger - class com.nakisa.framework.data.commandProcessor.base.SqlBatchReadWriteCommandProcessor : createRows : Error creating rows
490. 18 Apr 2012 15:28:13 ERROR com.nakisa.Logger - org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [INSERT INTO PA0001 (PERNR,EMPNAMEF,EMPNAME,PERSONNELSUBAREA,COMPANYCODE,ORGUNIT,CONTROLLINGAREA,COSTCENTER,EMPLOYEEGROUP,EMPLOYEESUBGROUP,POSITION,JOB,PERSONNELAREA,STARTDATE,BUSINESSAREA,PAYROLLAREA) VALUES ('00009771','S Suchitra .',' S SUCHITRA','I001','1000','40000285','1000','','E','T3','40011594','00000000','I001',TO_DATE('2006-8-14 0:0:0', 'YYYY-MM-DD HH24:MI:SS'),'','01')]; SQL state [null]; error code [17081]; error occurred during batching: ORA-12899: value too large for column "NKSDTQCNSL"."PA0001"."EMPNAMEF" (actual: 44, maximum: 40)
642. 18 Apr 2012 15:44:41 ERROR com.nakisa.Logger - com.nakisa.framework.data.commandProcessor.impl.OracleCommandProcessor : getDataTables : Problem executing query. Data Element Name:
643. 18 Apr 2012 15:44:41 ERROR com.nakisa.Logger - RulesProcessor : Objects cannot be retrieved.
781. 18 Apr 2012 16:31:14 ERROR com.nakisa.Logger - : org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [DROP INDEX AVC_PR]; SQL state [72000]; error code [1418]; ORA-01418: specified index does not exist
1074. 18 Apr 2012 16:33:12 ERROR com.nakisa.Logger - com.nakisa.framework.data.commandProcessor.impl.OracleCommandProcessor : getDataTables : Problem executing query. Data Element Name: CompanyCodeDataElement
1075. 18 Apr 2012 16:33:12 WARN com.nakisa.Logger - ChartingProcessor : StartChart : Could not create nodes. Error message returned:Index: 0, Size: 0
1076. 18 Apr 2012 16:33:12 ERROR com.nakisa.Logger - java.lang.IndexOutOfBoundsException: Index: 0, Size: 0
-----------------------------------------------------------------
Please suggest.
Best Reagrds
Dileep P
Hi Dileep,
Your database encoding doesn't seem correct according to this error:
490. 18 Apr 2012 15:28:13 ERROR com.nakisa.Logger - org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [INSERT INTO PA0001 (PERNR,EMPNAMEF,EMPNAME,PERSONNELSUBAREA,COMPANYCODE,ORGUNIT,CONTROLLINGAREA,COSTCENTER,EMPLOYEEGROUP,EMPLOYEESUBGROUP,POSITION,JOB,PERSONNELAREA,STARTDATE,BUSINESSAREA,PAYROLLAREA) VALUES ('00009771','S Suchitra .',' S SUCHITRA','I001','1000','40000285','1000','','E','T3','40011594','00000000','I001',TO_DATE('2006-8-14 0:0:0', 'YYYY-MM-DD HH24:MI:SS'),'','01')]; SQL state [null]; error code [17081]; error occurred during batching: ORA-12899: value too large for column "NKSDTQCNSL"."PA0001"."EMPNAMEF" (actual: 44, maximum: 40)
For Oracle I recommend WE8MSWIN1252, although if you are using AL32UTF8 then ensure that parameter nls_length_semantics is set to type string with value CHAR.
This value is set per Oracle instance (not per tablespace) and will affect all tablespaces within that instance. For example, if you are co-hosting your Oracle DB for OrgChart on the DB instance used by NW then you have a conflict as NW only supports BYTE and not CHAR.
Best regards,
Luke
Dear Luke,
As you recommend. I just changed to dedicated Schema with CHAR.
then once again i would have taken the SAP Extraction - this Sucessfully.
But Analytics it Failed - still it shows as completed.
Generating Analytic: Position Hierarchy Unoccupied Vacant Position Count ...
Failed.
Generating Analytic: Position Hierarchy Occupied Vacant Position Count ...
Failed.
Analytics generation completed.
After this - i just take the
Error Extraction for Audit. This shows continuously Processing... Finally Unsuccessful.
When i see the CDS.Log file.
-----------------------------------------------------------------
509473. 19 Apr 2012 15:21:33 ERROR com.nakisa.Logger - : org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE VIEW ERRORS_BY_COUNTRY AS SELECT FIELD_VALUE AS COUNTRY, GROUPNAME, SUM(ANALYTICS_RESULT.RESULT_VALUE)*100 AS AVERAGE FROM RULES_ANALYTICS, ANALYTICS_RESULT, RULES
509638. 19 Apr 2012 15:21:33 ERROR com.nakisa.Logger - : org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE VIEW ERROR_LISTING_BY_COUNTRY AS SELECT RULES_ANALYTICS.EXTRACTID, RULES_ANALYTICS.RULEID, RULES.CATEGORYID, RULES.GROUPNAME, RULES.SUBGROUPNAME, ANALYTICS_RESULT.FIELD_VALUE AS COUNTRY, ERRORS_DAILYTABLE.OBJECTID, ERRORS_DAILYTABLE.ORGUNITID, ERRORS_DAILYTABLE.POSITIONID,(ANALYTICS_RESULT.RESULT_VALUE*100) AS SCORE
509729. 19 Apr 2012 15:21:33 ERROR com.nakisa.Logger - org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [DROP VIEW ERRORSCOUNTOUGROUP]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
509807. 19 Apr 2012 15:21:33 ERROR com.nakisa.Logger - : org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [CREATE VIEW ERRORSCOUNTOUGROUP
510073. 19 Apr 2012 15:21:33 ERROR com.nakisa.Logger - org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [DROP VIEW EmployeeNationalityMissing]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
510152. 19 Apr 2012 15:21:33 ERROR com.nakisa.Logger - : org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [DROP VIEW EmployeePhotoMissing]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
510154. 19 Apr 2012 15:21:33 ERROR com.nakisa.Logger - org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [DROP VIEW EmployeePhotoMissing]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
5510274. 19 Apr 2012 15:26:49 ERROR com.nakisa.Logger - com.nakisa.framework.data.commandProcessor.impl.OracleCommandProcessor : getDataTables : Problem executing query. Data Element Name: ErrorsPositionHierarchyDataElement
510275. 19 Apr 2012 15:26:49 WARN com.nakisa.Logger - ChartingProcessor : StartChart : Could not create nodes. Error message returned:Index: 0, Size: 0
510276. 19 Apr 2012 15:26:49 ERROR com.nakisa.Logger - java.lang.IndexOutOfBoundsException: Index: 0, Size: 0
510586. 19 Apr 2012 15:27:26 ERROR com.nakisa.Logger - com.nakisa.framework.data.commandProcessor.impl.OracleCommandProcessor : getDataTables : Problem executing query. Data Element Name: AllErrorsDataElement
510602. 19 Apr 2012 15:27:31 ERROR com.nakisa.Logger - com.nakisa.framework.data.commandProcessor.impl.OracleCommandProcessor : getDataTables : Problem executing query. Data Element Name: ErrorsPositionHierarchyDataElement
510603. 19 Apr 2012 15:27:31 WARN com.nakisa.Logger - ChartingProcessor : StartChart : Could not create nodes. Error message returned:Index: 0, Size: 0
-----------------------------------------------------------------
Please Suggest.
Best Regards
Dileep P
Hi Dileep,
For the analytics, did you configure the SAP connection in Configure Staging > Configure Analytics?
I strongly advise that you download DataQualityConsole 3.0 SP3 from SMP, undeply the existing application and deploy that. You should be able to export your configuration from the existing SP2 build and import it into the new SP3 build. Howver, if you've not done anything other than configure the database connection etc then it's probably best just to start over again with SP3.
Best regards,
Luke
Dear Luke,
As you recomeneed, we just installed the DataQualityConsole 3.0 SP03.
Now few error are gone, but still some error in product level.
When i try to configure the Analytics, this error messages is coming up.
Please suggest.
Best Regards
Dileep P
Dileep,
Can you confirm you have set relative URLs? (see "VSN30_SP3_Deployment_En.pdf" page 5, section 2.2).
If so, I would suggest raising an OSS if you have.
Regards,
Stephen
Hi Dileep,
This is standard in all SPs of DQC - analytics is not available.
Best regards,
Luke
Dear Luke,
As i understand from you, This analytics is not required for DataQulaityConsole or any other?
Please suggest
Best Regards
Dileep P
Hi Dileep,
Yes, that is correct. Analytics are not required for DQC. Especially since they could be based on bad data 
Best regards,
Luke
