Connecting to Data Sources; SQL and Tableau

Connecting to the target Tableau Data Source (TDS) on the Tableau site

When you open DataSelf/Tableau Desktop, the screen shows the connection options on the left and recent workbooks opened on the right. Note there are multiple connection points. DataSelf Desktop uses only the Tableau Server (Target) and Microsoft SQL Server (Source). The Desktop app is the bridge connecting the two.

In most cases, when you log into DataSelf/Tableau Desktop, your login points to the correct Tableau site. But if your login credentials grant access to multiple Tableau sites, the Server menu is where you go to select the desired server and site.

Once the Desktop app is connected to the correct Tableau site, you can select a Tableau Data source on the site. Click on the Tableau Server option in the Search for Data section. A new window displays showing all the Tableau Data Sources available. Note under the Type column all the Data Sources display two cylinders. This indicates they are Extracts, where the data has been extracted from the SQL Server source, transformed and loaded into the Tableau Data Source. This provides better performance than a SQL direct connection.

If a single cylinder displays, this indicates a direct connection to the SQL data, not an extract. A direct connection calls the data from the SQL source, which slows performance. In DataSelf’s system, this serves no purpose because the SQL data does not change moment by moment. An Extract provides faster performance. Part of the DataSelf ETL+ scheduled refresh process is to also refresh the Extract.

This will connect to the cloud Sales_Invoice Data Source on Sheet 1 ready to design a workbook. But in this case, we want to modify the Data Source, not design a new workbook. The challenge is that you cannot modify an online data source, it is fixed. You must download the Data Source to Create a Local Copy. You can modify a local Data Source, then re-publish it back to Tableau.

Modify an existing Tableau Data source (TDS)

Right click on the Sales_Invoice Data Source and select Create a Local Copy.

The location window will display with the default path to the Documents\My Tableau Repository\Datasources folder. This is not a required location, simply the default. Once you have published the modified Datasource back to the Tableau site, you can delete the local copy.

Tableau will run the data query that created the original extract and store it on your computer.
If the Data Source has millions of records, this might take a while, depending on internet speed, etc.

After it has downloaded, you will see two Data Sources in the Desktop app. You can close the online Data Source since you won’t do anything with it. You will overwrite it when you publish the modified version of it back to the Tableau site.

Right click on the local copy of the Data Source and select Edit Data Source

Connecting to the SQL source data

Since you are modifying the Extracted Data Source, you must connect it to the SQL Data Warehouse source it is pulling data from. This requires knowing the server and database names and have a valid specific SQL User login, (not the windows authentication login).

The Desktop app runs the query connecting the SQL Data Warehouse tables to the Tableau data set and displays the Data Source edit window.

Note: running this query process might take several minutes depending on the size & complexity of the query.

The Tableau Data Source Name shows at the very top. Within the data source is the Tableau field called ARTran. This is a Tableau object field that contains more information. The object name is simply the name of the first table within the object. To open the object, you can double click, right click and open, or click on the down arrow and open.

The top half of the screen shows the diagram layout of all the SQL tables and the join types.

• The two circles with the left circle colored is a Left Outer Join.

• The two circles with the center overlap colored is an Equal or Inner Join.

• The two circles with the right circle colored is a Right Outer Join.

Click on the two circles icon to see the table join properties. In the screenshot below the unique link between records in the ARTran table and ARRegister table are ARTran.Refnbr = ARRegister.Refnbr and ARTran.TranType = ARRegister.DocType.

Note: This requires a working knowledge of database table linking rules for your data sources.

The bottom half of the screen shows the data field detail and properties. You can hide existing fields and create calculated fields. To see all the fields, click on the gear icon in upper right of section and select Show hidden fields. I find it easier to sort the fields in A to Z ascending order.

You can click on the top bar of the section and drag it up to make the section taller to display more data.

The Physical Table is the SQL table name. The Remote Field Name is the SQL field name. This is helpful when the Tableau field name is renamed. Clicking on the down arrow, or right clicking, displays the properties window. Notice the other property to hide/unhide the field.

Dataself’s policy is to never Rename the Tableau Field Name, or Copy Values.

Another DataSelf policy is to create Calculated Fields for the data fields you want to see, then hide the original fields. This way, if the ERP publisher changes field names, don’t have to go to EVERY workbook and manually change the field on the view. Just change field name referenced within the Calculated Field for the data set, then all the workbooks display as expected.

If you don’t see the field you recently added to the SQL data warehouse, try refreshing the data source.

Testing your data source modifications

Once you see the fields as expected in the Data Source Edit tab, you can click back to Sheet1 and test the data. Remember to choose the display options you want by clicking on the down arrow to the right of the search box. The typical defaults are Group by Folder, Sort by Name, uncheck Show Hidden Fields.

Note: Any calculated fields you create at the data source level like this become permanent fields for all workbooks when you publish. DataSource calculated fields cannot be modified at the workbook level.