4 Replies Latest reply on May 22, 2018 2:39 PM by Patrick A Van Der Hyde

    How do I show empty tables?

    Nick Baiamonte

      I am using a Microsoft SQL data source and have a bunch tables I would like to monitor.  The tables are all refreshed nightly so if something fails the table will have no rows in it.  I was hoping to show the number of rows in each table including the ones with no rows, that way I would know which table had the problem.  Easy enough for tables that are populated but I haven't been able to show anything for the empty tables.  Any way to do this?

        • 1. Re: How do I show empty tables?
          Patrick A Van Der Hyde

          Hello Nick,

           

          One thought - I run extracts each day for visualizations here in the community.  I use a command line batch file to refresh the extract and then send the output of that process to a file that is emailed to our team each year.  If the extract returns nothing for a table then the script is setup to echo the table name but the resulting rows is blank.  We then monitor the emails for null values to determine that an issue occurred during the refresh extract and can act.

           

          I am checking to see if there are other ides to help out here.  Also - I will move this to Tableau Server Server Administration as this sounds specific to a Server install. 

           

          Patrick 

          • 2. Re: How do I show empty tables?
            Matt Coles

            There are lots of ways to solve this. SQL Server gives you lots of options, especially. One you might use is write a single Custom SQL query to monitor all your tables:

             

             

            SELECT COUNT(*) AS Rowcount, 'table1' AS TableName
            FROM table1
            UNION
            SELECT COUNT(*) AS Rowcount, 'table3' AS TableName
            FROM table3
            UNION
            SELECT COUNT(*) AS Rowcount, 'table3' AS TableName
            FROM table3
            

             

             

            Repeat for all the tables you want to monitor. Alert on the measure "Rowcount" for when it's equal to 0. Make sure the TableName field is visible and it'll show up in the email you get. Boom, done!

            2 of 2 people found this helpful
            • 3. Re: How do I show empty tables?
              Jonathan Drummey

              I've used both Patrick's & Matt's suggestions to good effect, here's a couple more that use Tableau's data blending capabilities and minimal data preparation. Data blends are a form of post-aggregate left join so they can query each secondary source, get 0 or more rows back, and use that information to draw a view. One nice feature of using data blending is that the underlying sources can be just about anything (except for 0 row text files, Tableau doesn't like those and throws errors):

               

              Method 1: the organized way

               

              1) Create a text table with a row for each table with the table name.

              2) Add that as a Tableau data source and drag the table name dimension.

              3) Add a new data source for each table.

              4) Build a Records per Table calc with a formula that looks something like this:

               

              CASE MIN([Table])

                  WHEN "A" THEN ZN(SUM([a].[Number of Records]))

                  WHEN "B" THEN ZN(SUM([ORG_UNIT (MACEPA_ZM.ORG_UNIT) (MACEPA_ZM)].[Number of Records]))

              END

               

              5) Build a view, in this case table B is the ORG_UNIT table which has 0 records.

               

              Screen Shot 2018-05-22 at 3.49.57 PM.png

               

              Method 2: the ad hoc way using type in calculations and format special values:

               

              1) Create a one-row text file with one field and add that as a Tableau data source and drop the field on Detail as a dimension.

              2) Add a data source for each file.

              3) Double-click on detail and type in //[name of table] records, then press shift+enter, then start typing to add Number of Records from the given table, then tab in, then type *1. This creates a type in calculation with the number of records. Add that to Label.

              4) Drag Measure Values to the Show Me drop zone (which will be a tiny drop zone over the text mark) to create a measure names/values table.

              5) Double-click on the empty area of the Measure Values card and go through the steps for #3 for the next table.

              6) Repeat #5 for each measure.

              7) When you're done right-click on the Measure Values pill on Text and choose Format... then in the Pane tab->Special Value section->Text enter 0. This populates the Null values with 0.

              8) do other formatting as necessary.

               

              I did a GIF of steps 3-7 to show this, it took ~30 seconds to add two tables:

               

              show 0 records.gif

               

              Cheers,

               

              Jonathan

              3 of 3 people found this helpful
              • 4. Re: How do I show empty tables?
                Patrick A Van Der Hyde

                Jonathan Drummey with a gif    even.   Nick you have a superstar list of responses.  Please let us know what you do and how it all works out. 

                 

                Thank you to both Matt and Jonathan for jumping in here too.

                 

                Patrick