First steps in pulling in product data from PCMT

Technical Committee
, ,
#1

Hello technical committee,

I wanted to kick-off a discussion on how we can most-rapidly pull in product data from PCMT to OpenLMIS’ Reference Data.

A starter user story

As an administrator I want to configure the address, credentials and product family of a PCMT instance so that a regularly occurring pull of products from the given family occurs and populates the OpenLMIS product list so that OpenLMIS product master data may be sourced from a product catalog.

PCMT API

PCMT has a REST API returning JSON (HATEOS) that has searching and paging capabilities built-in. Security is handled by OAuth2 using a simple web Bearer token, however we’ll need to support the refresh token workflow. We’ll also be able to find products that have changed since the last time the list has been polled by using a last-updated date-time. I’ll post more on the details of the API later as we get closer to a decision.

OpenLMIS’ Work

At the very least an OpenLMIS team would be responsible for:

  • configuring how and when a component would pull data from PCMT.
  • regularly pulling that data into Reference Data, and further configuring OpenLMIS’ Products. e.g. PCMT likely wouldn’t have a Program Orderable configuration as that’s OpenLMIS specific. So after a Product is pulled in from PCMT, someone in OpenLMIS would likely still want to configure that product/orderable to Programs.

To carry out this plumbing a number of options are apparent, however I’d like the team to weigh in on which could be accomplished soonest, and which we have the most confidence in right-now:

  1. Nifi. It’s still our go-to data-mover in the reporting stack and we know it’s robust at moving data, however I think a number of us are unhappy at how complex managing it’s deployment is.
  2. Build it in Java. Either in Reference Data or as another micro-service. We might be most comfortable with this coding and deployment-wise, however we’d be starting from scratch: scheduling, state storage between pulls, etc.
  3. Build a Mediator in OpenHIM. This would likely be most favorable longer-term and to the OpenHIE community, however our team hasn’t deployed OpenHIM yet nor built a Mediator. You can install via Docker and run locally pretty easily to explore.

Next steps

Longer-term we are likely to see steps made to standardize product master data in FHIR and to build re-usable mediators to more systems. In the meantime however with what’s happening around the world with COVID, there is a desire to make this work quickly and reliably. Personally I think option 3 will be useful for the team longer term, however I’d like to hear what the team thinks in terms of getting something done and potentially learning something new.

Best,
Josh

#2

Hi Josh,

sorry for the late response.

Thanks for posting the user story and little bit of background. As for the proposed solutions, I’ve discussed them with the team, and probably building it in Java would be the most intuitive for us because of using well-known technology. We also have an idea that complements the mentioned one. What about using refdata-seed tool? We could get data using REST API, process it somehow to CSV files and run seed tool to pull that data into OpenLMIS. So, the missing parts would be scheduling and processing data to CSV. What do you think?

Best,
Klaudia

#3

Hi Klaudia and Josh,

I think we can try adapt existing service created for DHIS2 integration in Malawi. It’s workable ETL service with scheduler, so this part is done already. Adding Export to CSV shouldn’t be difficult.

So I think we can create new microservice based on existing DHIS2 Integration service.

Best,
Jakub

#4

Thanks @Klaudia_Palkowska and @JakubJakubowski,

This makes sense. I think I know, but just to be clear are you thinking we’ll build it as a new service using Java, or as a part of the Reference Data service?

The response from PCMT will be JSON, and Reference Data seed tool converts CSV’s into JSON for Reference Data service’s API. If this is an external service, I like the idea of using the API, however the JSON->CSV->JSON flow is a bit round-about. I’m not against it, however I think you could convert the JSON from the PCMT API to the JSON needed for the Reference Data API pretty simply. Perhaps even just a simple jq script.

Is this a service that runs in OpenLMIS through docker? Or was this done with Nifi?

Lets also remember we’ll want someway to observe the connection: from failed credentials through to not understood messages (e.g. incorrect unit of measure). Eventually we’ll want more and more of this observable in the UI, however for now we’ll likely be able to keep this minimal. One thing however we should keep in mind though is that early on we should provide a way for admins to see in the UI: when the last update occurred and possibly someway to trigger an update manually.

#5

Hi @joshzamor ,

We decided to use Java and adapt existing DHIS2 Integration service from Malawi. That service runs in OLMIS through docker. Right now it works like this: 1.create JSON payload data from OLMIS -> 2.Start execution manually or by scheduler -> 3.send payload to API external endpoint. Also there is UI in OLMIS on which we can start manually or set scheduler. There is a table where are stored responses status and response body from external endpoint - so we can adapt it to.

Probably we should develop minimal workable service ASAP and later add extra stuff like scheduler.

So we have something, but need to add some updates to above steps. In first step we need to call to PCMT endpoint and parse response JSON to JSON which we will send to our service. About second step, right now we should stay focused on manual execution, scheduler will be easy to adapt later. In third step we need to change destination of calls to our service and make sure that it will be compatible.
In new UI right now we should stay focused to have ability to start execution manually and store response in a table.

Firstly we need every useful information/docs about PCMT endpoint which we will call. Like JSON schema, authorization, statuses, URI etc.

Best regards,
Jakub

#6

Hi @JakubJakubowski,

This sounds like a start if the team thinks they can have it done in the next sprint: pulling products from PCMT into OpenLMIS.

Lets start with PCMT is an extension to Akeneo, and the Akeneo API is the place to start. While that page has links to a Swagger spec, I find the most useful resources are to read the API Reference and then go get the Postman collection and environment files to start playing with the API.

To start experimenting you’ll need an environment to work from, here are some good options:

For any of these environments, to connect to them you’ll need to create an API credential in the system (not the username and password for admin above). Follow the Akeneo guide for how to get a client id and secret from the web-interface.

Once you’ve got Postman connecting by performing the auth flow, the most interesting endpoint is the get Product Models, which will give you something that looks like this:

{
    "_links": {
        "self": {
            "href": "http://demo.productcatalog.io/api/rest/v1/product-models?page=1&with_count=false&pagination_type=page&limit=10"
        },
        "first": {
            "href": "http://demo.productcatalog.io/api/rest/v1/product-models?page=1&with_count=false&pagination_type=page&limit=10"
        },
        "next": {
            "href": "http://demo.productcatalog.io/api/rest/v1/product-models?page=2&with_count=false&pagination_type=page&limit=10"
        }
    },
    "current_page": 1,
    "_embedded": {
        "items": [
            {
                "_links": {
                    "self": {
                        "href": "http://demo.productcatalog.io/api/rest/v1/product-models/1000"
                    }
                },
                "code": "1000",
                "family": "RH_PRODUCTS_TRADEITEMS_VARIANTS",
                "family_variant": "VARIANT_RH_SOURCE_ID",
                "parent": null,
                "categories": [
                    "10000463",
                    "L5_IMP_ETO",
                    "PRODUCTS_TO_TRADE_ITEMS"
                ],
                "values": {
                    "L3": [
                        {
                            "locale": null,
                            "scope": null,
                            "data": "Implantable_Contraceptives"
                        }
                    ],
                    "ROUTE_OF_ADMINISTRATION": [
                        {
                            "locale": null,
                            "scope": null,
                            "data": "Parenteral_Slow_Release"
                        }
                    ],
                    "price_reference": [
                        {
                            "locale": null,
                            "scope": null,
                            "data": [
                                {
                                    "amount": "8.50",
                                    "currency": "USD"
                                }
                            ]
                        }
                    ],
                    "BASE_UOM": [
                        {
                            "locale": null,
                            "scope": null,
                            "data": "Set"
                        }
                    ],
                    "GPC_CODE": [
                        {
                            "locale": null,
                            "scope": null,
                            "data": "10000463"
                        }
                    ],
                    "PARENTID": [
                        {
                            "locale": null,
                            "scope": null,
                            "data": "1000"
                        }
                    ],
                    "STRENGTH": [
                        {
                            "locale": null,
                            "scope": null,
                            "data": "68 mg/rod"
                        }
                    ],
                    "FREQUENCY": [
                        {
                            "locale": null,
                            "scope": null,
                            "data": "Every 3 years"
                        }
                    ],
                    "UNSPSC_CODE": [
                        {
                            "locale": null,
                            "scope": null,
                            "data": "51352005"
                        }
                    ],
                    "ACTIVE_INGREDIENT": [
                        {
                            "locale": null,
                            "scope": null,
                            "data": "Etonogestrel"
                        }
                    ],
                    "PRODUCT_DESCRIPTION": [
                        {
                            "locale": null,
                            "scope": null,
                            "data": "Etonogestrel 68 mg/rod, 1 Rod Implant"
                        }
                    ]
                },
                "created": "2020-04-13T19:25:52+00:00",
                "updated": "2020-04-13T19:25:52+00:00",
                "associations": {
                    "SUBSTITUTION": {
                        "products": [],
                        "product_models": [],
                        "groups": []
                    }
                }
            },
           ...

Note the format:

  • Page links - The client will need to use paging.
  • Resource links for Self
  • The client will need to keep track of the last data-time it requested the product list to do incremental updates (more on that tomorrow)
  • That attributes and their values are lists of objects - each attribute may have many values depending on locale, scope (aka channel). More on this tomorrow.

Lets start with that and let me know if you get through to making successful requests. Tomorrow I’ll post more about the format of Products, Product Models, and which items are important as opposed to non-essential.

#7

Hi @joshzamor,

Thanks for update. I’m able to make successful request on Postman. Also I started to code service to calls this endpoint and I’m able to do that. I understand that we will be calling this endpoint, right? So I can create Model Objects based on this response?

Regards,
Jakub

#8

Hi @JakubJakubowski,

Right, from PCMT’s perspective a Product Model is equivalent to OpenLMIS’ Commodity Orderable concept. With a caveat: we should ignore any Product Model where parent is not null.

I didn’t get all the way done with updating one of the demo sites with OpenLMIS attribute names, partly because it looks like the API docs aren’t complete? In particular the different Dispensibles (DefaultDispensable, ContainerDispensable, VaccineDispensable) seem to be missing from the json schema.

Do we need to update the api docs? @Sebastian_Brudzinski would the csv/excel upload for the ref-data-seed-tool have this documented (and if so could you attach an empty one)?

#9

Hi @JakubJakubowski,

As I’ve modeled more of the catalog’s concerns, I have a few changes:

  • Get Products, not Product Models. The API level changes are minimal, but you’ll need it based of how I’ve changed modeling products.
  • Only return those in the LMIS category. e.g. {{url}}/api/rest/v1/products?search={"categories":[{"operator":"IN","value":["LMIS"]}]}

And then I have some field mappings:

PCMT OpenLMIS
LMIS_UUID id (thought this would be useful for updates)
LMIS_CODE productCode
PRODUCT_DESCRIPTION fullProductName
PRODUCT_DESCRIPTION description (this isn’t really used)
UOM_QTY_FACTOR netContent
LMIS_PACK_ROUNDING_THRESHOLD packRoundingThreshold (todo)
LMIS_ROUND_TO_ZERO roundToZero (todo)

A sample from demo.productcatalog.io:

{
    "_links": {
        "self": {
            "href": "http://demo.productcatalog.io/api/rest/v1/products?page=1&with_count=false&pagination_type=page&limit=10&search=%7B%22categories%22%3A%5B%7B%22operator%22%3A%22IN%22%2C%22value%22%3A%5B%22LMIS%22%5D%7D%5D%7D"
        },
        "first": {
            "href": "http://demo.productcatalog.io/api/rest/v1/products?page=1&with_count=false&pagination_type=page&limit=10&search=%7B%22categories%22%3A%5B%7B%22operator%22%3A%22IN%22%2C%22value%22%3A%5B%22LMIS%22%5D%7D%5D%7D"
        }
    },
    "current_page": 1,
    "_embedded": {
        "items": [
            {
                "_links": {
                    "self": {
                        "href": "http://demo.productcatalog.io/api/rest/v1/products/MASK_N95_MEDIUM_PACK10"
                    }
                },
                "identifier": "MASK_N95_MEDIUM_PACK10",
                "enabled": true,
                "family": "PPE",
                "categories": [
                    "LMIS",
                    "PPE",
                    "PRODUCTS_TO_TRADE_ITEMS"
                ],
                "groups": [],
                "parent": "MASK_N95_MEDIUM",
                "values": {
                    "PACK_SIZE": [
                        {
                            "locale": null,
                            "scope": null,
                            "data": "pack_10"
                        }
                    ],
                    "price_reference": [
                        {
                            "locale": null,
                            "scope": null,
                            "data": [
                                {
                                    "amount": "10.00",
                                    "currency": "USD"
                                }
                            ]
                        }
                    ],
                    "BASE_UOM": [
                        {
                            "locale": null,
                            "scope": null,
                            "data": "Piece"
                        }
                    ],
                    "LMIS_CODE": [
                        {
                            "locale": null,
                            "scope": null,
                            "data": "PPE100"
                        }
                    ],
                    "LMIS_UUID": [
                        {
                            "locale": null,
                            "scope": null,
                            "data": "f430dbee-885f-4363-9dc6-8f5804361613"
                        }
                    ],
                    "PRODUCT_DESCRIPTION": [
                        {
                            "locale": null,
                            "scope": null,
                            "data": "Mask, Respirator, N95/FFP2, Size Medium"
                        }
                    ],
                    "UOM_QTY_FACTOR": [
                        {
                            "locale": null,
                            "scope": null,
                            "data": "10.0000"
                        }
                    ]
                },
                "created": "2020-04-22T05:22:26+00:00",
                "updated": "2020-04-22T05:24:22+00:00",
                "associations": {
                    "SUBSTITUTION": {
                        "products": [],
                        "product_models": [],
                        "groups": []
                    }
                }
            },
            {
                "_links": {
                    "self": {
                        "href": "http://demo.productcatalog.io/api/rest/v1/products/MASK_N95_MEDIUM_PACK20"
                    }
                },
                "identifier": "MASK_N95_MEDIUM_PACK20",
                "enabled": true,
                "family": "PPE",
                "categories": [
                    "LMIS",
                    "PPE",
                    "PRODUCTS_TO_TRADE_ITEMS"
                ],
                "groups": [],
                "parent": "MASK_N95_MEDIUM",
                "values": {
                    "PACK_SIZE": [
                        {
                            "locale": null,
                            "scope": null,
                            "data": "pack_20"
                        }
                    ],
                    "price_reference": [
                        {
                            "locale": null,
                            "scope": null,
                            "data": [
                                {
                                    "amount": "15.00",
                                    "currency": "USD"
                                }
                            ]
                        }
                    ],
                    "BASE_UOM": [
                        {
                            "locale": null,
                            "scope": null,
                            "data": "Piece"
                        }
                    ],
                    "LMIS_CODE": [
                        {
                            "locale": null,
                            "scope": null,
                            "data": "PPE101"
                        }
                    ],
                    "LMIS_UUID": [
                        {
                            "locale": null,
                            "scope": null,
                            "data": "4444b237-af62-4d0d-ad6a-a1614c6d0127"
                        }
                    ],
                    "PRODUCT_DESCRIPTION": [
                        {
                            "locale": null,
                            "scope": null,
                            "data": "Mask, Respirator, N95/FFP2, Size Medium"
                        }
                    ],
                    "UOM_QTY_FACTOR": [
                        {
                            "locale": null,
                            "scope": null,
                            "data": "20.0000"
                        }
                    ]
                },
                "created": "2020-04-22T05:24:25+00:00",
                "updated": "2020-04-22T05:25:25+00:00",
                "associations": {
                    "SUBSTITUTION": {
                        "products": [],
                        "product_models": [],
                        "groups": []
                    }
                }
            }
        ]
    }
}

Notes

  • As you can tell, this is a work in progress, expect things to change so stay flexible.
  • Field names are likely to change.
  • If we use a Product endpoint or a Product Model endpoint is still up in the air for the mid-term. For the short-term I think we’ll go with Products for now. I’ve spent a lot of time playing around with the structure in Akeneo and for now I think this is our path forward.
  • I’m still working on expanding the fields (todo’s above), as well as the items in there.

I’d love to hear how it’s going on your end and anything that’s of concern.

Best,
Josh

#10

Hi @joshzamor,

Firstly, we are not sure if we can use LMIS_UUID since the PCMT will support many OLMIS instances. In the case of updating, we could search by LMIS_CODE instead and get an identifier. Please let us know if we misinterpreted something.

Secondly, we lost LMIS data on the demo.productcatalog.io, so we started testing on test.pcmt.villagereach.org but the server was also redeployed. Was it intentional? Which one should we use to make sure that the LMIS catalog and all required attributes are available?

Luckily, we managed to test the request to OpenLMIS with mocked values of packRoundingThreshold and roundToZero. There is one more field required - dispensable (Could we map some attribute from PCMT to this?). When the value of dispensable is also mocked, we are getting HTTP 200.

Best,
Klaudia

#11

The thought was if the OpenLMIS API let you set the UUID from the value defined in PCMT on first creation, you could use it for every update after, avoiding the code lookup step. The API docs look like it lets you do this, however if it’s not needed I can remove. If it’s not possible because we’re using this “to CSV” approach however with the refdata-seed-tool, then I want to know that now so we can plan for the future after the proof of concept. Please let me know if we can’t use it because the API documentation is misleading or because we have this other tool in the middle.

Those are public instances. I would have expected one of those instances to reset shortly after though. We’ll move to another instance which I’ll send to you in Slack.

I asked earlier about the valid values for dispensable but didn’t hear back. We can certainly add it to PCMT, however our OpenLMIS API documentation doesn’t cover it - what are the valid values for dispensable? Also it’s marked as not-required in the UI. I think we should create a backlog item in OpenLMIS to update the docs on this.

Thanks @Klaudia_Palkowska and team, I’m excited to see this getting closer!

#12

Thank you @joshzamor for the explanation. We checked this and creating orderable with predefined UUID works properly so we will do it.

As you mentioned before, there are three types of Dispensable. I guess we could use DefaultDispensable which has one required field - dispensingUnit. Valid values for dispensing units are i.e. 10 tap strip, each, etc.

It’s not marked as required on the UI because we support only default dispensable and display its property - dispensingUnit. When the existing orderable (from demo-data or added using API) is related to non-default dispensable, it requires a different property but dispensingUnit field is still displayed and it should be possible to leave its value as null. We have a ticket to add support for different types of dispensable on the UI.

Best,
Klaudia

#13

In other words - this is free text, right?

Best,
Sebastian

#14

@Sebastian_Brudzinski Yes, that’s right.

#15

Hi @Klaudia_Palkowska, after the technical committee today where we said we needed to add dispensingUnit to PCMT, I realized that in-fact it’s already there as the field BASE_UOM. That makes our mapping table:

PCMT OpenLMIS
LMIS_UUID id (thought this would be useful for updates)
LMIS_CODE productCode
PRODUCT_DESCRIPTION fullProductName
PRODUCT_DESCRIPTION description (this isn’t really used)
UOM_QTY_FACTOR netContent
LMIS_PACK_ROUNDING_THRESHOLD packRoundingThreshold
LMIS_ROUND_TO_ZERO roundToZero
BASE_UOM dispensingUnit

Are we complete?