3 Replies Latest reply on Aug 7, 2013 12:12 AM by giles.somers

    code for a custom sql to reduce data import size

    giles.somers

      Hi all

      I've been working late into the night all week trying to get a dashboard completed

      I've got several Excel sheets in a workbook joined as tables (I'm on v7 as we're still on Server v7)

       

      The Tableau workbook is incredibly slow with 15mins in between each change I make, so it has been a long process.

      So, I'm looking into ways to cut the data set down and make it more efficient

       

      - extracting wont work as it imports 20m rows then sticks at sorting after several hours (even fields I've left out of the joins seem to get imported?)

      - I was frequently getting 0x80004005 errors after 15mins of waiting with various queries

       

      My plan was to set up global context filters on a couple of fields, hoping that would create a smaller data set for extracting and working with (I still couldnt export initially but will try again)

       

      Alternatively, I'm considering custom SQL filters - most articles seem to suggest that's best (which makes sense)

      I've struggled to find examples of the right code though

      I just want to filter field X where the cells contain "text y" (and if possible also a second filter of field A where the cells contain "text B")

       

      Thanks for any help and advice

        • 1. Re: code for a custom sql to reduce data import size
          giles.somers

          So I added global filters at context level to try and reduce the burden, but it comes up with this

           

          'Microsoft JET database error 0x80004005: The size of a field is too long.

          Unable to create local query context'

           

          This error didnt come up before and I've not added any cells with long text names (or does the 255 field limit refer to something like a code that's generated from calculations or filters??)

           

          'Resolution

          Duplicate the data in a non-Jet data source, such as Microsoft SQL Server.

          Cause

          The field exceeds the Jet data engine character limit of 255.'

           

          I can't practically just duplicate to MS SQL server (but will if I had to).

          Is there another way around this, such as changing the Excel file contents?

           

          Each time I move to a solution some other error comes up!
          I can't work with the data as it hits the 2GB limit, it gives errors when I try to extract and now it gives a new error when I try to filter the data down.

           

          Only thing left for me to try now is the Custom SQL filter to see if that removes some of the other issues...

          • 2. Re: code for a custom sql to reduce data import size
            Dan Cory

            There are a lot of possibilities here. Mostly I would recommend you contact support and share with them the logs and Excel file that reproduce the problem.

             

            If you have a multiway Excel join and a lot of rows, then you are almost certainly better off creating an extract. I don't know why the extract is failing without seeing the logs. You can hide any columns you don't need before creating the extract and they won't be loaded. You can also create a filter in the extract dialog (context filters will not help you) and so only create an extract on some of the records.

             

            Dan

            1 of 1 people found this helpful
            • 3. Re: code for a custom sql to reduce data import size
              giles.somers

              Thanks Dan, I'll try a few of those things. Somehow I've managed to get an extract working but I get errors on refreshing some of the time still