Link Stored Procedures using Initial SQL

Version 1

    Why this post?

    I recently ran into a situation where access to data was only allowed via stored procedures. The data that we needed was stored in SQL Server, and to get to it we first needed to:

     

    Step 1. Execute one stored procedure which would return one value: the current version of a product.

    Step 2. Execute another stored procedure which would receive the value from step 1, and return the data set that was going to be used in the workbook.

     

    The sequence was needed because the table accessed by step 2 was rather large, so the stored procedure would filter it to only what was specified by step 1.

     

    Wait, are you sure we need this post?

    In a perfect world we could:

     

    a. Access the tables directly and use regular filters in Tableau.

    b. Or, modify the database to consolidate both stored procedures.

     

    But in this imperfect world, we didn't have the permissions to do that. So...

     

    OK, what do we do then?

    Initial SQL to the rescue.

     

    At a high level we are:

     

    1. Defining the temporary tables.

    2. Executing the first stored procedure and saving the results in one temporary table.

    3. Reading the results of the temporary table and saving it as a variable.

    4. Executing the second stored procedure passing the variable described above and saving the results in another temporary table.

     

    With this approach we were able to not just bring one version's worth of data but 2, which allowed us to compare how each was performing and was a nice extra.

     

    Once that's done, we have a temporary table we can access using custom SQL:

     

    After that, it's all the usual Tableau magic!