Rest endpoints for SDG indicators as Excel?

[Brian Lewis]

As per email discussion:

‌

There are a bit more SDG indicators then those requested in the UIS questionnaire and having all data export end points referred to as something like api/warehouse/sdg/$DATANAME$ I think makes more sense (e.g. api/warehouse/sdg/411 for SDG 4.1.1.). Then a UIS end point (e.g. ^/uis) for the actual generation of the UIS questionnaire which would make use of a collection of SDG endpoints. That way we can also make use of the individual SDG end points to get the data in other places (and not just the UIS questionnaire). Or am I missing something about how you are going with this?

Ideally, I would be able to /api/warehouse/sdg/411?xl for every SDG indicator that we can currently produce. And use those workbooks with some styling modifications to send to the MS word Digest.

[Brian Lewis]

The UIS workbook is now generated on the server, rather than on the client. We no longer need the legacy stand-alone tool, (and we don't need to adapt that tool to make it work again with current export requirements).

The server processing uses epplus version 4 to manipulate the Excel document using a predeployed template. Note that the approach used in the legacy tool - cross process COM Automation of Excel - is strongly discouraged in server applications; see https://support.microsoft.com/en-us/topic/considerations-for-server-side-automation-of-office-48bcfe93-8a89-47f1-0bce-017433ad79e2

‌

Generation of the uis Survey is invoked through a single rest endpoint:

[GET] api/uis/<year>

This endpoint returns the Excel workbook, which is downloaded by the browser.

The handler for this endpoint is in UisController.cs. In here is the logic to create the workbook. In particular, it sources the data from the database using a new class DsUisSurvey.

This DataLayer class has a method for each sheet of the workbook. In each case the method returns a dataset specifically designed to the needs of its corresponding sheet. (supplying numbers and qualifiers). When Uis Controller has this recordset, it picks out values and puts them in the sheet ( using range names for guidance rather than hardcoding cell references as in the legacy tool).

These methods of DsUisSurvey are named UisA1, UisA2 etc. Each invokes a stored procedure warehouse.uisA1 warehouse.uisA2 etc. These stored procs are where the heavy lifting is done.

Note then that to produce the Uis Survey UisController accesses the data it needs by the usual method ie going via the injected DataFactory to get to DsUisSurvey. So, this all works without it being necessary to expose these recordsets UisA1, uisA2 etc to the client via the REST API.

That’s not to say that we couldn't do that - it would be a simple enough matter to add new endpoints to UisController to return these raw datasets. But the data produced by these stored procs - ie the data needed to fill the Uis Survey - is quantitative data, not indicators ( ie ratios).

In summary ,I think while creating endpoints like api/warehouse/sdg/4.1.1?xl is a great idea, it is quite a different piece of work from the structure described above for generating the Uis survey.

[Brian Lewis]

PPS: In some ways , this goes back to very early ideas in the Pineapples desktop app where a suite of “analysis workbooks” was created, each reporting on a particular EFA indicator.

‌

[Ghislain Hachey]

@Brian Lewis Agreed this is a different albeit related piece of work. We can discuss this on our next call.

[Ghislain Hachey]

This work will not go ahead exactly as is. It was replaced by extracting SDG indicators directly from Excel using the classic data connection approach.