2 Replies Latest reply on Jun 5, 2012 1:11 PM by Dan Cory

    Advice needed for managing my data updates and extracts

    Gareth Robins

      Up until now I've stumbled on to my current system for managing the weekly updates to my data.  I would love to hear your thoughts on a better way to achieve what I want.

       

      All our data is stored in SAP BW, which I have to extract using a query tool in Excel (BEx).  I'm reporting on article sales (products) by week, by store, by year.  This has so far yielded around 15mill rows.

       

      I've created a query to extract the data via cycling through several VBA queries, then exporting that data into MS Access.  So that process is automated each week, just type in a new week number, press a button and wait. 

       

      There's so much data that I've had to split the Access DB into one for each year, 2008 onwards.  Then another DB links those tables and creates a UNION ALL, then adds in some characteristics about each store, that weren't part of the original BEx queries. 

       

      Tableau crashes if I try and create an extract from all data at once.  Something about the JET database having unsufficient memory.  So in Access I filter on the first year, import into Tableau to create the extract, then filter on <2009, and do an incremental refresh, and so on.

       

      Once all the data is in there, I have a tableau workbook that just updates the extract using a incremental refresh.

      I have another workbook that only points to that created extract.  This is my working workbook, where I create many different views depending on peoples requests.  There are no dashboards, people just want answers, so it's more exploratory.  I save each view as a bookmark for future access.

       

      I have three other dashboards that I refresh.  For each of these, I've created an aggregate extract of the main extract above, so after I've run the Excel to Access query, refresh the main extract with an incremental refresh, I update these dashboards with their own refresh.

       

       

      I love Tableau, but all this seems incredibly complicated, and I'm hoping there's a better way!  I'm limited to using Access to store data locally.

       

      Any help/advice would be greatly appreciated.