Lecture Notes: Using Custom SQL in Tableau

Introduction

• Overview of using custom SQL in Tableau.
• Differentiation between Custom SQL and Initial SQL.
• Usage of stored procedures and parameters with Custom SQL.

Setup for Demo

• SQL Server: Database used is SQL Server.
• Superstore Database: Contains tables - Orders, People, and Returns.
• Stored Procedure: Demonstration includes connecting stored procedures in Tableau.

Connecting to SQL Server

• Connection Screen: Find Microsoft SQL Server option.
  • Use "More" to search if not visible.
  • Input server details (local server or fully qualified domain name).
• Authentication: Options include Windows authentication or username/password.
• Database Selection: Changeable via dropdown in connection screen.

Using Custom SQL

• New Custom SQL: Accessed via the sidebar under "tables".
• SQL Statement: Example given: SELECT TOP 10 * FROM Orders.
• Viewing Data: Demonstration of viewing data in Tableau sheet.*

Introducing Parameters

• Editing SQL Query: Modify SQL to include parameters.
• Parameter Example: WHERE RowID <= [Parameter]
  • Use of integer parameter "RowID Parameter".
• Parameter Application: Dynamically adjust number of records shown.

Stored Procedures

• Stored Procedure Example: Returns order details based on product name.
  • Use of wildcard % for substring matching in SQL.
• Connecting Stored Procedures in Tableau:
  • Create parameters in Tableau to pass into stored procedure.
  • Example shown with "Product Name Parameter".

Limitations with Stored Procedures

• Live Connection: Issue with creating extracts due to parameter referencing.

Initial SQL

• Differences from Custom SQL:
  • Initial SQL runs upon connection, Custom SQL is used for data pulling.
• Usage: Prepare data or set database settings.

Initial SQL Example

• SQL Snippet: Create and utilize temporary table for session information.
• Parameters: Supported parameters include Tableau server user, version, app, workbook name.

Demonstration in Tableau

• Custom SQL Query with Temporary Table: Utilize session information stored in temporary table.
• Display in Tableau: Show environment values (e.g., Tableau app, version) alongside order data.

Conclusion

• Recap of Custom SQL, Connection procedures, and Initial SQL.
• Encouragement for further exploration and application of these skills in Tableau.