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.
2 of 2 people found this helpful
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!
3 of 3 people found this helpful
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:
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]))
5) Build a view, in this case table B is the ORG_UNIT table which has 0 records.
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:
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.