Quandl connector for SAP Lumira

Quandl.com is a data platform that provides access to millions of financial, economic, and demographic time series datasets online. Both free and premium datasets are exposed through an API and can be consumed data directly in various tools.

We’ve built the Quandl connector to conveniently access and import these datasets through an intuitive interface and also search for these datasets within SAP Lumira itself.

The Quandl Connector Github repository has the source code as well as the zip file to install the extension. You can download the latest build of the extension from here and install it using the Extension Manager in SAP Lumira. In this blog, we’ll go through the steps to access a dataset from Quandl and import it into Lumira.

Let’s open the Quandl Database Browser to look at the various databases offered by Quandl. We are particularly interested in importing the The Economist Big Mac Index into SAP Lumira. This index tracks the price of the Big Mac burger offered at McDonalds in various countries. In the database browser, Select Index Data > United States > Economic Indexes > The Economist – Big Mac Index to open the database and look at the underlying datasets.

Search for “United States” to find the dataset we are interested in. Please make a note of the dataset code “ECONOMIST/BIGMAC_USA” that we’ll use in the Quandl Connector for Lumira.

Open SAP Lumira Desktop which has the Quandl connector installed to import this dataset.

Select Next to open the Quandl Connector. You’ll now see a dialog box that you can use to enter or change the various parameters.

The parameters that you can change are,

Dataset Name: Enter a name for the new dataset being created in Lumira

Quandl Code: Enter any valid code assigned to datasets in Quandl.

All Data or Selected Date Range: Imports the entire dataset or a subset within the specified dates.

Start and End Date are used to select the date range. Ensure the dataset has values in this date range or you’ll get an error.

Sorting Order: Import the dataset in ascending or descending order of dates. Defaults to descending.

Limit Rows: Limit the number of rows of data being imported. Use a valid number or leave it empty if you don’t want to restrict.

End of period: Use this option to select a period and retrieve values at the end of every week, month, quarter or year. Select None and use aggregation functions inside Lumira if you are interested in Average or Sum of values over the same period of time.

Transform: Transform the dataset being imported using functions supported by Quandl. Please refer to the Quandl documentation for more details.

Quandl API Key: Enter the API key of your Quandl account to access the premium datasets you have subscribed to. It is also a good idea to register for an account and enter an API key to avoid the rate-limiting Quandl might apply while accessing free datasets.

After changing the parameters above you can now select Create to acquire the Big Mac Index for United States into Lumira.

The imported dataset will now be available in the Prepare room.

The date dimension is automatically assigned a Date type. You can create a time hierarchy by selecting the Create date/time hierarchy option on the date dimension. All numbers are assigned as Measures with the aggregation function set to None. Lets change the aggregation type of dollar_price measure to Average. We can now switch to the Visualize room and create a time series chart.

All the parameters you’ve selected while importing the dataset are saved within the Lumira document. You can refresh the Lumira document to import the latest daily data using the Data > Refresh document option. You can also edit the parameters at any time using the edit option in Data > Edit data source in Lumira.

We’ve gone through a quick overview of the steps required to import a dataset from Quandl into Lumira. The data blending feature in Lumira will further allow you to link your internal datasets with the millions of datasets from Quandl with a few steps and expand the range of your possibilities.

Update – Nov 2016: Niklas added a new “Database Browser” feature to the extension.This will allow you to search for databases and datasets directly within Lumira itself in Create or Edit workflows without having to navigate and copy codes from Quandl.com. Download the latest version of the extension to check it out!

To access it, you have to select “Database Browser” from the main window.

Then enter a search term and press the return key to retrieve all the relevant databases from Quandl. Ex: “Employment”, “Stocks”, or “Economist” to retrieve the Big Mac Index for a country.

You can also select the “Proceed without selecting database…” option if you want to skip selecting one and proceed to searching directly within all datasets.

Select a database from this list, search and select a dataset within this database.

Your Quandl Code field will be automatically updated with the selected dataset.

You can now continue to import this dataset into Lumira.

At any point, if you are receiving an error in the UI saying “Unable to retrieve dataset list. Check your Quandl API usage limit or your internet connection“, it is time to register for a free Quandl account and add your key to the `API Key` field before opening the `Database Browser`.