Stored Procedure and Data Pivoting

Stored procedures are subroutine available to those applications that access a relational database system. When you connect to an SAP Sybase ASE, Teradata Database, or Microsoft SQL Server with Tableau, you can use a stored procedure for defining the connection.

For Oracle, use a table function for defining the connection. Oracle table functions act similarly to stored procedures and are listed under Stored Procedures on the Data Source page.

While creating a data source using one of these data types, available procedures are listed under the Stored Procedures, as shown in the below Microsoft SQL Server example:

From the left pane, either drag the procedure to the canvas or double-click on one of the listed procedures. If the parameters are available in the procedure, the Parameters dialog box will automatically display. 

Instead of entering a value, you can also use an existing Tableau parameter or create a new Tableau parameter for the value:

If you expose the Tableau parameter in the view, users can change the parameter’s value in the procedure interactively.

Stored procedures do not support federation, joining, or relating. They are represented under a single logical table in the logical layer and do not allow opening the Join/Union.

Notes on Stored Procedures:

If you are using stored procedures for defining a data source for Tableau, keep the following in mind:

• If the stored procedure will return more than one result set, Tableau reads the first and ignores the rest.
• If the stored procedure has output parameters, Tableau filters the stored procedure.
• Stored procedures that have parameters of a type non-scalar are excluded.
• Result set columns that do not have matching types in Tableau are logged. If all result set columns map to the unknown data types, Tableau displays the below message:

“The result set… has no usable columns.”

• Stored procedures that do not return any result sets are listed on the data source page.
• If no value is given for a parameter that the stored procedure requires, an error will occur. Tableau cannot decide in advance whether parameters are required.
• Tableau does not perform any transaction-management for stored procedures, i.e., stored procedure writers do not depend on Tableau to start transactions before calling stored procedures or to commit them afterward.
• Column names must be unique for the stored procedures. If the two columns have the same name or no name is provided, the procedure can result in an error.
• Suppose there are multiple queries in a stored procedure (for example, to read values from another table or to hold temporary combinations). In that case, each of the queries must return the same sets of columns in the same order having the same names and data types. For ensuring that column order and names match the query results, you may need explicitly CAST to ensure the data type is correct. If a stored procedure does not follow the above guidelines, an error message can result:

“InsertData: unbound column error.”

• If there are a number of queries in a stored procedure (for instance, to read values from another table or to hold the temporary combinations) and the procedure generates an error, try adding the SET NOCOUNT ON to the top of the procedure. This prevents the message that shows the number of rows affected by a Transact-SQL statement from being returned as a part of the result set for a query.

Pivot Data from Columns to Rows

Sometimes, analyzing data stored in a crosstab format can be difficult in Tableau. While working with Microsoft Excel, text file, Google Sheets, and .pdf data sources, you can pivot your data from crosstab format into a columnar format. If you are working with any other data sources, you can Pivot using custom SQL (Tableau Desktop).

For instance, suppose you have the number of devices sold in a quarter for three vendors in three separate fields. You can pivot the data so that the vendor is in one field, and the number of devices sold are in another field.

Pivot the data:

After setting up the data source in the grid, you can select two or more columns. Click on the drop-down arrow next to the column name, and then select Pivot. New columns named Pivot field names and Pivot field values are created and added to the data source. The new columns will replace the original columns that you have selected to create the pivot.

Add to the pivot:

For adding more data to the pivot, select another column, click on the drop-down arrow next to the column name, and then select the option Add Data to Pivot. Make sure that the pivot columns and values look as expected before you begin your analysis.

Pivot using custom SQL (Tableau Desktop):

You can use custom SQL to pivot your data, even if you are not working with Excel, Google Sheets, text file, and .pdf data sources. When you use the operator UNION ALL in a custom SQL query, you can take values from the distinct columns and put them in a new column.

For instance, suppose you have a table called Contest.

To optimize your analysis in Tableau, you can use the below custom SQL query for pivoting the columns Start Time and End Time so that their values reside in a single column.

The above custom SQL query will do the following:

• It will pivot the Start Time column header into a string value called Start and add that value to a new column Action.
• It will pivot the End Time column header into a string value called End and add that value to a new column Action.
• It will pivot the Start Time and End Time columns to create their values in a new column called Time.

The below table shows the results of this custom SQL query.