HPC GAPAnalysis API - UN-OCHA/hpc-api GitHub Wiki
This tool is a new HPC tool that will be very useful to OCHA and for cluster coordinators. It fills an important gap in the current HPC tools by creating a bridging dashboard between the RPM and the Projects Module. (Hence we call it the ‘bridge tool.’) It thus allows cluster coordinators to proactively guide the partners’ project development so as to best cover the priority activities, per geographical area and target population.
Cluster coordinators classically decide on their activities and targets based on the HNO (plus their estimate of cluster capacity), with geographical detail down to lowest admin level i.e. admin 3 at least, plus target populations therein (e.g. IDPs, host communities, returnees). They upload these activities and targets onto RPM. Users of the PM can then see the same information when they draft their projects—they choose a cluster-registered activity, see that activity’s overall target, and note their project target, with the option of geo and target population breakdown. The gap arose from the fact that cluster coordinators could not easily see what the draft projects were adding up to. For example, the Food Security cluster coordinator sets a target for the main food-assistance activity of 1 million people (X in this district, Y in that district, etc.). Food Security partners then get busy drafting their projects, including this activity. But during this drafting, or even after project finalization, the cluster coordinator could not see how many people the draft projects collectively proposed to reach – whether it was way under 1 million, way over, or about right. Neither could they see which districts had a surplus of planned outputs (according to the draft projects) vis-à-vis the original cluster target, and which districts had a deficiency. The cluster coordinators were thus ‘flying blind’ when trying to fulfil their obligation to keep the projects aligned with the priority needs.
The bridge tool now allows them to see all this, in an easy dashboard view. The main advantage is that it allows the cluster coordinator (supported by OCHA) to intervene in and ‘massage’ the project-development process to adjust those surpluses and deficiencies, before the projects become final – getting project owners to move some planned outputs from a surplus district (or target population) to a deficient one; getting them to cut, when the projects’ collective proposed outputs exceed the cluster target for that activity; or getting them to expand, if the projects’ targets collectively fall short.
This tool can also be useful for countries that practice activity-based planning, and only record projects on the PM post facto: as projects are funded and recorded, the bridge tool will show the cluster coordinator what are the totals per activity (and geo unit etc.) of the projects that are actually funded and implemented. With this info, cluster coordinators can guide other partners who are drafting projects and seeking funding, so that they fill gaps.
We encourage you to discuss this with your cluster coordinators ASAP, and hopefully use it soon.
Getting Started using the HPC GAPAnalysis API
The bridge tool can be used in a number of different ways:
- Using the API Directly
- Using the Excel Workbook
- Using our Dashboard Tool. Watch a video illustrating using the Bridge Tool in Nigeria context
Usage of the API and Excel Workbook are detailed in this document, but if you would like to use the dashboard, please contact [email protected] and we can help you set this tool up for you.
Using the API Directly
Request
API requests should be GET requests to the following URL.
https://api.hpc.tools/v2/projectGapAnalysis
With the following query parameters:
| Parameter | Required / Default Value | Details |
|---|---|---|
planId |
Required | The numeric ID of the plan you wish to get data for. |
includeDisaggregatedData |
Optional (default: false) |
Set to true if disaggregated data should be included |
includeConditionFields |
Optional (default: false) |
Set to true if condition fields answers should be included |
includeBudgetBreakdowns |
Optional (default: false) |
Set to true if per-project budget breakdowns should be included |
For example, requests could look like the following:
-
GET https://api.hpc.tools/v2/projectGapAnalysis?planId=1039 -
GET https://api.hpc.tools/v2/projectGapAnalysis?planId=1039&includeDisaggregatedData=true&includeConditionFields=true
Response
Responses will look something like this, the exact shape of the data will depend
on which parameters you have set to true above.
{
"data": {
// The list of all organizations relevant to the plan data
"organizations: [
{
"id": 11484,
"name": "Human Health Aid-Burundi",
"nativeName": null,
"abbreviation": "HHA Burundi",
"url": "http://humanhealthaid.com/"
},
// ...
],
// The list of all clusters / sectors for the plan
"governingEntities": [
{
"id": 6112,
"name": "Abris / Articles Non-Alimentaires",
"customRef": "CLSHL"
},
// ...
],
// The list of all global sectors for the plan
"globalClusters": [
{
"id": 14,
"code": "PRO-HLP",
"name": "Protection - Housing, Land and Property",
"type": "aor"
},
// ...
// The list of objectives / activities throughout the plan
"planEntities": [
// An example strategic objective
{
"id": 12491,
"type": "SO",
"customRef": "SO1",
"description": "...",
"supports": [],
"governingEntity": null
},
// An example specific objective, supporting the above strategic objective
{
"id": 12493,
"type": "SP",
"customRef": "SP1",
"description": "...",
"supports": [
12491
],
"governingEntity": null
},
/**
* An example cluster objective,
* supporting the above specific objective,
* and assigned to cluster/sector 6112 above
*/
{
"id": 12499,
"type": "CO",
"customRef": "CLPRO/CO1",
"description": "...",
"supports": [
12493
],
"governingEntity": 6112
},
// ...
],
/**
* If `includeConditionFields` is `true`,
* this property will be present,
* and will be a list of the condition fields used for projects of this plan.
*/
"conditionFields": [
{
"id": 2668,
"name": "1 - Veuillez spécifier le type de votre organisation",
"fieldType": "select"
},
// ...
],
"projects": [
{
// Always-Present Project Data
"id": 175415,
"name": "NFI assistance for IDPs",
"code": "HBDI21-SHL-175415-1",
"status": "accepted",
"budgetUSD": 448060,
// IDs of organizations (listed above) associated with this project
"organizations": [
11484
],
// IDs of clusters/sectors (listed above) associated with this project
"governingEntities": [
6112
],
// Possible Additional Data
/**
* If `includeConditionFields` is `true`,
* This property will be present, and be a list of values for each of
* the condition fields that this project has values defined for.
*
* It does not include entries for condition fields for which the
* project left blank.
*/
"conditionFields": [
{
"id": 2668,
"value": "National NGO"
},
// ...
],
/**
* If `includeBudgetBreakdowns` is `true`,
* then this property will be present,
* and contain a list of sections of the budget,
* split by organization + cluster / sector and global cluster i.e. Protection AoRs.
*
* `organization` and `governingEntity` will refer to the IDs of items
* from their respective lists above.
*/
"budgetBreakdown": [
{
"organization": 11484,
"governingEntity": 6112,
"globalCluster": 11,
"budgetUSD": 448060
}
]
},
// ...
],
"indicators": [
{
"id": 23221,
"customRef": "CLSHL/CA7/IN1",
"description": "Nombre de ménages ayant bénéficié de cash ou de matériaux de construction pour construire un abri semi-permanent",
/**
* Either the cluster or planEntity (objective / activity) under which
* this indicator belongs
*/
"parent": {
"type": "planEntity",
"id": 13018
},
/**
* The IDs planEntities (objective / activity) that this indicator
* directly or indirectly supports (e.g. by belonging to a specific
* entity directly, or by belonging to an entity that supports another
* entity, etc...)
*/
"supportsEntities": [
13018,
12532,
12493,
// ...
],
/**
* If this indicator belongs to a cluster / sector,
* a value will be defined here
*/
"governingEntity": 6112,
/**
* The overall target for this indicator for the plan,
* projects should be targeting against this value,
* which is presented to them when registering projects.
*/
"target": 15193,
/**
* A list of projects that are contributing towards this indicator,
* along with the individual project target values.
*/
"projectTargets": [
{
"id": 176634,
"target": 1000
},
{
"id": 176633,
"target": 3847
},
// ...
],
/**
* The SUM of the project targets for this indicator,
* disaggregated by project status.
*/
"projectTargetsByStatus": {
"accepted": {
"targetSum": 4847,
"projectCount": 2
}
},
/**
* Only present if `includeDisaggregatedData` is `true`,
* (Example and explanation provided below)
*/
"disaggregated": {
// ...
},
/**
* The unit for this indicator,
* either `null` or an object with an `id` and `label`.
*/
"unit": {
"id": 2,
"label": "Households"
}
},
// ...
],
"caseloads": [
/**
* The structure of this data is the same as for "indicators",
* except that items do not have a `unit` property.
*/
],
/**
* When `includeDisaggregatedData` is `true`,
* This list contains all locations that are directly referenced from
* disaggregated caseload or indicator data
*/
"locations": [
{
"id": 37,
"pcode": "BDI",
"name": "Burundi",
"adminLevel": 0
},
{
"id": 25811044,
"pcode": "BDI017",
"name": "Bujumbura Mairie",
"adminLevel": 1
},
// ...
]
}
}
Disaggregated Data
When the includeDisaggregatedData flag is set to true,
indicator and caseload items will include a disaggregated property when that
data is available.
The value of such property looks something like this:
{
/**
* Array of location IDs used for this disaggregation,
* `null` when the location can't be resolved
*/
"locations": [
null,
25811045,
25811046,
// ...
],
/**
* List of categories used for this disaggregation
*/
"categories": [
{
"ids": [
2241,
2260
],
"label": "PDI/IDPs - Filles/Girls"
},
{
"ids": [
2241,
2261
],
"label": "PDI/IDPs - Garçons/Boys"
},
// List of categories that are used
],
/**
* A 2D array of targets,
* where each row is associated with a location,
* and each column is associated with a category,
* in the same order as the arrays above.
*
* * There should be the same number of rows as locations
* * Each row will either be EMPTY, or have the same number of items as the
* `categories` array. (this is to reduce the response size)
* * Each "cell" can be 1 of 4 things, details listed below,
* but here is an illustrative example:
*/
data: [
/**
* This row is for `locations[0]` -> `null`
* As it is an empty array, all columns can be considered empty too
*/
[],
/**
* This row is for `locations[1]` -> `25811045`
*/
[
/**
* This column is for `categories[0]` -> `PDI/IDPs - Filles/Girls`,
*
* As it is empty, there are neither plan nor project targets for it.
*/
[],
/**
* This column is for `categories[1]` -> `PDI/IDPs - Garçons/Boys`,
*
* As it only has 1 item, it has a plan target,
* but no projects are contributing to it.
*/
[1234],
],
/**
* This row is for `locations[2]` -> `25811046`
*/
[
/**
* This column is for `categories[0]` -> `PDI/IDPs - Filles/Girls`,
*
* The first value is the plan target,
* the second value is an array of targets for projects that are
* contributing to it.
*/
[
1234,
[
{
"id": 123456,
"target": 432
},
// ...
]
],
/**
* This column is for `categories[1]` -> `PDI/IDPs - Garçons/Boys`,
*
* As it has null for its first item,
* there is no plan target for this location + category,
* but there are project targets specified.
*/
[
null,
[
{
"id": 123456,
"target": 432
},
// ...
]
],
]
]
}
Each "cell" of the 2D array above will be in one of 4 forms:
-
If there is no plan target for a cell, and no projects with a target either, it will be the empty array:
[] -
If there is a plan target for a cell, but no project targets, then it will be an array with 1 item, which will be the plan target:
[1234] -
If there is both a plan target, and 1 or more projects that contribute to this target for the given cell, then the cell will be an array with 2 items:
- The first item is the plan target
- The second item is an array of project targets, detailing each project's ID, along with their targets
[ 1234, [ { "id": 123456, "target": 432 }, // ... ] ], -
If there is no plan target, and 1 or more projects that contribute to this target for the given cell, then the cell will be an array with 2 items:
- The first item will be
null - The second item is an array of project targets, detailing each project's ID, along with their targets
[ null, [ { "id": 123456, "target": 432 }, // ... ] ], - The first item will be
Using the Excel Workbook
We have created an Excel workbook that can be easily configured to automatically download and present gap-analysis data for your plan in a useful manner.
PowerQuery is used throughout the workbook to download, and re-organize the data provided by the API into useful tables / sheets.
Download the Excel Workbook and Power BI relationship queries
You can download the Excel file here: v2.1_RPM_PM_Bridge_Tool_Template.xlsx
You can download the Power BI file here: v2.1_RPM_PM_Bridge_Tool_Template.pbix
What's inside the Excel Workbook (same queries exists in the PBI and proposed relationships between the queries)
The workbook presents the following sheets:
Caseload Data:
-
1.1 - CaseloadData:
All caseloads targets for all entities including plan, clusters, strategic objectives, specific objectives, cluster objectives and cluster activities from RPM, & overall caseload targets, and projects target (total and by projects status), count of projects (total and by status) from PM against these caseloads.
-
1.2 - DisaggregatedCaseloadData:
All caseloads targets for all entities including plan, clusters, strategic objectives, specific objectives, cluster objectives and cluster activities from RPM, & overall caseload targets, and projects target (total and by projects status), count of projects (total and by status) disaggregated by category and locations from PM against these caseloads.
-
1.3 - CaseloadProjectBreakdown:
All caseloads targets for all entities including plan, clusters, strategic objectives, specific objectives, cluster objectives and cluster activities from RPM, & caseload targets, and projects target (including project id, project status, budget, organizations) from PM against these caseloads.
-
1.4 - DisaggCaseloadProjects:
All caseloads targets for all entities including plan, clusters, strategic objectives, specific objectives, cluster objectives and cluster activities from RPM, & caseload targets by project (including project id, project status, budget, organizations) disaggregated by category and locations from PM against these caseloads.
Indicator Data:
-
2.1 - IndicatorData:
All indicators targets for all entities including plan, clusters, strategic objectives, specific objectives, cluster objectives and cluster activities from RPM, & overall indicator target for all projects (total and by projects status), count of projects (total and by status) from PM against these indicators.
-
2.2 - DisaggregatedIndicatorData:
All indicators targets for all entities including plan, clusters, strategic objectives, specific objectives, cluster objectives and cluster activities from RPM, & overall indicators targets for all projects (total and by projects status), count of projects (total and by status) disaggregated by category and locations from PM against these indicators.
-
2.3 - IndicatorProjectBreakdown:
All indicators targets for all entities including plan, clusters, strategic objectives, specific objectives, cluster objectives and cluster activities from RPM, & indicators targets and projects target (including project id, project status, budget, organizations) from PM against these indicators.
-
2.4 - DisaggIndicatorProjects:
All indicators targets for all entities including plan, clusters, strategic objectives, specific objectives, cluster objectives and cluster activities from RPM, & disaggregated indicators targets by project (including project id, project status, budget, organizations) by category and locations from PM against these indicators.
Project Data:
-
3.1 - Projects:
All projects that have been submitted against the plan, including project ID, code and name, clusters (and per-cluster budgets), organizations (and per-organization budgets), and all condition field values. condition fields are custom questions answered for each projects such as Gender mainstreaming, CASH ..etc.
Configuring and Using the Excel Workbook
This workbook needs to be configured, and will get all of the latest data from RPM and Project Module. It contains data that has not yet been published, and as such you need to authenticate using HID.
Configuration:
-
Go to Sheet "0.1 - Configuration" and enter the ID of the plan
-
(Optional) If you would like to include a "Severity" alongside location information to help with data analysis, you may add this information to the sheet "0.2 - LocationSeverity".
You can add / modify this data at any point in the future, and it will be used the next time you Refresh data (see below)
-
(Optional) If you would like to include a "Priority" alongside indicator information to help with data analysis, you may add this information to the sheet "0.3 - IndicatorPriority".
You can add / modify this data at any point in the future, and it will be used the next time you Refresh data (see below)
-
Save your changes!
First Load / Refresh:
The first time you load data, you will be asked how you want to authenticate with the API. Given that this API includes unpublished data, you will need to log-in as yourself using HID.
-
Generate a HID Token using the instructions here: https://github.com/UN-OCHA/hpc-api/wiki/Authentication#generating-long-term-hid-tokens
-
Trigger a refresh by going to "Data" (1) and clicking "Refresh All" (2):
-
The data refresh will be started. You will be presented with a popup asking you how you would like to authenticate. Choose "Basic" (1), enter the username "
hid" (2), and enter the token you generated in step 1 into the password field (3):
-
At the bottom-left of your screen, you will see that the data is currently refreshing:
-
After a short period of time, this should complete, and your data should be available across your sheets.
Refreshing Data:
You can refresh your data as often as you like by following step 2 above. If you change the configuration (i.e. the PlanID, LocationSeverity Table, or IndicatorPriority Table), then you will need to refresh for that configuration to take effect.
Troubleshooting Privacy / Firewall Warnings:
You may be presented with a warning about "AllData referencing other queries or steps ...". If this is the case, then you must reconfigure some of your privacy settings to allow you to use the queries that generate the data in this workbook. This is because we combine data from this workbook (your configuration) with data from our API, be careful when doing this if you have modified any of the PowerQuery queries.
-
Go to "Data" (1) -> "Get Data" (2) -> "Query Options" (3).
-
Go to "Global" -> "Privacy" (1), and select "Combine data according to each file's Privacy Level settings" (2).
-
Go to "Current Workbook" -> "Privacy" (1), and select "Ignore the Privacy Levels and potentially improve performance" (2). Then click "Ok" (3).
-
Attempt to Refresh the data again, and it should now work.