Skip to end of banner
Go to start of banner

DataSelf ETL+ API

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 20 Next »

Extract Endpoint - Beta Version

Many organizations have their data extracted into DataSelf cloud servers for optimized reporting and data analysis. The Extract Endpoint allows them to easily and securely download their 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:

    • Click Ok.

    • Set the Delimiter to Comma.

    • If the table has undergone structural changes since the last extraction on this Excel install (cache), click the Refresh icon.

    • Click Load.

      • If you see errors on the Queries & Connections panel after the data load: mouse over the errors section and click on it to open the Power Query Editor. Scan through the shown tables for “Error”, click on them to get a description on the bottom panel. The most common fix to these errors is by going to the main query in Power Query → select Changed Type on the right panel → edit the Table.TransformColumnType expression (drop down at the top of the table), for instance, a column set to Inte64.Type must be changed to type text.

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/Issues:

  • 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), ''), ',', ''), '"', '""')

  • Endpoint syntax is case-sensitive, except for the parameters.

  • 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:

    • Click Ok.

    • Set the Delimiter to Comma.

    • If the table has undergone structural changes since the last extraction on this Excel install (cache), click the Refresh icon.

    • Click Load.

      • If you see errors on the Queries & Connections panel after the data load: mouse over the errors section and click on it to open the Power Query Editor. Scan through the shown tables for “Error”, click on them to get a description on the bottom panel. The most common fix to these errors is by going to the main query in Power Query → select Changed Type on the right panel → edit the Table.TransformColumnType expression (drop down at the top of the table), for instance, a column set to Inte64.Type must be changed to type text.

    • 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/Issues

  • We recommend cleaning up Tableau views used for data extraction such as removing totals, subtotals, and hidden or unnecessary dimensions and measures.

  • 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.

  • The order of the parameters after ? is not relevant.

  • No labels