Analytics table dimensions contain duplicates: ["uidlevel3", "uidlevel4"] DHIS2 v2.17

Pierre_Dane · 23 February 2015 06:46

Hi,

Our analytics run is failing with the below error. I’ve tried deleting all the data and then started reducing metadata until I had almost none in the database but the problem did not resolve. I’ve also run queries to find duplicates on many tables, especially all the org unit ones and I’ve found no duplicates. I’ve pasted the code causing the error below but I’m not sure exactly which dimension tables are being validated. Are these only the tables here:http://bazaar.launchpad.net/~dhis2-devs-core/dhis2/trunk/view/head:/dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/dimension/DefaultDimensionService.java? What other tables are used as dimensions? I definitely checked all tables that have the columns uidlivel[n] and they don’t contain duplicates so I’m thinking it must be a join somewhere.

Thanks

ERROR 2015-02-23 08:10:23,869 Unexpected error occurred in scheduled task. (TaskUtils.java [taskScheduler-9])

java.lang.IllegalStateException: Analytics table dimensions contain duplicates: [“uidlevel3”, “uidlevel4”]

at org.hisp.dhis.analytics.table.AbstractJdbcTableManager.validateDimensionColumns(AbstractJdbcTableManager.java:339)

at org.hisp.dhis.analytics.table.JdbcAnalyticsTableManager.createTable(JdbcAnalyticsTableManager.java:123)

at sun.reflect.GeneratedMethodAccessor611.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:201)

at com.sun.proxy.$Proxy100.createTable(Unknown Source)

at org.hisp.dhis.analytics.table.DefaultAnalyticsTableService.createTables(DefaultAnalyticsTableService.java:198)

at org.hisp.dhis.analytics.table.DefaultAnalyticsTableService.update(DefaultAnalyticsTableService.java:119)

at org.hisp.dhis.analytics.scheduling.AnalyticsTableTask.run(AnalyticsTableTask.java:136)

at org.hisp.dhis.scheduling.ScheduledTasks.run(ScheduledTasks.java:60)

at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)

at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)

at java.util.concurrent.FutureTask.run(FutureTask.java:262)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:178)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:292)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)

at java.lang.Thread.run(Thread.java:745)

UI output:

2015-02-23 06:10:23 Process failed: Analytics table dimensions contain duplicates: [“uidlevel3”, “uidlevel4”]

2015-02-23 06:10:23 Creating analytics tables, processes: 1, org unit levels: 5

2015-02-23 06:10:23 Updating analytics tables

2015-02-23 06:10:14 Updating resource tables

2015-02-23 06:10:14 Analytics table update process started

protected void validateDimensionColumns( List<String[]> dimensions ) + { + if ( dimensions == null || dimensions.isEmpty() ) + { + throw new IllegalStateException( "Analytics table dimensions are empty" ); + } + + List<String> columns = new ArrayList<>(); + + for ( String[] dimension : dimensions ) + { + columns.add( dimension[0] ); + } + + Set<String> duplicates = ListUtils.getDuplicates( columns ); + + if ( !duplicates.isEmpty() ) + { + throw new IllegalStateException( "Analytics table dimensions contain duplicates: " + duplicates ); + } + }

Lars · 23 February 2015 09:01

Hi Pierre,

check the organisationunitlevel table and if the level column are
continuous and has no duplicates.

select * from orgunitlevel order by level;

regards,

Lars

Calle_Hedberg · 23 February 2015 11:38

Lars,

We had a nearly identical issue with one of our database on Friday - importing one new orgunit from another databaseinstance resulted in duplicates in the orgunitlevel table, which in turn caused analytics to fail.

The key design issue (I would not call it a bug, even if it appears like that) here is the data and meta-data import functionality in DHIS2 does not assist users to avoid such duplication by

identify potential duplicates by checking e.g. name or level or UID
display the potential duplicates to the user to enable her/him to modify or drop the conflicting records.

This type of import checks have been in DHIS 1.4 for a long time.

This design issue does not only affect the orgunitlevel table - it affects much more profoundly the whole categoryoptioncombo and attributeoptioncombo system, where it is very easy to end up with multiple versions of in reality the same categoryoptioncombo name (but each with it’s own internal id and uid). That in turn causes havoc with e.g. indicators, because the indicator engine seemingly randomly picks the first of those duplicates it find. This issue has required many days of cleaning up in the back end for at least two country systems I’ve worked on recently (Namibia and Lesotho).

Any chance we could have a round-table discussion on how to re-design the import processes to handle this better?

Regards

Calle

···

On 23 February 2015 at 11:01, Lars Helge Øverland larshelge@gmail.com wrote:

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–

Hi Pierre,

check the organisationunitlevel table and if the level column are continuous and has no duplicates.

select * from orgunitlevel order by level;

regards,

Lars

Calle Hedberg

46D Alma Road, 7700 Rosebank, SOUTH AFRICA

Tel/fax (home): +27-21-685-6472

Cell: +27-82-853-5352

Iridium SatPhone: +8816-315-19274

Email: calle.hedberg@gmail.com

Skype: calle_hedberg

Lars · 23 February 2015 12:31

Hi Calle,

yes we will look into it.

The meta-data importer does match against existing objects, first on uid, then code, then name (if unique).

This problem is specific to category option combos, since option combos are generated based on category options/combos and not created explicitly. What could happen is that you have identical categories on another system, but the generated option combos get different uids. We need to make a special case for the the category option combos and compare equality on category options + combo and not just uid/code.

The same goes for org unit levels. What could happen is that you have create a level on another system with a matching numeric level but another domain. That way you can end up with two org unit levels with the same numeric level when importing. We need to make a special case during import to check not just uid/code/name but also level.

regards,

Lars

···

On Mon, Feb 23, 2015 at 12:38 PM, Calle Hedberg calle.hedberg@gmail.com wrote:

Lars,

We had a nearly identical issue with one of our database on Friday - importing one new orgunit from another databaseinstance resulted in duplicates in the orgunitlevel table, which in turn caused analytics to fail.

The key design issue (I would not call it a bug, even if it appears like that) here is the data and meta-data import functionality in DHIS2 does not assist users to avoid such duplication by

identify potential duplicates by checking e.g. name or level or UID

display the potential duplicates to the user to enable her/him to modify or drop the conflicting records.

This type of import checks have been in DHIS 1.4 for a long time.

This design issue does not only affect the orgunitlevel table - it affects much more profoundly the whole categoryoptioncombo and attributeoptioncombo system, where it is very easy to end up with multiple versions of in reality the same categoryoptioncombo name (but each with it’s own internal id and uid). That in turn causes havoc with e.g. indicators, because the indicator engine seemingly randomly picks the first of those duplicates it find. This issue has required many days of cleaning up in the back end for at least two country systems I’ve worked on recently (Namibia and Lesotho).

Any chance we could have a round-table discussion on how to re-design the import processes to handle this better?

Regards

Calle

On 23 February 2015 at 11:01, Lars Helge Øverland larshelge@gmail.com wrote:

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–

Calle Hedberg

46D Alma Road, 7700 Rosebank, SOUTH AFRICA

Tel/fax (home): +27-21-685-6472

Cell: +27-82-853-5352

Iridium SatPhone: +8816-315-19274

Email: calle.hedberg@gmail.com

Skype: calle_hedberg

Hi Pierre,

check the organisationunitlevel table and if the level column are continuous and has no duplicates.

select * from orgunitlevel order by level;

regards,

Lars

Calle_Hedberg · 23 February 2015 12:52

In short, YES.

There is clearly still a need for a more sophisticated type of import, though - where the user can SEE what is identified for import and if required modify or drop specific things…

Regards

Calle

···

On 23 February 2015 at 14:31, Lars Helge Øverland larshelge@gmail.com wrote:

Hi Calle,

yes we will look into it.

The meta-data importer does match against existing objects, first on uid, then code, then name (if unique).

This problem is specific to category option combos, since option combos are generated based on category options/combos and not created explicitly. What could happen is that you have identical categories on another system, but the generated option combos get different uids. We need to make a special case for the the category option combos and compare equality on category options + combo and not just uid/code.

The same goes for org unit levels. What could happen is that you have create a level on another system with a matching numeric level but another domain. That way you can end up with two org unit levels with the same numeric level when importing. We need to make a special case during import to check not just uid/code/name but also level.

regards,

Lars

–

On Mon, Feb 23, 2015 at 12:38 PM, Calle Hedberg calle.hedberg@gmail.com wrote:

Lars,

We had a nearly identical issue with one of our database on Friday - importing one new orgunit from another databaseinstance resulted in duplicates in the orgunitlevel table, which in turn caused analytics to fail.

The key design issue (I would not call it a bug, even if it appears like that) here is the data and meta-data import functionality in DHIS2 does not assist users to avoid such duplication by

identify potential duplicates by checking e.g. name or level or UID

display the potential duplicates to the user to enable her/him to modify or drop the conflicting records.

This type of import checks have been in DHIS 1.4 for a long time.

This design issue does not only affect the orgunitlevel table - it affects much more profoundly the whole categoryoptioncombo and attributeoptioncombo system, where it is very easy to end up with multiple versions of in reality the same categoryoptioncombo name (but each with it’s own internal id and uid). That in turn causes havoc with e.g. indicators, because the indicator engine seemingly randomly picks the first of those duplicates it find. This issue has required many days of cleaning up in the back end for at least two country systems I’ve worked on recently (Namibia and Lesotho).

Any chance we could have a round-table discussion on how to re-design the import processes to handle this better?

Regards

Calle

On 23 February 2015 at 11:01, Lars Helge Øverland larshelge@gmail.com wrote:

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–

Calle Hedberg

46D Alma Road, 7700 Rosebank, SOUTH AFRICA

Tel/fax (home): +27-21-685-6472

Cell: +27-82-853-5352

Iridium SatPhone: +8816-315-19274

Email: calle.hedberg@gmail.com

Skype: calle_hedberg

Hi Pierre,

check the organisationunitlevel table and if the level column are continuous and has no duplicates.

select * from orgunitlevel order by level;

regards,

Lars

Calle Hedberg

46D Alma Road, 7700 Rosebank, SOUTH AFRICA

Tel/fax (home): +27-21-685-6472

Cell: +27-82-853-5352

Iridium SatPhone: +8816-315-19274

Email: calle.hedberg@gmail.com

Skype: calle_hedberg

Calle_Hedberg · 23 February 2015 12:58

Sorry, one more thing:

A more fundamental challenge relates to the whole cat/att-optioncombo DESIGN for any particular database - DHIS2 enables anything with very little guidance, and the net result can be very messy configurations with multiple permutations of what is basically the same thing, like (age,gender,NEW) and (gender,NEW,age) and (NEW,age, gender) combos for data broken down on gender, age, and NEW/REPEAT (just to use a very practical/actual example from Lesotho).

What that reflect is mainly lack of guidance materials (materials, training) and lack of checks when creating new combos.

Will get back to it, for sure…

Regards

calle

···

On 23 February 2015 at 14:52, Calle Hedberg calle.hedberg@gmail.com wrote:

In short, YES.

There is clearly still a need for a more sophisticated type of import, though - where the user can SEE what is identified for import and if required modify or drop specific things…

Regards

Calle

–

On 23 February 2015 at 14:31, Lars Helge Øverland larshelge@gmail.com wrote:

Hi Calle,

yes we will look into it.

The meta-data importer does match against existing objects, first on uid, then code, then name (if unique).

This problem is specific to category option combos, since option combos are generated based on category options/combos and not created explicitly. What could happen is that you have identical categories on another system, but the generated option combos get different uids. We need to make a special case for the the category option combos and compare equality on category options + combo and not just uid/code.

The same goes for org unit levels. What could happen is that you have create a level on another system with a matching numeric level but another domain. That way you can end up with two org unit levels with the same numeric level when importing. We need to make a special case during import to check not just uid/code/name but also level.

regards,

Lars

–

Calle Hedberg

46D Alma Road, 7700 Rosebank, SOUTH AFRICA

Tel/fax (home): +27-21-685-6472

Cell: +27-82-853-5352

Iridium SatPhone: +8816-315-19274

Email: calle.hedberg@gmail.com

Skype: calle_hedberg

On Mon, Feb 23, 2015 at 12:38 PM, Calle Hedberg calle.hedberg@gmail.com wrote:

Lars,

We had a nearly identical issue with one of our database on Friday - importing one new orgunit from another databaseinstance resulted in duplicates in the orgunitlevel table, which in turn caused analytics to fail.

The key design issue (I would not call it a bug, even if it appears like that) here is the data and meta-data import functionality in DHIS2 does not assist users to avoid such duplication by

identify potential duplicates by checking e.g. name or level or UID

display the potential duplicates to the user to enable her/him to modify or drop the conflicting records.

This type of import checks have been in DHIS 1.4 for a long time.

This design issue does not only affect the orgunitlevel table - it affects much more profoundly the whole categoryoptioncombo and attributeoptioncombo system, where it is very easy to end up with multiple versions of in reality the same categoryoptioncombo name (but each with it’s own internal id and uid). That in turn causes havoc with e.g. indicators, because the indicator engine seemingly randomly picks the first of those duplicates it find. This issue has required many days of cleaning up in the back end for at least two country systems I’ve worked on recently (Namibia and Lesotho).

Any chance we could have a round-table discussion on how to re-design the import processes to handle this better?

Regards

Calle

On 23 February 2015 at 11:01, Lars Helge Øverland larshelge@gmail.com wrote:

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

–

Calle Hedberg

46D Alma Road, 7700 Rosebank, SOUTH AFRICA

Tel/fax (home): +27-21-685-6472

Cell: +27-82-853-5352

Iridium SatPhone: +8816-315-19274

Email: calle.hedberg@gmail.com

Skype: calle_hedberg

Hi Pierre,

check the organisationunitlevel table and if the level column are continuous and has no duplicates.

select * from orgunitlevel order by level;

regards,

Lars

Calle Hedberg

46D Alma Road, 7700 Rosebank, SOUTH AFRICA

Tel/fax (home): +27-21-685-6472

Cell: +27-82-853-5352

Iridium SatPhone: +8816-315-19274

Email: calle.hedberg@gmail.com

Skype: calle_hedberg