Follow

Connect to JSON with a CData data source

Before you attempt this you need to add the JSON provider to your list of available sources.

Go through this guide before you start.

Add a CData data source

JSON Information

It should be mentioned that this provider can connect to JSONP files as well. Here is a link to the file I use in this example

http://mysafeinfo.com/api/data?list=moviesbyearnings2005&format=json&alias=yr=Year,rank=Rank,tit=Title,grs=Gross,opn=Opened,cnt=Theatres 

It contains the top 300 highest grossing movies in the USA in 2005. The setup is as follows. 

[
  {
    "Year": "2005",
    "Rank": 1,
    "Title": "Star Wars: Episode III - Revenge of the Sith",
    "Gross": 380270577.000000,
    "Opened": "2015-05",
    "Theatres": 3663
  },
  {
    "Year": "2005",
    "Rank": 2,
    "Title": "The Chronicles of Narnia: The Lion, the Witch and the Wardrobe",
    "Gross": 291710957.000000,
    "Opened": "2015-12",
    "Theatres": 3853
  },
  {
    "Year": "2005",
    "Rank": 300,
    "Title": "3-Iron",
    "Gross": 241914.000000,
    "Opened": "2015-04",
    "Theatres": 31
  }
]

CData Setup

Add a CData data source and point to the JSON provider.

The first step in setting up a CData connector should always be to click the ? in the top right corner.

Sp_rgsm_lstegn.PNG

JSON guide from CData

You have two methods of connecting to the data.

Online resource

If it is on a online resource location you need to fill out the following fields.

URI: http://mysafeinfo.com/api/data?list=moviesbyearnings2005&format=json&alias=yr=Year,rank=Rank,tit=Title,grs=Gross,opn=Opened,cnt=Theatres

You need to paste in the link to the JSON resource location.

JSON Path: $

This is the the root object/element. If you want to be more specific there is some good examples in this link. JsonPath

1.PNG

Local resource

If you have it saved as a .JSON file you need to fill out the following fields.

Data Source: <path to file>

Add the path to the file in the data source field.

JSON Path: $

The same as before.

3.PNG

Getting the data

Synchronize to get the available fields.

2.PNG

After that you can set up incremental load, make data type overwrites, data selection rules and use the Query Table tool.

You can also use additional data sources. You just need the additional sources to have the same metadata.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.