7 Replies Latest reply on Jan 31, 2017 4:00 AM by Dovi Lilling

    Unable to Union Multiple Published Extracts

    Paul Merrill

      I have built a viz to help analyze claim data. We process many thousands of claims a day, so looking at 6 years worth of historical data requires a data set with over 40 million records.

       

      Our back end is SQL 2008r2. We have a single table that holds this data. I created a single extract to pull everything from the table and published the extract to the server. This helped improve the speed of using the workbook, but as you can imagine the extract takes several hours to refresh. We need a daily refresh of this data, so I had to find a way to speed this up.

       

      My solution was to break up the extract into multiple extracts and find a way to union them in the workbook. This way we can limit our daily refresh to only those extracts that are for the current and previous fiscal year. All claims before the previous year are closed and the database records can not be updated so there is no need for those extracts to ever refresh.

       

      The problem I ran into is that there does not seem to be a way to union those datasets back together in the workbook. The only feature I found that can do something similar is to connect to one published extract and then choose "Append From Server" to add rows from another published extract. Unfortunately, this is a one time, manual command that simply adds every row from the second extract to the first. It does not tell the workbook to define the relationship between the two extracts as a union.

       

      Ok then. So my next step will be to suggest we decide that this data should be analyzed on an aggregated scope (maybe monthly). Before I make that suggestion I wanted to put this question to the community to ask:

      1. Is there a way to define a UNION relationship between multiple published extracts?
      2. Is there a better way how to handle daily refreshes of such a large amount of data?

       

      Thanks, Fellow VizMates!