3 Replies Latest reply on Aug 14, 2013 9:30 AM by erik lundberg

    Scheduled extract refresh every hr for 6 hr from Teradata table

    erik lundberg

      Hi,

       

      I have a Teradata table that's created once a day between 6am and noon.  I have scheduled a extract refresh every hour between 6am and noon.  I tried adding a "DROP TABLE myTable;" as a second query in the custom query but it does not let me continue complaining that there is something wrong with the query.  By doing it this way the scheduled refresh would only actually run sucessful one time of the 6 times it runs.  After the successful run the table is deleted and the next refresh fails.  Leaving the extract as is until next day.

       

      Is it not allowed to have more than one select statement in the custom query?

       

      I know about TABCMD and that works just fine.  But I wanted to solve this problem without having to add another layer of scripting/bat files...

       

      Regards

      Erik

        • 1. Re: Scheduled extract refresh every hr for 6 hr from Teradata table
          Russell Christopher

          Hey Erik -

           

          So if I understand, you're trying to fire DDL statements at Teradata from Tableau? Does this even work from Desktop?

          • 2. Re: Scheduled extract refresh every hr for 6 hr from Teradata table
            erik lundberg


            Hi Christopher,

             

            I'm trying to find a workaround for Tableau not having something that can have it trigger a refresh wnd data in a table have been updated.

             

            If it would be possible to execute a DROP TABLE myTable after the SELECT statement have finished.  The rest of my scheduled refreshes will fail and that's ok.

             

            I'm trying to avoid using the tabcmd command due to our strict server environment.

             

            I was also thinking of a incremental refresh but don't know of a way to purge existing old data out of the extract.

             

            Regards

            Erik

            • 3. Re: Re: Scheduled extract refresh every hr for 6 hr from Teradata table
              erik lundberg

              Ok, I think I have a solution to the refresh: We start with an extract with snapshot-date = 2013-08-10 (every record in the file/table has this date on it) • When publishing we add TWO schedules, one INCREMENTAL refresh(runs every hour from 6am to noon) and one FULL refresh(runs at midnight) On the morning of 2013-08-11 • 6am a scheduled INCREMENTAL refresh runs, because NO new file/table have been pushed, nothing is updated. • 7am a scheduled INCREMENTAL refresh runs, because NO new file/table have been pushed, nothing is updated. A NEW FILE IS FTP’ed or NEW DATA is INSERTED into a TABLE • 8am a scheduled INCREMENTAL refresh runs, because a NEW file or TABLE with snapshot-date 2013-08-11 the extract is updated. • 9am a scheduled INCREMENTAL refresh runs, because NO new file/table have been pushed, nothing is updated. • 10am a scheduled INCREMENTAL refresh runs, because NO new file/table have been pushed, nothing is updated. • 11am a scheduled INCREMENTAL refresh runs, because NO new file/table have been pushed, nothing is updated. • 12am a scheduled INCREMENTAL refresh runs, because NO new file/table have been pushed, nothing is updated. The workbook has a FILTER that only includes the TOP snapshot-date. • Midnight a scheduled FULL refresh runs, and creates an extract with only snapshot-date 2013-08-11 included. I know that the extract is twice the size it needs to be. This works on a text file and on a database table. Regards Erik