REST API and Structuring Data

Stephen Kirk

I am currently trying to use the REST API connector within BOARD to bring in data. I have been able to successfully make a connection but it brings the data in as rows rather than as columns as would be desired to be able to load to entities/cubes within BOARD.

Ideally in the above, "LEADID", "Last Name", "Email" and "Website" would be my columns.

 

Has anyone had any experience or have any ideas how to get this data in a format that is suitable for use in BOARD.

 

Thanks,

Stephen

Daniele Di Lorenzo

Hi Stephen

 

you can read this table more times and each time set a different filter on ETL validation field so only records referred to one parameter (e.g. LEADID) will be uploaded.

 

Or set a cube with a dimension called e.g. "Data Type" to be fed with field "val", so you will have all infos in 1 txt cube (with dimensions: "val" & "no") and uploaded with 1 data read

 

 

hope this helps

best regards

Daniele

Stephen Kirk

Thanks for this.

 

Unfortunately there are quite a number of tables that we will be reading from, and setting up so many individual readers and with changing Val columns (such as new account codes, months) doesn't look to be a workable solution. I've seen in other systems - like as Qlik allow the control of scripts to format the table themselves - is there any functionality such as this available in BOARD?

 

Daniele Di Lorenzo

In SQL data readers you can set a manual sql script but, as far as I know, not in connector data readers.

 

My 2nd solutions works better in my opinion. If you have more tables you can create one cube/one data reader by each connection, maybe using auxiliary entities/trees

Once in Board you can process data in order to obtain structure you want using data flows, layout exports and  reload to final structures etc..

 

Sorry but since I don't know the tables structures and the result you have to achieve I cannot be more precise at this stage

Unknown

You could also use a script (powershell or python) to generate a local CSV file on the server. Once you have a local CSV, you can pivot the data in a data reader.  

Unknown

Hi Stephen,

 

unluckily there are many flavours of REST APIs nowadays and getting them into BOARD directly through the connector is really a  matter of chance - the API standard with flattened data through GET requests that is covered by our connector is enough most of the times but, like in your specific case, it cannot do much when data is not in a "friendly" format to be loaded into BOARD.

If you have a lot of different entities to loop for and cannot use Daniele Di Lorenzo solution (which is very neat), the best option would be to either save the file in csv through a script as Bob Gill says - i could also suggest another solution, to build an API wrapper.

If you have enough knowledge of python (there is actually a lot of half-cooked scripts on the web, just by googling it) it's easy to build a wrapper. In your case you can get the wrapper to talk to the API and reformat the data in the way you want, and expose it again (through another API or directly through csv files).I used this approach in order to have a customer BOARD Cloud instance to connect to a set of data exposed from a source system API - the wrapper has been set to run on a very small cloud box that was doing all the API processing and ETLs needed and then re-expose the data in a BOARD-friendly format to our BOARD API Connector.

 

Hope this helps

 

Michele

Previous Member

Hello Stephen Kirk , I too am trying to import data into Board through API but no luck so far, i would be really thankful if you can share screenshots and knowledge regarding creating connection through REST adapter and extracting queries.

 

Thanks and Regards,

Malav shelat

Stephen Kirk

Hi Malav,

With the particular API I was testing, I could handle accessing the query through just the URI and hence it was just a case of having the correct setting here:

 

However, I am now having similar problems trying to access another API, which I will create a post on now.

 

Cheers,

Stephen

Previous Member

Thanks Stephen Kirk , if possible please tag me on that so i can follow.

 

best regards,

Malav Shelat

Daniele Di Lorenzo

Hi Michele Roscelli

 

very interesting the idea of the API wrapper

I'll cnsider it in future needs, if any

 

thanks a lot

buona giornata!