Refreshable Excel data spreadsheets

Overview

Refreshable data spreadsheets can be created using Excel's standard data management features with the data being retrieved directly from Fusion Registry's CSV REST web services.

Principles

Excel has built-in functionality for loading data from a range of sources including the Web which allows it to load data directly from Fusion Registry's REST API. The REST API can provide data in a number of different formats including XML and JSON, but CSV is the best choice being easily interpreted and processed by Excel.
A simple refreshable sheet can be created with an Excel Web data query and a Fusion Registry REST API data query URL.
Example https://demo.metadatatechnology.com/FusionRegistry/ws/public/sdmxapi/rest/data/WB,GCI,1.0/GHA.GCI..?format=csv

Basic Steps

1. Open a new Excel workbook and choose the Data menu.
2. From the ribbon bar choose: New Query > From Other Sources > From Web
3. Enter the Fusion Registry REST URL for the data you are interested in

Excel should connect to the URL and download the data into the worksheet which can be saved and used as normal. The data can be refreshed as needed using the Refresh All on the Data ribbon bar. The Show Queries option, also on the Data ribbon bar will open the query sidebar with more information.

Generating the data URLs

Fusion Registry's REST API follows the SDMX REST API syntax, with some additional parameters and CSV extensions which are useful here.

URLs can be crafted by hand if you understand the SDMX REST syntax and know what datasets you need. However, there are a couple of other ways to discover data and generate the URLs automatically.

Fusion Data Browser

1. Launch the Fusion Data Browser
2. Choose a data set and use the quick filters to select the data of interest.
3. Choose Export to CSV, but rather than exporting the file use the Query Syntax function to get the URL

The URl for the query can be copied and pasted into Excel.

Fusion Registry Web Services Query Builder

1. From the Fusion Registry user interface, choose Web Service > Data from the left-hand menu bar
2. Choose the Dataflow and the sub-cube using the query builder
3. Choose the CSV data format

The URL for the query can be copied and pasted into Excel.

Private services and authentication

Excel will need to authenticate with the REST API if the Registry is operating in private mode or Content Security rules restrict access to certain data.

In private mode, Excel will detect authentication is required and will automatically prompt for a username and password. But in other cases, you may need to explicitly set the credentials that Excel will use when accessing the API.

1. In Excel, after entering the URL, choose the Transform Data option on the data preview window - this will show the Power Query Editor
2. On the Power Query ribbon bar, choose the Data Source Setting option
3. Choose the new data source from the list and select the Edit Permissions option
4. Under Credentials choose Edit...
5. Choose Basic and enter the User name and Password