You can import data from REST API sources which return JSON, XML or CSV data. Data import is similar to Excel and CSV file upload but you do not need to export source data to file and upload to eazyBIflex.bi. In addition you can schedule regular daily import from provided REST API data source.
Please at first read about eazyBI about flex.bi cubes, dimensions and measures - you will need to understand multi-dimensional data model to be able to map source file columns to cube dimensions and measures.
In this tutorial we will use GitHub issues REST API data source as example.
On this page:
- Create new source application
- REST API source parameters
- Incremental import
- Custom JavaScript code
- Authorize access to REST API source
- Source columns mapping
- Importing of source data
- Export definition
- GitHub issues source application definition example
| Table of Contents |
|---|
Create new source application
...
It is recommended to use page or offset and limit parameters if possible as it will enable concurrent REST API requests and will make the import faster. By default, up to 10 concurrent REST API requests will be made at the same time. Starting from the eazyBI version 4.7 you You can adjust the maximum number of concurrent requests using the Concurrency parameter (for example, reduce it if too many concurrent requests are causing errors in the source application).
Next, we need to specify if and how the requests should be authenticated in Authentication parameters. You can use simple Basic authentication and provide username and password or specify authentication HTTP header name and value, as well as use OAuth 1.0a or OAuth 2.0 authentication. If you need to use OAuth authentication then typically you will need to register eazyBI register flex.bi as consumer / client in the provider application.
In this example GitHub uses OAuth 2.0 for their REST API authentication. At first we need to register eazyBI register flex.bi application in our GitHub account settings / Applications:
...
Please specify there Authorization callback URL as Redirect URL that you see in eazyBI in flex.bi REST API source parameters page.
After registration you will get your application Client ID and Client Secret - please paste them in eazyBI in flex.bi REST API source parameters page corresponding fields.
...
If you will create several REST API sources in your eazyBI your flex.bi account that all retrieve data from the same source site (GitHub in this example) then you can use the same Client ID and Client Secret for all these REST API sources.
...
Finally specify wether REST API will return JSON, XML or CSV. In case of JSON you can specify JSONPath expression which returns JSON array of objects that we want to import in eazyBI in flex.bi (it is necessary if REST API returns more complex object and array of data for import are in some lower level JSON attribute). Similarly in case of XML you can specify XPath expression which returns list of XML nodes that should be imported in eazyBI flex.bi.
In our GitHub issues example we do not need to provide JSONPath expression as REST API will return simple array of issue objects.
...
- Select Use incremental import to enable it.
- If REST API does not provide parameters for selecting just the recent updated data then you can use the option "Stop incremental import when no source data are changed in a returned page". If during the import some REST API results page will have all the same data that have been imported before then the import will be stopped. But please be aware that if data are changed when stored in eazyBI in flex.bi (e.g. decimal numbers are truncated when stored) then the source data page will not be exactly the same as in eazyBI flex.bi.
Therefore it is recommended to specify Additional URL parameters to limit just the recently updated source data. Typically you need to specify a URL date parameter that limits results to recently updated source data.
For example, in our GitHub issues example REST API hassinceparameter to return only issues updated at or after the specified date and time. We can use the following additional URL parameter:since={{ 3 days ago | %Y-%m-%d }}This will dynamically get a relative date and time 3 days ago and will format it using a strftime format string %Y-%m-%d (see available strftime format options). The following relative time units can be used – years, months, weeks, days, hours, minutes, seconds. And instead of ago also from now can be used to get a date and time in future.
If incremental import is used then it will be required to specify a Source ID column in the source columns mapping step. Source ID column value should provide a unique results row identifier. It is used to identify when some existing imported rows in eazyBI in flex.bi should be replaced with updated source data during the incremental import.
...
You can use custom JavaScript code to modify received JSON, XML or CSV data before importing into eazyBI flex.bi. Click Add custom JavaScript code to show code editor.
...
If you would like to skip some data rows and do not import them in eazyBI in flex.bi then use return false; in these cases. Here is example which will skip data rows which do not have title property:
...
You can also create new properties for the doc object when you need to construct eazyBI construct flex.bi dimension level names or calculate additional measures. Here is example how to create full_name property:
...
You can map one source data row to multiple data rows that should be imported into eazyBI flex.bi. For example, if doc is an invoice object with several lines then you can return an array of invoice lines which contains both invoice and line attributes:
...
You can use Underscore.js functions (like _.each) in your custom JavaScript code.
Please contact eazyBI contact flex.bi support if you need help to write custom JavaScript code for your specific needs.
...
In case of OAuth authentication in the first time you will be redirected to source application authorization page where you will need to authorize eazyBI authorize flex.bi access to source application data:
...
After successful authorization you will be redirected back to eazyBI to flex.bi Source columns mapping page. If there will be any authentication errors then you will see corresponding error messages - please review your authentication settings and try again.
...
REST API source columns mapping is similar to Excel or CSV file columns mapping where you specify which source data fields should be imported as corresponding eazyBI corresponding flex.bi dimensions or measures. Please review Excel and CSV file upload documentation page if you have not yet done so.
You can click Generate default names toolbar button to turn on automatic dimension and level and measure name generation from column names (which is turned on by default when doing file upload). And when you have mapped columns that you want to import in eazyBI in flex.bi then you can click Hide unmapped columns to hide columns that will not be imported into eazyBI flex.bi.
See example of GitHub issues columns mapping:
...
If you need any help with REST API source data import then please contact eazyBI contact flex.bi support.