Where are custom dataelement/indicator attributes stored

Support - Assistance technique
1

We’re trying to extend our dataelement and indicator attributes with some custom attributes so that we can store our entire metadata dictionary for Rwanda. I have created the new fields and they display correctly, but I can’t find the table where the attributes and their values are stored. We have around 400 indicators and data elements in a spreadsheet and I’d rather not cut and paste each of the 4 new attributes into the system.

Can someone help with the names of the tables… and relationships.

Randy Wilson

Senior HMIS and Data Use Advisor, Ministry of Health/Rwanda

Integrated Health Systems Strengthening Project

Management Sciences for Health, Inc. (MSH)

2

All the types that support attribute have a corresponding attributevalues table. I.e. for data elements, its dataelementattributevalues. You might also want to look at attribute table to find your wanted attribute IDs.

···

–
Morten

On Mon, Aug 26, 2013 at 10:30 AM, Wilson,Randy rwilson@msh.org wrote:

We’re trying to extend our dataelement and indicator attributes with some custom attributes so that we can store our entire metadata dictionary for Rwanda. I have created the new fields and they display correctly, but I can’t find the table where the attributes and their values are stored. We have around 400 indicators and data elements in a spreadsheet and I’d rather not cut and paste each of the 4 new attributes into the system.

Can someone help with the names of the tables… and relationships.

Randy Wilson

Senior HMIS and Data Use Advisor, Ministry of Health/Rwanda

Integrated Health Systems Strengthening Project

Management Sciences for Health, Inc. (MSH)

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

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

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

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

3

Hi Randy, its interesting to hear that you are using DHIS 2 for data dictionary purposes.

The tables related to data element attributes are:

attribute (you have already created those attributes so no need to insert anything)

attributevalue (contains reference to the attribute and holds the actual attribute value)

dataelementattributevalues (contains references to attributevalues and dataelements - links these two together)

So you must first insert the attributevalue records - should be easy to generate that sql from the spreadsheet:

insert into attributevalue values (nextval(‘hibernate_sequence’), ‘’, );

Then comes one challenge - you must know the identifier of the attributevalue in order to link it to the right data element:

insert into dataelementattributevalues values (,);

Not sure how to best fix this but one way could be to create a script where you use the currval function and have these two sql commands alternating like this:

insert into attributevalue values (nextval(‘hibernate_sequence’), ‘’, );

insert into dataelementattributevalues values (,currval(‘hibernate_sequence’));

insert into attributevalue values (nextval(‘hibernate_sequence’), ‘’, );

insert into dataelementattributevalues values (,currval(‘hibernate_sequence’));

regards,

Lars

4

Thanks Lars.

This is very helpful. I have done something similar to add French translations for many data elements, so we should be able to figure it out with this guidance.

Randy

···

Hi Randy, its interesting to hear that you are using DHIS 2 for data dictionary purposes.

The tables related to data element attributes are:

attribute (you have already created those attributes so no need to insert anything)

attributevalue (contains reference to the attribute and holds the actual attribute value)

dataelementattributevalues (contains references to attributevalues and dataelements - links these two together)

So you must first insert the attributevalue records - should be easy to generate that sql from the spreadsheet:

insert into attributevalue values (nextval(‘hibernate_sequence’), ‘’, );

Then comes one challenge - you must know the identifier of the attributevalue in order to link it to the right data element:

insert into dataelementattributevalues values (,);

Not sure how to best fix this but one way could be to create a script where you use the currval function and have these two sql commands alternating like this:

insert into attributevalue values (nextval(‘hibernate_sequence’), ‘’, );

insert into dataelementattributevalues values (,currval(‘hibernate_sequence’));

insert into attributevalue values (nextval(‘hibernate_sequence’), ‘’, );

insert into dataelementattributevalues values (,currval(‘hibernate_sequence’));

regards,

Lars

5

Good, let us know how it goes.

Lars

···

On Tue, Aug 27, 2013 at 10:02 AM, Wilson,Randy rwilson@msh.org wrote:

Thanks Lars.

This is very helpful. I have done something similar to add French translations for many data elements, so we should be able to figure it out with this guidance.

Randy

From: Lars Helge Øverland [mailto:larshelge@gmail.com]

Sent: Monday, August 26, 2013 9:42 PM

To: Morten Olav Hansen

Cc: Wilson,Randy; dhis2-users@lists.launchpad.net

Subject: Re: [Dhis2-users] Where are custom dataelement/indicator attributes stored

Hi Randy, its interesting to hear that you are using DHIS 2 for data dictionary purposes.

The tables related to data element attributes are:

attribute (you have already created those attributes so no need to insert anything)

attributevalue (contains reference to the attribute and holds the actual attribute value)

dataelementattributevalues (contains references to attributevalues and dataelements - links these two together)

So you must first insert the attributevalue records - should be easy to generate that sql from the spreadsheet:

insert into attributevalue values (nextval(‘hibernate_sequence’), ‘’, );

Then comes one challenge - you must know the identifier of the attributevalue in order to link it to the right data element:

insert into dataelementattributevalues values (,);

Not sure how to best fix this but one way could be to create a script where you use the currval function and have these two sql commands alternating like this:

insert into attributevalue values (nextval(‘hibernate_sequence’), ‘’, );

insert into dataelementattributevalues values (,currval(‘hibernate_sequence’));

insert into attributevalue values (nextval(‘hibernate_sequence’), ‘’, );

insert into dataelementattributevalues values (,currval(‘hibernate_sequence’));

regards,

Lars