3 Replies Latest reply on Aug 13, 2019 1:12 PM by Jonathan Drummey

    Refresh within Prep not working?

    Chris Conner

      For back story: I am connected to a MySQL server DB and have created a nice flow from several table joins with multiple stages of cleaning and output going to our Tableau server as a data source. I have this data source connected to a single workbook. I have all the latest versions. I am also by no means an expert user.

       

      This is all fine and well. I use the data source in my workbook as an extract and no issues there. The problem I'm having is that within Prep Builder i cannot get this data flow to refresh from the server without closing the app and then re-opening it, which also requires a re-log into Tableau server. There is a constant flow of data to the tables on the server from our front-end web based UI and i need to be able to validate my data as I'm building this workbook against a live connection to those same tables with another workbook. I also need to validate this data and the workbooks against the web based High Charts that run from the server. For reference, we are trying as a company to get away from IT having to be in control of all our BI.

       

      I know this seems extravagant but i am trying to really make use of Prep and get away from having to write SQL constantly. The Prep UI is 100X more effective for joins and cleaning. Compared to writing SQL all month and planning out which table attributes to use and what needs to be cleaned up server-side....not to mention planning out projects and scheduling time for getting IT to do the cleaning. I couldn't even begin to calculate how much time i save.

       

      Does anyone else feel me on this?

       

      I'm not worried about setting up Conductor on the server and going that route. However i don't understand why, as I'm using Prep, i cannot refresh my data on the fly. I can see a little circular button at the top, but when i hover over it i get "No refreshes available". No amount of running my flow does anything. Am i doing something wrong?

       

      Thanks

        • 1. Re: Refresh within Prep not working?
          Jonathan Drummey

          Hi Chris,

           

          I'm not sure I totally understand the results you are expecting vs. what you are experiencing, so I'll walk through what Prep does and then we can try to figure out where the diversion is.

           

          First of all, though, given that there's a front-end web-based UI that's generating data, writing it into MySQL, and then you're reading it from Prep another test to do is check whether that that the fresh data that you're expecting to see in Prep is really in the MySQL tables you are querying from. I'd suggest doing a quick set of tests with a SQL tool to make sure that when you do a SELECT query on the MySQL table(s) (which is what Prep is doing) that the data is there. I write this from personal experience: I've spent hours with a few different visualization & preparation tools tearing my hair out wondering why they weren't showing my "latest data" when it turned out that the latest data wasn't actually there, it was still in some cache or staging table and not where I was trying to query it from.

           

          Assuming that the data is actually in the database where you expect it, then Prep essentially has two modes of operation: The first mode is when we're editing flows in Prep Builder, the second is when a flow is being run (whether in Prep Builder or via the command line or conductor, though each of those can have their own variations in behavior for this purpose they are all the same).

           

          In run mode (clicking on the triangular play/Run button or using the Flow->Run All menu in Prep Builder) Prep is running the flow from beginning to end so it goes out to the input connections, fetches the latest data, goes through the steps of the flow, and produces the output(s). In Prep Builder we do this by clicking on the Run button. Is this happening as expected for you?

           

          In edit mode Prep Builder does a whole lot of caching and regularly updates the profile pane & data grid as we move around in the interface. It sounds like you are expecting to be able to click the Refresh button (circle made of two arrows) at any step and that every Input step will refresh, is that correct? (This is also the documented behavior of Prep). It sounds like it's not happening for you, is that the case? If so, what happens if you refresh an individual Input step - does that work? Also, just to be clear, clicking the Refresh button *does not* write any output. If you need to generate output then you have to run the flow.

           

          Hope this helps!

           

          Jonathan

          • 2. Re: Refresh within Prep not working?
            Chris Conner

            Thank you for the reply Jonathan. This actually set me on the right path but also brought other info to light.

             

            I am able to verify when new data is available by using a Tableau workbook that connects to the main table I'm pulling data from. I'm just using a COUNTD(table.id) on this table to get the number of records and I'm able to see the latest records in full with a cross-tab. On the web-UI side,  I can see new data being entered because it shows up immediately and is displayed in the web page via a DataTables API feed. These two are in sync, so... I would expect that if the data is showing there, then obviously it stands to reason that the data exists on the server and should be available to query by Prep.

             

            What i have figured out is that when i run the flow, it does fetch the correct amount of records that are available, however it does not update the number of records being displayed in the Changes Pane under join result.

             

            It will show me the number of records that were queried when i run the flow however, but the Changes Pane never updates unless i completely shut Prep down and restart the file. I have also tried changing the join type to no avail. It just goes right back to what it was. So clearly it seems to me that once the file loads the data in, no amount of running flows brings new data in. That makes sense, but feels wrong. I should be able to refresh my table joins and pull in the new records, which would change my join result.

             

            These two screen shots illustrate my point. Even though I'm joining several tables, they are all left joins and the number of distinct records does not change, so these two numbers should be matching. The correct COUNTD on this table is the 58,834, not the 58,829 as the join result is displaying.

             

             

            "It sounds like you are expecting to be able to click the Refresh button (circle made of two arrows) at any step and that every Input step will refresh, is that correct? (This is also the documented behavior of Prep). It sounds like it's not happening for you, is that the case?"

             

            I am not able to click on the refresh button at any time, or any step. I've tried clicking on every step i have and nothing.

            When i click on "Run all flows" whether its the output step, menu, or one of the buttons, it runs the flow fine.

             

            Now that I'm seeing this i guess my issue is that Prep is not working as i was expecting. I feel like the join results should update when i run a flow however. Not sure if this is the intention.

            • 3. Re: Refresh within Prep not working?
              Jonathan Drummey

              Hi Chris,

               

              Thanks for the detailed response!

               

              Re: your point "I feel like the join results should update when i run a flow however. Not sure if this is the intention." This is not how Prep works at this time (to the best of my knowledge). Running a flow is a distinctly different mode for Prep from editing a flows, and the Join window's results are definitely part of edit mode.

               

              A key difference in editing vs. running is that in edit mode inputs, joins, aggregates, and pivots can all trigger sampling by Prep (and we have explicit control over sampling in the Input step), so I'm thinking that most likely what is happening is that there's some sampling going on. You can tell when that's happening when you see the orange Sampled text in the toolbar of the Profile/changes pane on a given step (and downstream steps that use that sampled data). Recently I created a feature request for turning off sampling so we can get "same-same" results between edit mode & running a flow at https://community.tableau.com/ideas/10257 , please vote for it if you'd like to see that added.

               

              If there's not sampling happening then even though you're seeing same-same results through the web interface I'd go back to my suggestion about testing the SQL queries...it's unlikely but over the years I have seen weird results where depending on the access method a database returned different results based on internal caching in the database. You can use Prep's log files to see the queries that it is issuing. If you're able to completely rule that out then it would be something for Tableau support, I think.

               

              In any case the fact that the Refresh button doesn't seem to be anything for you is a potential issue, I suggest reaching out to Tableau support for that as well.

               

              Jonathan