Hi all,
I´m running a live version with data and metadata imported and it all works
fine but I don´t find the way to generate the Analytic tables.
If I run them manually I get this Process failed: Exception during
execution (log attached).
If I remember well it expects the tables to be generated by the automatic
process so i scheduled the task yesterday to be executed daily and left the
laptop on... but nothing changes...
Maybe it got suspended and the task was never executed... (will change that
now and lets see tomorrow) but anyway, if that is my problem, I suggest it
may be useful to generate the tables even if the task is triggered manually
for the first time.
Version: 2.13Build revision: 12908Build date: 2013-12-04 16:24
* INFO 11:42:10,857 Create organisation unit structure table SQL: CREATE TABLE
_orgunitstructure ( organisationunitid INTEGER NOT NULL PRIMARY KEY, level
INTEGER, idlevel1 INTEGER, uidlevel1 CHARACTER(11), idlevel2 INTEGER, uidlevel2
CHARACTER(11), idlevel3 INTEGER, uidlevel3 CHARACTER(11), idlevel4 INTEGER,
uidlevel4 CHARACTER(11), idlevel5 INTEGER, uidlevel5 CHARACTER(11), idlevel6
INTEGER, uidlevel6 CHARACTER(11)); (JdbcResourceTableStore.java
[taskScheduler-7])
* INFO 11:42:10,903 Organisation unit structure table generated
(DefaultResourceTableService.java [taskScheduler-7])
* INFO 11:42:10,911 Create category option combo name table SQL: CREATE TABLE
_categoryoptioncomboname ( categoryoptioncomboid INTEGER NOT NULL PRIMARY KEY,
categoryoptioncomboname VARCHAR(250) ) (JdbcResourceTableStore.java
[taskScheduler-7])
* INFO 11:42:10,988 Category option combo name table generated
(DefaultResourceTableService.java [taskScheduler-7])
* INFO 11:42:11,112 Create data element group set table SQL: CREATE TABLE
_dataelementgroupsetstructure ( dataelementid INTEGER NOT NULL, dataelementname
VARCHAR (250), "Health Data Elements " VARCHAR (250), "mCaHnjHSgW0"
CHARACTER(11), PRIMARY KEY ( dataelementid ) )
(CreateDataElementGroupSetTableStatement.java [taskScheduler-7])
* INFO 11:42:11,130 Data element group set table generated
(DefaultResourceTableService.java [taskScheduler-7])
* INFO 11:42:11,211 Create indicator group set table SQL: CREATE TABLE
_indicatorgroupsetstructure ( indicatorid INTEGER NOT NULL, indicatorname
VARCHAR (250), PRIMARY KEY ( indicatorid ) )
(CreateIndicatorGroupSetTableStatement.java [taskScheduler-7])
* INFO 11:42:11,212 Indicator group set table generated
(DefaultResourceTableService.java [taskScheduler-7])
* INFO 11:42:11,307 Create organisation unit group set table SQL: CREATE TABLE
_organisationunitgroupsetstructure ( organisationunitid INTEGER NOT NULL,
organisationunitname VARCHAR (250), "Ownership" VARCHAR (250), "xPpF0XXMxdT"
CHARACTER(11), "Type" VARCHAR (250), "nLsuwTKm63F" CHARACTER(11), "Type of
Health Facility" VARCHAR (250), "tzIlEDCf6Ke" CHARACTER(11), PRIMARY KEY (
organisationunitid ) ) (CreateOrganisationUnitGroupSetTableStatement.java
[taskScheduler-7])
* INFO 11:42:11,335 Organisation unit group set table generated
(DefaultResourceTableService.java [taskScheduler-7])
* INFO 11:42:11,339 Create category structure table SQL: CREATE TABLE
_categorystructure ( categoryoptioncomboid INTEGER NOT NULL,
categoryoptioncomboname VARCHAR (250), "Age groups under/over 5" VARCHAR (250),
"g1FhSyJjgMf" CHARACTER(11), "default" VARCHAR (250), "fotp6ViKEQv"
CHARACTER(11), PRIMARY KEY ( categoryoptioncomboid ) )
(CreateCategoryTableStatement.java [taskScheduler-7])
* INFO 11:42:11,342 Category table generated (DefaultResourceTableService.java
[taskScheduler-7])
* INFO 11:42:11,355 Create data element structure SQL: CREATE TABLE
_dataelementstructure ( dataelementid INTEGER NOT NULL PRIMARY KEY,
dataelementname VARCHAR(250), periodtypeid INTEGER, periodtypename VARCHAR(250)
) (JdbcResourceTableStore.java [taskScheduler-7])
* INFO 11:42:11,387 Data element table generated
(DefaultResourceTableService.java [taskScheduler-7])
* INFO 11:42:11,557 Create period structure SQL: CREATE TABLE _periodstructure
(periodid INTEGER NOT NULL PRIMARY KEY, iso VARCHAR(10) NOT NULL, daysno
INTEGER NOT NULL, daily VARCHAR(10), weekly VARCHAR(10), monthly VARCHAR(10),
bimonthly VARCHAR(10), quarterly VARCHAR(10), sixmonthly VARCHAR(10), yearly
VARCHAR(10), financialapril VARCHAR(10), financialjuly VARCHAR(10),
financialoct VARCHAR(10)) (JdbcResourceTableStore.java [taskScheduler-7])
* INFO 11:42:11,560 Date period table generated
(DefaultResourceTableService.java [taskScheduler-7])
* INFO 11:42:11,654 Create date period structure SQL: CREATE TABLE
_dateperiodstructure (dateperiod DATE NOT NULL PRIMARY KEY, daily VARCHAR(10),
weekly VARCHAR(10), monthly VARCHAR(10), bimonthly VARCHAR(10), quarterly
VARCHAR(10), sixmonthly VARCHAR(10), yearly VARCHAR(10), financialapril
VARCHAR(10), financialjuly VARCHAR(10), financialoct VARCHAR(10))
(JdbcResourceTableStore.java [taskScheduler-7])
* INFO 11:42:16,731 Period table generated (DefaultResourceTableService.java
[taskScheduler-7])
* INFO 11:42:16,733 Create data element category option combo SQL: CREATE
TABLE _dataelementcategoryoptioncombo (dataelementuid VARCHAR(11) NOT NULL,
categoryoptioncombouid VARCHAR(11) NOT NULL) (JdbcResourceTableStore.java
[taskScheduler-7])
* INFO 11:42:16,733 Insert data element category option combo SQL: insert into
_dataelementcategoryoptioncombo (dataelementuid, categoryoptioncombouid) select
de.uid as dataelementuid, coc.uid as categoryoptioncombouid from dataelement de
join categorycombos_optioncombos cc on de.categorycomboid = cc.categorycomboid
join categoryoptioncombo coc on cc.categoryoptioncomboid =
coc.categoryoptioncomboid (JdbcResourceTableStore.java [taskScheduler-7])
* INFO 11:42:16,740 Create data element category option combo index SQL:
CREATE INDEX dataelement_categoryoptioncombo ON _dataelementcategoryoptioncombo
(dataelementuid, categoryoptioncombouid) (JdbcResourceTableStore.java
[taskScheduler-7])
* INFO 11:42:16,741 Data element category option combo table generated
(DefaultResourceTableService.java [taskScheduler-7])
* INFO 11:42:16,743 [Level: INFO, category: DATAMART, time: Wed Dec 11
11:42:16 CET 2013, message: Updating resource tables] (InMemoryNotifier.java
[taskScheduler-7])
* INFO 11:42:16,838 Create organisation unit structure table SQL: CREATE TABLE
_orgunitstructure ( organisationunitid INTEGER NOT NULL PRIMARY KEY, level
INTEGER, idlevel1 INTEGER, uidlevel1 CHARACTER(11), idlevel2 INTEGER, uidlevel2
CHARACTER(11), idlevel3 INTEGER, uidlevel3 CHARACTER(11), idlevel4 INTEGER,
uidlevel4 CHARACTER(11), idlevel5 INTEGER, uidlevel5 CHARACTER(11), idlevel6
INTEGER, uidlevel6 CHARACTER(11)); (JdbcResourceTableStore.java
[taskScheduler-7])
* INFO 11:42:16,853 Organisation unit structure table generated
(DefaultResourceTableService.java [taskScheduler-7])
* INFO 11:42:16,855 Create category option combo name table SQL: CREATE TABLE
_categoryoptioncomboname ( categoryoptioncomboid INTEGER NOT NULL PRIMARY KEY,
categoryoptioncomboname VARCHAR(250) ) (JdbcResourceTableStore.java
[taskScheduler-7])
* INFO 11:42:16,857 Category option combo name table generated
(DefaultResourceTableService.java [taskScheduler-7])
* INFO 11:42:16,870 Create data element group set table SQL: CREATE TABLE
_dataelementgroupsetstructure ( dataelementid INTEGER NOT NULL, dataelementname
VARCHAR (250), "Health Data Elements " VARCHAR (250), "mCaHnjHSgW0"
CHARACTER(11), PRIMARY KEY ( dataelementid ) )
(CreateDataElementGroupSetTableStatement.java [taskScheduler-7])
* INFO 11:42:16,873 Data element group set table generated
(DefaultResourceTableService.java [taskScheduler-7])
* INFO 11:42:16,876 Create indicator group set table SQL: CREATE TABLE
_indicatorgroupsetstructure ( indicatorid INTEGER NOT NULL, indicatorname
VARCHAR (250), PRIMARY KEY ( indicatorid ) )
(CreateIndicatorGroupSetTableStatement.java [taskScheduler-7])
* INFO 11:42:16,877 Indicator group set table generated
(DefaultResourceTableService.java [taskScheduler-7])
* INFO 11:42:16,889 Create organisation unit group set table SQL: CREATE TABLE
_organisationunitgroupsetstructure ( organisationunitid INTEGER NOT NULL,
organisationunitname VARCHAR (250), "Ownership" VARCHAR (250), "xPpF0XXMxdT"
CHARACTER(11), "Type" VARCHAR (250), "nLsuwTKm63F" CHARACTER(11), "Type of
Health Facility" VARCHAR (250), "tzIlEDCf6Ke" CHARACTER(11), PRIMARY KEY (
organisationunitid ) ) (CreateOrganisationUnitGroupSetTableStatement.java
[taskScheduler-7])
* INFO 11:42:16,897 Organisation unit group set table generated
(DefaultResourceTableService.java [taskScheduler-7])
* INFO 11:42:16,901 Create category structure table SQL: CREATE TABLE
_categorystructure ( categoryoptioncomboid INTEGER NOT NULL,
categoryoptioncomboname VARCHAR (250), "Age groups under/over 5" VARCHAR (250),
"g1FhSyJjgMf" CHARACTER(11), "default" VARCHAR (250), "fotp6ViKEQv"
CHARACTER(11), PRIMARY KEY ( categoryoptioncomboid ) )
(CreateCategoryTableStatement.java [taskScheduler-7])
* INFO 11:42:16,904 Category table generated (DefaultResourceTableService.java
[taskScheduler-7])
* INFO 11:42:16,914 Create data element structure SQL: CREATE TABLE
_dataelementstructure ( dataelementid INTEGER NOT NULL PRIMARY KEY,
dataelementname VARCHAR(250), periodtypeid INTEGER, periodtypename VARCHAR(250)
) (JdbcResourceTableStore.java [taskScheduler-7])
* INFO 11:42:16,923 Data element table generated
(DefaultResourceTableService.java [taskScheduler-7])
* INFO 11:42:16,931 Create period structure SQL: CREATE TABLE _periodstructure
(periodid INTEGER NOT NULL PRIMARY KEY, iso VARCHAR(10) NOT NULL, daysno
INTEGER NOT NULL, daily VARCHAR(10), weekly VARCHAR(10), monthly VARCHAR(10),
bimonthly VARCHAR(10), quarterly VARCHAR(10), sixmonthly VARCHAR(10), yearly
VARCHAR(10), financialapril VARCHAR(10), financialjuly VARCHAR(10),
financialoct VARCHAR(10)) (JdbcResourceTableStore.java [taskScheduler-7])
* INFO 11:42:16,934 Date period table generated
(DefaultResourceTableService.java [taskScheduler-7])
* INFO 11:42:16,941 Create date period structure SQL: CREATE TABLE
_dateperiodstructure (dateperiod DATE NOT NULL PRIMARY KEY, daily VARCHAR(10),
weekly VARCHAR(10), monthly VARCHAR(10), bimonthly VARCHAR(10), quarterly
VARCHAR(10), sixmonthly VARCHAR(10), yearly VARCHAR(10), financialapril
VARCHAR(10), financialjuly VARCHAR(10), financialoct VARCHAR(10))
(JdbcResourceTableStore.java [taskScheduler-7])
* INFO 11:42:18,831 Period table generated (DefaultResourceTableService.java
[taskScheduler-7])
* INFO 11:42:18,833 Create data element category option combo SQL: CREATE
TABLE _dataelementcategoryoptioncombo (dataelementuid VARCHAR(11) NOT NULL,
categoryoptioncombouid VARCHAR(11) NOT NULL) (JdbcResourceTableStore.java
[taskScheduler-7])
* INFO 11:42:18,833 Insert data element category option combo SQL: insert into
_dataelementcategoryoptioncombo (dataelementuid, categoryoptioncombouid) select
de.uid as dataelementuid, coc.uid as categoryoptioncombouid from dataelement de
join categorycombos_optioncombos cc on de.categorycomboid = cc.categorycomboid
join categoryoptioncombo coc on cc.categoryoptioncomboid =
coc.categoryoptioncomboid (JdbcResourceTableStore.java [taskScheduler-7])
* INFO 11:42:18,836 Create data element category option combo index SQL:
CREATE INDEX dataelement_categoryoptioncombo ON _dataelementcategoryoptioncombo
(dataelementuid, categoryoptioncombouid) (JdbcResourceTableStore.java
[taskScheduler-7])
* INFO 11:42:18,836 Data element category option combo table generated
(DefaultResourceTableService.java [taskScheduler-7])
* INFO 11:42:18,837 [Level: INFO, category: DATAMART, time: Wed Dec 11
11:42:18 CET 2013, message: Updating analytics tables] (InMemoryNotifier.java
[taskScheduler-7])
* INFO 11:42:18,838 Starting update, no of processes: 3: 0:00:00.000
(Clock.java [taskScheduler-7])
* INFO 11:42:18,960 Get tables using earliest: 2011-01-01 00:00:00.0, latest:
2012-11-30 00:00:00.0 (AbstractJdbcTableManager.java [taskScheduler-7])
* INFO 11:42:18,994 Partition tables: [analytics_2011, analytics_2012], last 3
years: false: 0:00:00.156 (Clock.java [taskScheduler-7])
* INFO 11:42:18,995 [Level: INFO, category: DATAMART, time: Wed Dec 11
11:42:18 CET 2013, message: Creating analytics tables] (InMemoryNotifier.java
[taskScheduler-7])
* INFO 11:42:19,006 Create SQL: create table analytics_temp_2011
("mCaHnjHSgW0" character(11),"tzIlEDCf6Ke" character(11),"xPpF0XXMxdT"
character(11),"nLsuwTKm63F" character(11),"uidlevel1" character(11),"uidlevel2"
character(11),"uidlevel3" character(11),"uidlevel4" character(11),"uidlevel5"
character(11),"uidlevel6" character(11),"daily" character varying(10),"weekly"
character varying(10),"monthly" character varying(10),"bimonthly" character
varying(10),"quarterly" character varying(10),"sixmonthly" character
varying(10),"yearly" character varying(10),"financialapril" character
varying(10),"financialjuly" character varying(10),"financialoct" character
varying(10),de character(11) not null,co character(11) not null,level
integer,daysxvalue double, daysno integer not null, value double)
(JdbcAnalyticsTableManager.java [taskScheduler-7])
* INFO 11:42:19,018 Create SQL: create table analytics_temp_2012
("mCaHnjHSgW0" character(11),"tzIlEDCf6Ke" character(11),"xPpF0XXMxdT"
character(11),"nLsuwTKm63F" character(11),"uidlevel1" character(11),"uidlevel2"
character(11),"uidlevel3" character(11),"uidlevel4" character(11),"uidlevel5"
character(11),"uidlevel6" character(11),"daily" character varying(10),"weekly"
character varying(10),"monthly" character varying(10),"bimonthly" character
varying(10),"quarterly" character varying(10),"sixmonthly" character
varying(10),"yearly" character varying(10),"financialapril" character
varying(10),"financialjuly" character varying(10),"financialoct" character
varying(10),de character(11) not null,co character(11) not null,level
integer,daysxvalue double, daysno integer not null, value double)
(JdbcAnalyticsTableManager.java [taskScheduler-7])
* INFO 11:42:19,020 Created analytics tables: 0:00:00.182 (Clock.java
[taskScheduler-7])
* INFO 11:42:19,020 [Level: INFO, category: DATAMART, time: Wed Dec 11
11:42:19 CET 2013, message: Populating analytics tables] (InMemoryNotifier.java
[taskScheduler-7])
* INFO 11:42:19,038 Populate SQL: insert into analytics_temp_2011
("mCaHnjHSgW0","tzIlEDCf6Ke","xPpF0XXMxdT","nLsuwTKm63F","uidlevel1","uidlevel2","uidlevel3","uidlevel4","uidlevel5","uidlevel6","daily","weekly","monthly","bimonthly","quarterly","sixmonthly","yearly","financialapril","financialjuly","financialoct",de,co,level,daysxvalue,
daysno, value) select
degs."mCaHnjHSgW0",ougs."tzIlEDCf6Ke",ougs."xPpF0XXMxdT",ougs."nLsuwTKm63F",ous."uidlevel1",ous."uidlevel2",ous."uidlevel3",ous."uidlevel4",ous."uidlevel5",ous."uidlevel6",ps."daily",ps."weekly",ps."monthly",ps."bimonthly",ps."quarterly",ps."sixmonthly",ps."yearly",ps."financialapril",ps."financialjuly",ps."financialoct",de.uid,co.uid,ous.level,cast(dv.value
as double) * ps.daysno as daysxvalue, ps.daysno as daysno, cast(dv.value as
double) as value from datavalue dv left join _dataelementgroupsetstructure degs
on dv.dataelementid=degs.dataelementid left join
_organisationunitgroupsetstructure ougs on dv.sourceid=ougs.organisationunitid
left join _categorystructure cs on
dv.categoryoptioncomboid=cs.categoryoptioncomboid left join _orgunitstructure
ous on dv.sourceid=ous.organisationunitid left join _periodstructure ps on
dv.periodid=ps.periodid left join dataelement de on
dv.dataelementid=de.dataelementid left join categoryoptioncombo co on
dv.categoryoptioncomboid=co.categoryoptioncomboid left join period pe on
dv.periodid=pe.periodid where de.valuetype = 'int' and de.domaintype =
'aggregate' and pe.startdate >= '2011-01-01' and pe.startdate <= '2011-12-31'
and dv.value is not null and dv.value regexp '^(-?[0-9]+)(\.[0-9]+)?(E\d+)?$'
and ( dv.value != '0' or de.aggregationtype = 'average' or de.zeroissignificant
= true ) (JdbcAnalyticsTableManager.java [SimpleAsyncTaskExecutor-11])
* INFO 11:42:19,058 Populate SQL: insert into analytics_temp_2012
("mCaHnjHSgW0","tzIlEDCf6Ke","xPpF0XXMxdT","nLsuwTKm63F","uidlevel1","uidlevel2","uidlevel3","uidlevel4","uidlevel5","uidlevel6","daily","weekly","monthly","bimonthly","quarterly","sixmonthly","yearly","financialapril","financialjuly","financialoct",de,co,level,daysxvalue,
daysno, value) select
degs."mCaHnjHSgW0",ougs."tzIlEDCf6Ke",ougs."xPpF0XXMxdT",ougs."nLsuwTKm63F",ous."uidlevel1",ous."uidlevel2",ous."uidlevel3",ous."uidlevel4",ous."uidlevel5",ous."uidlevel6",ps."daily",ps."weekly",ps."monthly",ps."bimonthly",ps."quarterly",ps."sixmonthly",ps."yearly",ps."financialapril",ps."financialjuly",ps."financialoct",de.uid,co.uid,ous.level,cast(dv.value
as double) * ps.daysno as daysxvalue, ps.daysno as daysno, cast(dv.value as
double) as value from datavalue dv left join _dataelementgroupsetstructure degs
on dv.dataelementid=degs.dataelementid left join
_organisationunitgroupsetstructure ougs on dv.sourceid=ougs.organisationunitid
left join _categorystructure cs on
dv.categoryoptioncomboid=cs.categoryoptioncomboid left join _orgunitstructure
ous on dv.sourceid=ous.organisationunitid left join _periodstructure ps on
dv.periodid=ps.periodid left join dataelement de on
dv.dataelementid=de.dataelementid left join categoryoptioncombo co on
dv.categoryoptioncomboid=co.categoryoptioncomboid left join period pe on
dv.periodid=pe.periodid where de.valuetype = 'int' and de.domaintype =
'aggregate' and pe.startdate >= '2012-01-01' and pe.startdate <= '2012-12-31'
and dv.value is not null and dv.value regexp '^(-?[0-9]+)(\.[0-9]+)?(E\d+)?$'
and ( dv.value != '0' or de.aggregationtype = 'average' or de.zeroissignificant
= true ) (JdbcAnalyticsTableManager.java [SimpleAsyncTaskExecutor-13])
* INFO 11:42:19,140 [Level: ERROR, category: DATAMART, time: Wed Dec 11
11:42:19 CET 2013, message: Process failed: Exception during execution]
(InMemoryNotifier.java [taskScheduler-7])_______________________________________________
Mailing list: https://launchpad.net/~dhis2-users
Post to : [email protected]
Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp
