DHIS2 2.32 Analytics

Any insights and suggestions on the following are very welcome.
Recently upgraded instance to version: 2.32.1
Build revision:290d889
Build date:2019-07-04 21:53
Jasper reports version:6.3.1
Last analytics table runtime: 2 m, 7 s
Database version: PostgreSQL 10.9
Java version: 1.8.0_221

Data is not showing in visualisation despite triggering the analytics process manually. Analytics is not running completely (they run only for 2.7min) which is too short a time for the amount of data in the database.

When loading dashboards.,log throws the following:
* INFO 2019-08-27 14:19:55,004 Query failed, likely because the requested analytics table does not exist (JdbcAnalyticsManager.java [taskScheduler-8])
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select ax.“dx”,ax.“uidlevel3”, sum(daysxvalue) / 365 as value from analytics_2018 as ax where ax.“dx” in (‘ZDUDEOYQuHs’) and ax.“uidlevel3” in (‘ZMhvihfvanx’, ‘PoWQdy5S9Xi’, ‘EQg6N2v2TXj’, ‘IQCeFVtGUtg’, ‘P1HnUkQDPw0’, ‘HxjTgTNq2YD’, ‘LswTbnG3C0c’, ‘ENQA7sOVrPw’, ‘EbpvGZRGG04’, ‘A2Kgu7zMgJr’, ‘aimBmvkBWy3’, ‘M8moEd4dNYo’, ‘hQXbPPfQtLy’, ‘gd6cNp4HjsE’, ‘uJG4WFdUJSv’, ‘S1l6Df6SvLL’, ‘hrbAl7aUBwV’) and ( ax.“yearly” in (‘2018’) ) and ax.“year” in (2018) group by ax.“dx”,ax.“uidlevel3”]; nested exception is org.postgresql.util.PSQLException: ERROR: column ax.year does not exist
Hint: Perhaps you meant to reference the column “ax.yearly”.
Position: 436
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:419)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:478)
at org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:519)
at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getKeyValueMap(JdbcAnalyticsManager.java:651)
at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getAggregatedDataValues(JdbcAnalyticsManager.java:173)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.aop.interceptor.AsyncExecutionInterceptor$1.call(AsyncExecutionInterceptor.java:115)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.postgresql.util.PSQLException: ERROR: column ax.year does not exist
Hint: Perhaps you meant to reference the column “ax.yearly”.
Position: 436
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:224)
at com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyStatement.java:220)
at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:461)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:408)
… 18 more
* INFO 2019-08-27 14:19:55,004 Query failed, likely because the requested analytics table does not exist (JdbcAnalyticsManager.java [taskScheduler-25])
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select ax.“dx”,ax.“uidlevel3”, sum(daysxvalue) / 365 as value from analytics_2018 as ax where ax.“dx” in (‘ZDUDEOYQuHs’) and ax.“uidlevel3” in (‘qubeTudDNSC’, ‘FEGZA6BEAjj’, ‘GeGgihdOJ2p’, ‘CTdWtNCPRNh’, ‘cd0I6ndBMrT’, ‘MHcRPWjeguM’, ‘S4Lr8zJqfHx’, ‘RY0I8Ha0azq’, ‘WVBSPqTMrPP’, ‘QWwg51OvU0a’, ‘F2sNoOfO4u3’, ‘MDkBXGJ5CyR’, ‘o7sh3DOqth2’, ‘RBK39eC5LuY’, ‘UeDRejssxJd’, ‘rUBJcTYUnAE’, ‘VbGP71DRL3K’, ‘bVY4PcjsciS’) and ( ax.“yearly” in (‘2018’) ) and ax.“year” in (2018) group by ax.“dx”,ax.“uidlevel3”]; nested exception is org.postgresql.util.PSQLException: ERROR: column ax.year does not exist
Hint: Perhaps you meant to reference the column “ax.yearly”.
Position: 451
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:419)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:478)
at org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:519)
at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getKeyValueMap(JdbcAnalyticsManager.java:651)
at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getAggregatedDataValues(JdbcAnalyticsManager.java:173)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.aop.interceptor.AsyncExecutionInterceptor$1.call(AsyncExecutionInterceptor.java:115)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.postgresql.util.PSQLException: ERROR: column ax.year does not exist
Hint: Perhaps you meant to reference the column “ax.yearly”.
Position: 451
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:224)
at com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyStatement.java:220)
at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:461)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:408)
… 18 more
* INFO 2019-08-27 14:19:55,019 Query failed, likely because the requested analytics table does not exist (JdbcAnalyticsManager.java [taskScheduler-15])
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select ax.“dx”,ax.“uidlevel3”, sum(value) as value from analytics_2018 as ax where ax.“dx” in (‘Is1XcpgeN4I’) an

1 Like

Hi Anthony,

Try to go to the “Data Administration” app and run “Analytics tables” generation.

Then also ensure you set the period (years) in scheduler for subsequent analytics

Good luck,

Ranga

1 Like

Dear Ranga,
Thanks for your feedback, let me check how that will work

1 Like

Thanks, that has worked.

1 Like