9 Replies Latest reply on Aug 10, 2018 6:37 AM by Jan van Roozendaal

    Edit Data source Date range

    amanda brandimore

      Hi All

       

      I've created a data source connection and I'm realizing I've brought in too much data. It takes forever when I make a change (add a new link to a new table). The refresh takes a long time. I'd like to change the relative date range. Is there a way to edit this after you've created a connection? I can't find it.

       

      Below is what I want to edit.

        • 1. Re: Edit Data source Date range
          Jan van Roozendaal

          Hi Amanda,

           

          I'm not sure whether you are working with a live connection or a data extract. If you work with the latter, you can drop the extract from the workbook on Tableau Desktop and create a new one, this time applying an extract filter on your relative date range (last 30 days) by using your date dimension --> Relative Date Range (or something similar). This allows you to have an extract of just the data you need.

          11.png

          The following Tableau Online help page explains how to drop existing extracts and create new ones with filters: Extract Your Data

           

          Let me know if this helps you in the right direction!

          - Jan

          • 2. Re: Edit Data source Date range
            amanda brandimore

            Hi Jan

             

            Thanks for the quick response. I am connecting to Servicenow (cloud) based system and when I do, I choose the "Relative date range" - "6 months". That's all I do when I connect.  I'm not asked any more information other than that. I've never seen the print screen you are showing me.

             

            When I go the top right, I don't get the option to edit. Based on the link you provided, I can't get past step 1.

             

            Bare with me I'm new, so I'm not sure if I'm missing something obvious.

             

            Thank you

             

            • 3. Re: Edit Data source Date range
              Ritesh Bisht

              Useful information here

               

              Date range selections can impact performance

              When you apply a date filter, it's tempting to gather as much data as possible when you do an analysis, however, retrieving records from ServiceNow ITSM can be time-consuming. Tableau doesn’t know how much data there is in a particular date range until it retrieves the data. For this reason, you should restrict your date range at first, and then expand after you evaluate performance. Date filters are applied to these tables: metric_instance, change_request, incident, task, problem.

              To give you a rough idea of how much time it might take to retrieve data from ServiceNow ITSM, tests were conducted using a high-speed connection. This table shows how long it took in the test environment to retrieve a given number of records.

              Number of records

              Time to retrieve

              1,000

              11 seconds

              10,000

              2 minutes

              100,000

              18 minutes

               

              I am not sure if you can edit there .

              Thanks,

              Ritesh

              • 4. Re: Edit Data source Date range
                amanda brandimore

                Thanks for that information. The question is can I edit the range after I've done the work, or do I start over and create a new data source.

                As for reading the data, we won't be hitting production which will be good, it'll be an reporting\intelligence environment.

                • 5. Re: Edit Data source Date range
                  Jan van Roozendaal

                  Hi Amanda,

                   

                  While I don't have experience connecting to Servicenow, I notice that extracts are already created and so indeed, step 1 can't be achieved Perhaps another method can be used to simply replace your large data source with a smaller one by still using the same data source connection method (but this time applying your 30-day range).

                   

                  To add a new data source, navigate to the following (you can do this in your existing workbook, no need to re-do the work):

                  22.png

                  You will then have to repeat the steps for establishing your data source connection with Servicenow, with the 30-day range.

                   

                  Now there are two data sources on the workbook, but your smaller one isn't connected to any sheets or dashboards yet. Assuming that your smaller data source has the exact same naming convention for dimensions and measures (don't worry about calculated fields), you can replace the data source with the smaller one as such:

                  33.png

                  (Ignore the irrelevant names for my example screenshots )

                   

                   

                  Once successfully replaced, you can right-click on the larger data source and select "Close". This will work when no data from that data source is used anywhere in your workbook. And hopefully, you will be left with a smaller data source and with your workbook still functioning as before.

                   

                  - Jan

                  • 6. Re: Edit Data source Date range
                    amanda brandimore

                    Thanks for that level of detail, I appreciate it. At this point i haven't even moved from the data source tab and I'm testing the data right there. Going back from the data source tab to the Sheets is really slow as it tries to retrieve all the records when I go back to the DS tab.. Hence why I want to change the range in the DS tab to something smaller.

                     

                    The 2nd print screen you attached is great to have for the future, but doesn't really meet the need of what I'm looking for as I"m not on the sheet yet.

                     

                    I think what I'm hearing is that if you create a DS with large ranges, you can't edit it. you need to recreate it. Which sorta stinks if you have relationships already setup.  There is no "edit range" that I get at all.

                    • 7. Re: Edit Data source Date range
                      Jan van Roozendaal

                      On the DS tab you could establish that new data source connection. If relationships or data joins are set-up on that DS tab, you could drag and drop your new data source to the place your larger one is, and if needed fix the join relationship details. That will do the same thing like the replacing method I suggested. Afterwards, you will have to close your other data source, but as long as it is not used it won't be queried. Of course, if you are stuck on the Executing Query window, it's another story...

                      • 8. Re: Edit Data source Date range
                        amanda brandimore

                        Thank you Jan. Excuse my ignorance, but where am I dragging and dropping? It doesn't seem intuitive to me. I have multiple data sources but they only appear when I click the drop down arrow on the ds icon.

                         

                        • 9. Re: Edit Data source Date range
                          Jan van Roozendaal

                          My apologies - to perform a cross-database join, you can use the "Add" button on the left hand side of the Data Source Pane, as shown from your last screenshot. You'll then get a list of two connections, but they'll be bundled within the same data source on the workbook (probably a poor choice of words to explain it from my side ). Then you can perform the drag and drop method as mentioned earlier.

                           

                          Here is an example where I used two Excel files: one bigger than the other. Now I drag the Orders table from the Small Data Source over the existing one from the Big Data Source:

                          444.png

                           

                          Once replaced (the connections are color coded, so the blue tag will be replaced by the orange tag in my example), you can close the connection with the Big Data Source:

                          555.png

                           

                          You'll be left with one connection where your Small Data Source has been placed on your existing joins:

                          556.png

                           

                          Could this be a suitable solution for you?

                          - Jan