Strategies for publication of data in Excel

[Brian Lewis]

We have a combination of tools (DPT) and practices for publishing data into Excel tables and pivot tables - this forms the basis for the construction of documents such as Annual Stats Digest etc.

These connection are direct connections to SQL Server using Excel’s OLEDB capacity. It is possible to establish SQL connections across the WAN - but this entails some careful security setup to allows users through a firewall to reach the SQL Server.

We would like to be able to support users who do not have access to SQL ( either across their LAN, or across the WAN) to access data in Excel. This would allow more options for remote hosting, and also for a broader set of users to have access to Excel.

Proposing to add the following options that would allow Excel tables/pivot tables to be created from a connection to the REST API:

‌

1. Power Query data sources
2. Dynamic, static workbooks returned from the API

‌

Power Query

Power query extends Excel’s ability to access data from relational sources with the ability to extract, transform and load data from a wide variety of sources including http rest end points.

‌

Dynamic Workbooks

Currently this design is prototyped:

<rest endpoint>?xl

Add the xl option to the query string. This will return the data from the rest endpoint in Excel, as a table and pivot table.

<rest endpoint>?”xl=<file name>.xlsx

If a filename including extension is supplied, the returned Excel has this exact name.

<rest endpoint>?”xl=<file token>

A name is created from the token, in this form:

<context> <file token> <yyyy-mm-dd>.xlsx

‌

This table shows the comparative strength and weaknesses of the 3 approaches:

Mode	Connection	Refreshable?	Flexibility	Distribution
SQL/ DPT	SQL connection	Yes	Any Sql query.	Typically built by the end user using DPT
Power Query	Web portal connection	Yes	Restricted to REST endpoints.	Curated workbooks would be distributed to relevant users
Dynamic	Web portal connection	No	Restricted to REST endpoints. Since this cannot be refreshed, you would not build a lot of additional material into such a workbook.	Can be built into web interface - dynamically created on request

‌

‌

[Ghislain Hachey]

This useful information has been moved to the Pacific EMIS User Guide at https://docs.pacific-emis.org/doku.php?id=emis_user_manual#professional_data_publications managing the actual work on this will need more specific issues.