Issue importing CSV with categoryoptioncombos

I’m moving data between instances of DHIS-2 (from the HMIS to the Data Warehouse with a subset of data elements).

Since the dataelementids and orgunitides are not always the same in the two systems, I’m using the code fields for those. I’ve set up a query to provide the data I need with one exception: if the categoryoptioncomboids are not default, I need to find the id in the destination system.

Would it not be possible to use the categoryoptioncomboname during import, since there is no code field for categoryoptioncombos…a file that is autogenerated?

Here is a sample of the CSV: (I’ve added the categoryoptioncombo field to illustrate the issue).

“dataelement”;“period”;“orgunit”;“categoryoptioncomboid”;“categoryoptioncombo”;“value”

“8942”;“201312”;“573”;“14”;"(default)";“24”

“8942”;“201312”;“94”;“14”;"(default)";“34”

“8942”;“201312”;“433”;“14”;"(default)";“23”

“8942”;“201312”;“96”;“14”;"(default)";“30”

“8942”;“201312”;“266”;“14”;"(default)";“30”

“8942”;“201312”;“310”;“14”;"(default)";“20”

“8222”;“201311”;“384”;“297”;"(0-11 months)";“118”

“8222”;“201311”;“370”;“297”;"(0-11 months)";“15”

“8222”;“201311”;“155”;“297”;"(0-11 months)";“56”

“8222”;“201311”;“158”;“297”;"(0-11 months)";“46”

We move data across to the data warehouse each month, so I’d like to make the process as simple as possible.

Does anyone have a suggestion?

Randy Wilson

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

Integrated Health Systems Strengthening Project

Management Sciences for Health, Inc. (MSH)

BP 371

Kigali, Rwanda

+250-788308835

Skype Name (wilsonrandy_us)

www.msh.org

Description: http://ih.constantcontact.com/fs017/1102482301888/img/438.gif

Stronger health systems. Greater health impact.

Description: Follow MSH on FacebookDescription: Follow MSH on TwitterDescription: YouTube Follow us on Facebook and Twitter

Hi Randy,
The CSV file looks a little funky to me. It looks like you are using the internal IDs (not the UIDs). Likely what you will need is a transformation mapping of how to get between your source instance and your destination instance. This may involve mapping source data element UIDs to target data elements IDs and source category combo options to target category option combos.

As a trivial example, lets suppose you have

“Number of people males tested for malaria” and “Number of females tested for malaria”, both with default category combos in your source system. In the target system you have “Number of persons tested for malaria” with “Male” and “Female” as category combo options. You would therefore need to map your source data elements to the target data elements (in this case, a single one) and your source category combo options (default) to two category combo options.

Of course, there could be more complex transformations, such as combining certain source data elements/category combo options into different targets ones, but should also be possible, for instance by creating indicators in your source system, and effectively treating these as data elements in the target system. We have used this approach to map between different sources and targets, and it is generally effective, but obviously, with a sort of ETL-glue in between.

Regards,

Jason

image

image

image

image

···

On Tue, Feb 18, 2014 at 3:06 PM, Wilson,Randy rwilson@msh.org wrote:

I’m moving data between instances of DHIS-2 (from the HMIS to the Data Warehouse with a subset of data elements).

Since the dataelementids and orgunitides are not always the same in the two systems, I’m using the code fields for those. I’ve set up a query to provide the data I need with one exception: if the categoryoptioncomboids are not default, I need to find the id in the destination system.

Would it not be possible to use the categoryoptioncomboname during import, since there is no code field for categoryoptioncombos…a file that is autogenerated?

Here is a sample of the CSV: (I’ve added the categoryoptioncombo field to illustrate the issue).

“dataelement”;“period”;“orgunit”;“categoryoptioncomboid”;“categoryoptioncombo”;“value”

“8942”;“201312”;“573”;“14”;"(default)";“24”

“8942”;“201312”;“94”;“14”;"(default)";“34”

“8942”;“201312”;“433”;“14”;"(default)";“23”

“8942”;“201312”;“96”;“14”;"(default)";“30”

“8942”;“201312”;“266”;“14”;"(default)";“30”

“8942”;“201312”;“310”;“14”;"(default)";“20”

“8222”;“201311”;“384”;“297”;"(0-11 months)";“118”

“8222”;“201311”;“370”;“297”;"(0-11 months)";“15”

“8222”;“201311”;“155”;“297”;"(0-11 months)";“56”

“8222”;“201311”;“158”;“297”;"(0-11 months)";“46”

We move data across to the data warehouse each month, so I’d like to make the process as simple as possible.

Does anyone have a suggestion?

Randy Wilson

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

Integrated Health Systems Strengthening Project

Management Sciences for Health, Inc. (MSH)

BP 371

Kigali, Rwanda

+250-788308835

Skype Name (wilsonrandy_us)

www.msh.org

Stronger health systems. Greater health impact.

Follow us on Facebook and Twitter


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

Hi Randy

image

image

image

image

···

On 18 February 2014 13:06, Wilson,Randy rwilson@msh.org wrote:

I’m moving data between instances of DHIS-2 (from the HMIS to the Data Warehouse with a subset of data elements).

Since the dataelementids and orgunitides are not always the same in the two systems, I’m using the code fields for those. I’ve set up a query to provide the data I need with one exception: if the categoryoptioncomboids are not default, I need to find the id in the destination system.

Would it not be possible to use the categoryoptioncomboname during import, since there is no code field for categoryoptioncombos…a file that is autogenerated?

You have hit precisely the soft underbelly of the categoryoptioncombo problem here (one of them anyway). Because it does not have a code it is not easily matched. What you need is for the uid to be the same on both sides. The name is not so reliable, not least because it is a bit unpredictable how it is constructed (the order) from category names.

Your best bet might be to treat HMIS as your metadata registry for these datasets (with associated datelements, categories, options and combos). Then use the “Detailed Metadata Export” option to export the datasets and finally check the “with dependencies” box when you see it. If you import this into data warehouse you should get the same categoryoptioncombos on both sides.

Slightly further down the line we are hoping to “fix” this problem in dxf2 by exporting the categoryoptions themselves rather than the categoryoptioncombos, but this is a bit further down the line (and maybe won’t really work with csv).

Try the detailed metadata export and see how that works (it was actually created with your use case in mind).

Bob

Here is a sample of the CSV: (I’ve added the categoryoptioncombo field to illustrate the issue).

“dataelement”;“period”;“orgunit”;“categoryoptioncomboid”;“categoryoptioncombo”;“value”

“8942”;“201312”;“573”;“14”;"(default)";“24”

“8942”;“201312”;“94”;“14”;"(default)";“34”

“8942”;“201312”;“433”;“14”;"(default)";“23”

“8942”;“201312”;“96”;“14”;"(default)";“30”

“8942”;“201312”;“266”;“14”;"(default)";“30”

“8942”;“201312”;“310”;“14”;"(default)";“20”

“8222”;“201311”;“384”;“297”;"(0-11 months)";“118”

“8222”;“201311”;“370”;“297”;"(0-11 months)";“15”

“8222”;“201311”;“155”;“297”;"(0-11 months)";“56”

“8222”;“201311”;“158”;“297”;"(0-11 months)";“46”

We move data across to the data warehouse each month, so I’d like to make the process as simple as possible.

Does anyone have a suggestion?

Randy Wilson

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

Integrated Health Systems Strengthening Project

Management Sciences for Health, Inc. (MSH)

BP 371

Kigali, Rwanda

+250-788308835

Skype Name (wilsonrandy_us)

www.msh.org

Stronger health systems. Greater health impact.

Follow us on Facebook and Twitter


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

Agree to what is being said. The simplest would be to simply synchronize
the UIDs of data elements, org unit, cat option combos, on both sides.
Changing existing data element UIDs must be done with caution since it
affects indicator formulas, validation rule formulas and custom data entry
forms.

Lars

Lars what makes this scenario slightly fiddlier than normal (is there a normal!), is that it is only a subset of the datelements of the one system which are of interest in the other system. So its not a wholesale import-export of structural metadata. The orgunits at least should already be synched. The trick now is to synch the selected dataelements and their dependencies, which should work with the detailed metadata export … though it might require deleting the subjects on the target system first (not 100% sure without trying).

···

On 18 February 2014 19:29, Lars Helge Øverland larshelge@gmail.com wrote:

Agree to what is being said. The simplest would be to simply synchronize the UIDs of data elements, org unit, cat option combos, on both sides. Changing existing data element UIDs must be done with caution since it affects indicator formulas, validation rule formulas and custom data entry forms.

Lars

HI Bob,

Thanks for these suggestions. Actually the categoryoptioncomboname doesn’t have a uid either. Remember, it normally combines several options e.g. (Male, <5 Year). The options themselves have uids (e.g. Male or <5 years), but the combinations are system generated in the _categoryoptioncomboname table which only has two fields (categoryoptioncomboid and categoryoptioncomboname). The fact that the names are enclosed in parentheses also causes problems sometimes when exporting data though CSVs, but currently the only thing we could match on is the categoryoptioncomboname.

I’ll try the new detailed metadata export features. Maybe that will help.

Randy

image

image

image

image

···

Hi Randy

On 18 February 2014 13:06, Wilson,Randy rwilson@msh.org wrote:

I’m moving data between instances of DHIS-2 (from the HMIS to the Data Warehouse with a subset of data elements).

Since the dataelementids and orgunitides are not always the same in the two systems, I’m using the code fields for those. I’ve set up a query to provide the data I need with one exception: if the categoryoptioncomboids are not default, I need to find the id in the destination system.

Would it not be possible to use the categoryoptioncomboname during import, since there is no code field for categoryoptioncombos…a file that is autogenerated?

You have hit precisely the soft underbelly of the categoryoptioncombo problem here (one of them anyway). Because it does not have a code it is not easily matched. What you need is for the uid to be the same on both sides. The name is not so reliable, not least because it is a bit unpredictable how it is constructed (the order) from category names.

Your best bet might be to treat HMIS as your metadata registry for these datasets (with associated datelements, categories, options and combos). Then use the “Detailed Metadata Export” option to export the datasets and finally check the “with dependencies” box when you see it. If you import this into data warehouse you should get the same categoryoptioncombos on both sides.

Slightly further down the line we are hoping to “fix” this problem in dxf2 by exporting the categoryoptions themselves rather than the categoryoptioncombos, but this is a bit further down the line (and maybe won’t really work with csv).

Try the detailed metadata export and see how that works (it was actually created with your use case in mind).

Bob

Here is a sample of the CSV: (I’ve added the categoryoptioncombo field to illustrate the issue).

“dataelement”;“period”;“orgunit”;“categoryoptioncomboid”;“categoryoptioncombo”;“value”

“8942”;“201312”;“573”;“14”;"(default)";“24”

“8942”;“201312”;“94”;“14”;"(default)";“34”

“8942”;“201312”;“433”;“14”;"(default)";“23”

“8942”;“201312”;“96”;“14”;"(default)";“30”

“8942”;“201312”;“266”;“14”;"(default)";“30”

“8942”;“201312”;“310”;“14”;"(default)";“20”

“8222”;“201311”;“384”;“297”;"(0-11 months)";“118”

“8222”;“201311”;“370”;“297”;"(0-11 months)";“15”

“8222”;“201311”;“155”;“297”;"(0-11 months)";“56”

“8222”;“201311”;“158”;“297”;"(0-11 months)";“46”

We move data across to the data warehouse each month, so I’d like to make the process as simple as possible.

Does anyone have a suggestion?

Randy Wilson

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

Integrated Health Systems Strengthening Project

Management Sciences for Health, Inc. (MSH)

BP 371

Kigali, Rwanda

+250-788308835

Skype Name (wilsonrandy_us)

www.msh.org

Stronger health systems. Greater health impact.

Follow us on Facebook and Twitter


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

Hi Randy,
In fact, the category combo options do have UIDs, but they are not available through the resource tables for some reason. You can join to “categoryoptioncombo” with the categoryoptioncomboid field. I may be confusing things, since it seems there is more to it than from your emails, but just wanted to point this out.

Regards,

Jason

image

image

image

image

···

On Wed, Feb 19, 2014 at 8:43 PM, Wilson,Randy rwilson@msh.org wrote:

HI Bob,

Thanks for these suggestions. Actually the categoryoptioncomboname doesn’t have a uid either. Remember, it normally combines several options e.g. (Male, <5 Year). The options themselves have uids (e.g. Male or <5 years), but the combinations are system generated in the _categoryoptioncomboname table which only has two fields (categoryoptioncomboid and categoryoptioncomboname). The fact that the names are enclosed in parentheses also causes problems sometimes when exporting data though CSVs, but currently the only thing we could match on is the categoryoptioncomboname.

I’ll try the new detailed metadata export features. Maybe that will help.

Randy

From: Bob Jolliffe [mailto:bobjolliffe@gmail.com]

Sent: Tuesday, February 18, 2014 8:58 PM

To: Wilson,Randy

Cc: dhis2-users@lists.launchpad.net

Subject: Re: [Dhis2-users] Issue importing CSV with categoryoptioncombos

Hi Randy

On 18 February 2014 13:06, Wilson,Randy rwilson@msh.org wrote:

I’m moving data between instances of DHIS-2 (from the HMIS to the Data Warehouse with a subset of data elements).

Since the dataelementids and orgunitides are not always the same in the two systems, I’m using the code fields for those. I’ve set up a query to provide the data I need with one exception: if the categoryoptioncomboids are not default, I need to find the id in the destination system.

Would it not be possible to use the categoryoptioncomboname during import, since there is no code field for categoryoptioncombos…a file that is autogenerated?

You have hit precisely the soft underbelly of the categoryoptioncombo problem here (one of them anyway). Because it does not have a code it is not easily matched. What you need is for the uid to be the same on both sides. The name is not so reliable, not least because it is a bit unpredictable how it is constructed (the order) from category names.

Your best bet might be to treat HMIS as your metadata registry for these datasets (with associated datelements, categories, options and combos). Then use the “Detailed Metadata Export” option to export the datasets and finally check the “with dependencies” box when you see it. If you import this into data warehouse you should get the same categoryoptioncombos on both sides.

Slightly further down the line we are hoping to “fix” this problem in dxf2 by exporting the categoryoptions themselves rather than the categoryoptioncombos, but this is a bit further down the line (and maybe won’t really work with csv).

Try the detailed metadata export and see how that works (it was actually created with your use case in mind).

Bob

Here is a sample of the CSV: (I’ve added the categoryoptioncombo field to illustrate the issue).

“dataelement”;“period”;“orgunit”;“categoryoptioncomboid”;“categoryoptioncombo”;“value”

“8942”;“201312”;“573”;“14”;"(default)";“24”

“8942”;“201312”;“94”;“14”;"(default)";“34”

“8942”;“201312”;“433”;“14”;"(default)";“23”

“8942”;“201312”;“96”;“14”;"(default)";“30”

“8942”;“201312”;“266”;“14”;"(default)";“30”

“8942”;“201312”;“310”;“14”;"(default)";“20”

“8222”;“201311”;“384”;“297”;"(0-11 months)";“118”

“8222”;“201311”;“370”;“297”;"(0-11 months)";“15”

“8222”;“201311”;“155”;“297”;"(0-11 months)";“56”

“8222”;“201311”;“158”;“297”;"(0-11 months)";“46”

We move data across to the data warehouse each month, so I’d like to make the process as simple as possible.

Does anyone have a suggestion?

Randy Wilson

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

Integrated Health Systems Strengthening Project

Management Sciences for Health, Inc. (MSH)

BP 371

Kigali, Rwanda

+250-788308835

Skype Name (wilsonrandy_us)

www.msh.org

Stronger health systems. Greater health impact.

Follow us on Facebook and Twitter


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


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

HI Bob,

Thanks for these suggestions. Actually the categoryoptioncomboname
doesn't have a uid either.

That is true. But the categoryoptioncombo has. (I never really liked or
fully understood the idea of the _categoryoptioncomboname table anyway.
always felt the name should just be a field in the categoryoptioncombo
table. anyway that is an aside)

image

image

image

image

···

On 19 February 2014 18:43, Wilson,Randy <rwilson@msh.org> wrote:

Remember, it normally combines several options e.g. (Male, <5 Year). The
options themselves have uids (e.g. Male or <5 years), but the combinations
are system generated in the _categoryoptioncomboname table which only has
two fields (categoryoptioncomboid and categoryoptioncomboname). The fact
that the names are enclosed in parentheses also causes problems sometimes
when exporting data though CSVs, but currently the only thing we could
match on is the categoryoptioncomboname.

I'll try the new detailed metadata export features. Maybe that will help.

Randy

*From:* Bob Jolliffe [mailto:bobjolliffe@gmail.com]
*Sent:* Tuesday, February 18, 2014 8:58 PM
*To:* Wilson,Randy
*Cc:* dhis2-users@lists.launchpad.net
*Subject:* Re: [Dhis2-users] Issue importing CSV with categoryoptioncombos

Hi Randy

On 18 February 2014 13:06, Wilson,Randy <rwilson@msh.org> wrote:

I'm moving data between instances of DHIS-2 (from the HMIS to the Data
Warehouse with a subset of data elements).

Since the dataelementids and orgunitides are not always the same in the
two systems, I'm using the code fields for those. I've set up a query to
provide the data I need with one exception: if the categoryoptioncomboids
are not default, I need to find the id in the *destination* system.

Would it not be possible to use the categoryoptioncomboname during import,
since there is no code field for categoryoptioncombos...a file that is
autogenerated?

You have hit precisely the soft underbelly of the categoryoptioncombo
problem here (one of them anyway). Because it does not have a code it is
not easily matched. What you need is for the uid to be the same on both
sides. The name is not so reliable, not least because it is a bit
unpredictable how it is constructed (the order) from category names.

Your best bet might be to treat HMIS as your metadata registry for these
datasets (with associated datelements, categories, options and combos).
Then use the "Detailed Metadata Export" option to export the datasets and
finally check the "with dependencies" box when you see it. If you import
this into data warehouse you should get the same categoryoptioncombos on
both sides.

Slightly further down the line we are hoping to "fix" this problem in dxf2
by exporting the categoryoptions themselves rather than the
categoryoptioncombos, but this is a bit further down the line (and maybe
won't really work with csv).

Try the detailed metadata export and see how that works (it was actually
created with your use case in mind).

Bob

Here is a sample of the CSV: (I've added the categoryoptioncombo field to
illustrate the issue).

"dataelement";"period";"orgunit";"categoryoptioncomboid";"categoryoptioncombo";"value"

"8942";"201312";"573";"14";"(default)";"24"

"8942";"201312";"94";"14";"(default)";"34"

"8942";"201312";"433";"14";"(default)";"23"

"8942";"201312";"96";"14";"(default)";"30"

"8942";"201312";"266";"14";"(default)";"30"

"8942";"201312";"310";"14";"(default)";"20"

"8222";"201311";"384";"297";"(0-11 months)";"118"

"8222";"201311";"370";"297";"(0-11 months)";"15"

"8222";"201311";"155";"297";"(0-11 months)";"56"

"8222";"201311";"158";"297";"(0-11 months)";"46"

We move data across to the data warehouse each month, so I'd like to make
the process as simple as possible.

Does anyone have a suggestion?

*Randy Wilson*

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

Integrated Health Systems Strengthening Project

Management Sciences for Health, Inc. (MSH)

BP 371
Kigali, Rwanda
+250-788308835

Skype Name (wilsonrandy_us)

www.msh.org<http://www.msh.org/?utm_source=2012-10-15+MSH+internal+announce+email+signature&utm_campaign=iemailsig&utm_medium=email>

[image: Description:
http://ih.constantcontact.com/fs017/1102482301888/img/438.gif]<http://www.msh.org/?utm_source=2012-10-15+MSH+internal+announce+email+signature&utm_campaign=iemailsig&utm_medium=email>

*Stronger health systems. Greater health impact.*

[image: Description: Follow MSH on Facebook]<http://www.facebook.com/ManagementSciencesForHealth>[image:
Description: Follow MSH on Twitter]<https://twitter.com/#!/MSHHealthImpact>[image:
Description: YouTube] <http://www.youtube.com/mshhealthimpact>Follow us
on Facebook and Twitter

_______________________________________________
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