Can you explain your data source and the volume of data you're talking about so we can understand a bit better? How large is the extract?
It sounds like what you're trying to achieve is something like; Connect to transactional data > create aggregations / sums in tableau > export as extract > create new reports on the aggregation. Is my understanding correct?
I work for a travel company and we record each trip and lots of information about it. We create a tableau server datasource. By way of an incremental extract and using custom sql it produces a row for each trip and the key dimensions and metrics relating to it.
For some parts of the business this level detail is needed. Lets use an example of wheel chair bookings which we couldn't accommodate for, we would need to investigate why and ensure it didn't happen again.
This level of detail is not necessarily required for say the CEO, who only needs to know total number of bookings on a daily basis.
The data is currently sat at 28 Million rows, adding about 4 milion each month (Currently in the process of appending it) 70 columns. 4 gig in terms of size.
What i was hoping to do was use the tableau server data source -> Create aggregations on all visible Dimensions (in this case Bookings by day) -> Build reports + publish data source
When the CEO looks at a daily report of bookings then it only reads this number rather than countd booking id for the day (having to go through all records to calculate this), therefore increasing the report load time as this is a major problem.
This is only a simple example but it highlights my problem.
I hope this makes sense
Hello Ashley - Have you tried the "Aggregation" option during Tableau Extract before ? You might get a solution for this as well. Depending upon your requirement, this might help. Otherwise you need to perform the custom rollup at database level and then pull data to Tableau from that Table.
Hey Manideep, thanks for the response
this is what I hoping to do on the tableau server datasource, almost an extract of an extract.
I can seem to get to this point (in your picture), complete the aggregation but then i can't publish the new datasource to the server.
we are looking at building a WH but at the moment i can't touch do anything in the DB where the data is held, only read from it.
I don't think you're going to be able to create an extract of an extract as such, I think you'll at least need two sources;
Source 1 as you have it now for your transactional needs
Source 2 - a new source created using the aggregate process as suggested by Manideep above or using a different query to the database via custom SQL to force the aggregation into the DB layer.
Hello Ashley - What is the error you are getting while publishing ? Try to hide unused columns from the data source and then try to extract from Live source.
That unfortunately is the resolution I am coming to.
My only problem with this is that i am worried about bandwidth/resource restrictions from the database I am hitting, but I will try. Thanks for your help though in trying to solve my problem.
I am not receiving an error as such. I am receiving complaints about "The circle of death" which happens when it is waiting on the load for reports.
I have attempted all of these tricks, filters, hide fields etc.
Thanks though for all your help, i will attempt to rewrite the query at an aggregate level and will update