2 Replies Latest reply on Dec 29, 2011 10:05 AM by Ian Waring

    Am I confusing Tableau's Excel Engine?

    Ian Waring

      Our old AS400 ERP system was retired at the end of August, replaced by a SAP system. I need to carry data from January 2009 to August 2011 (which I pick up downstream of Essbase drill downs in Excel), and combine it with data from the SAP system - so that I can do trend analyses across both data sources.


      Unfortunately, we sell quite a bit of kit (we are the UK sub of the largest IT reseller in Europe), so the UK only data derived from drill downs amounts to some 2.3 million rows - which I have in three sheets inside a .xlsx file. So, thinking I was being clever, I made up a single named range that combines data from all three sheets, thinking that Tableau could feed off said named range. It defines a range called "SalesOut" that is successfully associated with ranges in 3 separate sheets in the same workbook (see Page 1 of the attached for a screen shot of the definition in Excel 2007).


      I open the Excel sheet, but before it presents the list of sheets and named ranges, it throws an error - see the second page of the attachment. It thinks the spreadsheet is already open by another user or application - which it isn't.


      Any idea of a bug fix or a work around?

        • 1. Re: Am I confusing Tableau's Excel Engine?
          Richard Leeke

          Looks as if you're confusing the MS Jet engine which Tableau uses.


          Options are either load the data into a database, or if you prefer to leave it in Excel you can do it with a custom SQL statement to UNION the three sheets together.


          The easy way to do that is to connect to the workbook and initially choose one of the sheets for a single table connection - SalesOut1 say.  But rather than save the connection, change it to Custom SQL.  Edit the Custom SQL and it will initially have a SELECT statement which lists all of the fields from sheet SalesOut1.  Copy that statement and paste it back in so you have 3 copies of the statement one after the other.  Insert lines between the statements saying "UNION ALL".  Edit the text of the second and third copies, replacing all references to "SalesOut1" with "SalesOut2" and "SalesOut3" respectively.  that should give you what you want.


          It may well be very slow - so you may need to create an extract - but at least it gets you the data you want without having to worry about creating a new database.

          • 2. Re: Am I confusing Tableau's Excel Engine?
            Ian Waring

            Thankyou Richard. I just need to get as far as an extract. I think that'll be a feat in itself rather than trying to blend Excel and Microsoft Analysis Services data (which would be the next stage), the latter of which is temperamental at the best of times :-)