6 Replies Latest reply on Oct 6, 2016 7:05 AM by Violeta Morse

    Summary table for roll up and drill down in Tableau

    Violeta Morse

      Hi, I'm working with a very large table (70mil records, 100+ columns and growing) used in tableau reports which are slowing down as the table size grows. The data in the table is at the lowest granularity so all calculations happen in real time and take minutes to complete (which means very slow report generation). I'm not sure why it was designed the way it is (have used tableau for a total of 2-3 weeks now) but hope there is a way to avoid real-time calculations that take minutes. My gut was to create an aggregate table and rewrite the tableau reports using it as a source. Tested it out - aggregated the data to a higher granularity (size went down from 70 mil to 1 mil rows), and with the new source report generation time is down to 1 sec. All good for the base report, until I tested the data with filters and realized that this approach works only at the granularity chosen for the summary table - summing up at a higher level results in double-counting some records that fall into more than 1 category.

       

      Here is a simplified example:
      'YEAR' - 'RETAIL CHAIN' - 'STORE' - 'STORE BRANCH' -  'LOCATION' - COUNT(Distinct EmployeeID)

       

      In the summary table this gives us correct distinct number of Employees per Store Branch (the lowest necessary level), but rolling up to Store or to Retail Chain inflates the counts since employees can work at more than 1 Store Branch or Store. For example, getting the distinct number of employees only for Stores 1, 2 and 3 can't be done by summing up the counts because of possible overlap.

       

      How can I aggregate data in advance in a way that it allows accurate roll up and drill down in Tableau ( there are lower levels of granularity that are definitely not necessary)?

       

      Thank you!

      Vily