...

SQL Extractor - Beta Version

Many organizations have their data extracted into DataSelf cloud servers store data in their DataSelf cloud data warehouse(s) for optimized reporting and data analysis. The Extract Endpoint SQL Extractor allows them to easily and securely download their data for further reporting or manipulation using other tools such as Excel.

...

.

The SQL Extractor can extract the list and the data from the data warehouse tables and views, as well as define a subset of the data by defining columns to download and/or applying extract filters.

Endpoint

https://api.beta.dataself.com/

...

dw6/entities/<entity id>/extract?token=<user token>&source

...

Core Parameters

...

=dw&schema=<schema name>&object=<table/view name>&fields=<field name1>,<field name2>&filters[<filter field name1>]=<filter value>

Parameters

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

...

• 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

...

- Beta Version

Many organizations store data in their DataSelf’s cloud Tableau Embedded site(s) for optimized reporting and data analysis. The Tableau View Extractor allows them to easily and securely download their data for further reporting or manipulation using other tools such as Excel.

The Tableau View Extractor can extract the list of available workbooks and views from an API-dedicated Tableau Project, project and then extract their 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<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.

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

...

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