| Table of Contents | ||
|---|---|---|
|
Extract Endpoint - Beta Version
Many organizations have their data extracted into DataSelf cloud Many organizations store data in their DataSelf cloud BI servers for optimized reporting and data analysis. The Extract Endpoint DataSelf API allows them to easily and securely download their access and manipulate data for further reporting or manipulation using other tools such as Excel.
Core Endpoint
https://api.beta.dataself.com/<metadata server>/entities/<entity id>/extract?token=<user token>&source=<function>
Core Parameters
<metadata server>: dw6 (dw6=beta metadata server).
<entity id>: ETL+ Entity ID, e.g.: abccorp
token=<user token>: ETL+ user token - you receive it in your DataSelf Product Registration email. To retrieve it, search your inbox for “DataSelf Product Registration“ or log into your ETL+ → Settings → Client Info → Send → check your email for the product registration.
source=<function>:
dw: See SQL Extractor
tableau-view: See Tableau View Extractor
SQL Extractor
Allows the extraction of data from the client’s cloud data warehouse. It can extract the list of available tables and views, tables/views data, define table/views columns to download, and apply extract filters.
Endpoint
https://api.beta.dataself.com/dw6/entities/<entity id>/extract?token=<user token>&source=dw&schema=<schema name>&object=<table/view name>&fields=<field name1>,<field name2>&filters[<filter field name1>]=<filter value>
Parameters
See Core Endpoint for core parameters.
schema=<schema name>: Optional. If not used, it assumes the schema is dbo.
object=<table/view name>: Optional. If not used, the endpoint returns the list of available tables/views including their row count, bytes, and create date.
fields=<field name1>: Optional. Can only be used in conjunction with the table parameter. If not used, the endpoint returns all fields of the table/view. Use commas to separate multiple fields.
filters=[<filter field name1>]=<filter value>: Optional. Can only be used in conjunction with the table parameter. When used, it’ll filter the extraction based on the criteria.
Example:
The list of tables/views:
https://api.beta.dataself.com/dw6/entities/abccorp/extract?source=dw&token=xx
A table/view from the erp schema:
https://api.beta.dataself.com/dw6/entities/abccorp/extract?source=dw&token=xx&schema=erp&object=ITMMVT
A table/view from the dbo schema:
https://api.beta.dataself.com/dw6/entities/abccorp/extract?source=dw&token=xx&object=ITMMVT
Some columns from a table/view:
https://api.beta.dataself.com/dw6/entities/abccorp/extract?source=dw&token=xx&object=ITMMVT&fields=AVC_0,ITMREF_0,ROWID
A filtered table/view:
https://api.beta.dataself.com/dw6/entities/abccorp/extract?source=dw&token=xx&object=ITMMVT&filters[ITMref_0]=Fin063
Output format
CSV
Future: JSON
Output options
CSV download: use the endpoint on an incognito web browser’s tab
Excel: go to the Data ribbon → From Web → paste the endpoint → Ok.
Security
ETL+ User: The ETL+ API user token ties users to their ETL+ entities and data warehouses.
IP Whitelisting: This feature only works behind DataSelf VPN or via IP whitelisting on dw6. In the future, the plan is to use Azure Entra to secure the extraction without IP Whitelisting.
SSL enabled.
Notes:
Endpoint syntax is case-sensitive, except for the parameters.
With the beta API, some extracted characters, such as commas and line breaks, can mess up the CSV file. Here’s a SQL transformation to clean up some of them: REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(Column1)), CHAR(13), ''), CHAR(10), ''), ',', ''), '"', '""')
The order of the parameters after ? is not relevant.
Tableau View Extractor
Allows the extraction of data from the client’s Tableau site. It can extract the list of available workbooks and views from an API-dedicated Tableau Project, and then extract data from views from that project.
Endpoint
https://api.beta.dataself.com/dw6/entities/<entity id>/extract?token=<user token>&source=tableau-view&view=<view id>
Parameters
See Core Endpoint for core parameters.
view=<view id>: Optional. If not used, the endpoint returns the list of available Tableau views, workbooks, and view IDs from the ds_tableau_api Tableau Project. When this parameter is used, it’ll return the view’s data unpivoted. You might need to create optimized Tableau views for the API extraction (e.g., remove the view totals and subtotals and other simple calculations that can be done in Excel).
Example:
Extract the list of views:
https://api.beta.dataself.com/dw6/entities/abccorp/extract?token=xx&source=tableau-view
To extract a view:
https://api.beta.dataself.com/dw6/entities/abccorp/extract?token=xx&source=tableau-view&view=viewid
Output format
CSV
Future: JSON
Output options
CSV download: use the endpoint on an incognito web browser’s tab
Excel: go to the Data ribbon → From Web → paste the endpoint → Ok.
You might need to pivot/unpivot the table to view it like the Tableau view.
Security
ETL+ User: The ETL+ API user token ties users to their ETL+ entities and what Tableau sites (must match ETL+ entity) they can access. In the future, this will pull the Tableau entity from the ETL+ Job Steps' Tableau scripts.
IP Whitelisting: This feature only works behind DataSelf VPN or via IP whitelisting on dw6. In the future, the plan is to use Azure Entra to secure the extraction without IP Whitelisting.
SSL enabled.
Tableau Site: Add ds_tableau_api user to the client’s Tableau site. In the future, the user will add their own Site Admin credentials. Perhaps the user credentials will control what views they can access.
Dedicated Tableau Project: The endpoint requires views saved in the ds_tableau_api top-level project. Be sure to create that project and copy desired workbooks into it.
Notes
...
Endpoint syntax is case-sensitive, except for the parameters.
...
With the beta API, some extracted characters, such as commas and line breaks, can mess up the CSV file.
...
data analysis.