Hi all, I may be approaching this entirely wrong, but I think I need some help steering the right way.
I have a big database of transaction data - about 3 years worth, and with the associated things like customer account details. There are 3 different tables with transaction details in them for which they’d like to see things like first and last transaction by customer/time-period
I have to create dashboards that allow my business users to view reports on this data by day/week/month/year/to-date (eg. year to date) and over all time.
What feels like the efficient thing to do, is to import all that historic data once, everything precalculated, then join it with the recent, updating data in Spice - but I can’t see a way that is possible. The closest I can do is create the archive tables in Redshift, create an identically structured view from the recent, mutable data, union them in Redshift, and import that into Spice - but I don’t know if that’s great, if Spice will actually be able to only do an incremental update in this case?
Any recommendations? I can’t be the only person with a load of basically static historic data, along with recent, updating data, which I want to be able to visualise together?
There is different approaches to do this simple task.
Approach 1. (If data size if small)
Create a view based on these three table in your database. ( if your database allow to do this I mean if the table have huge data.).
Make a connection with Quicksight.
Import all data and implement the schedule refresh, SPICE will handle it.
Approach Two. (If data size if big)
Write an extract jobs in CSV, and use the S3 as a source with Quickssight.
You can try AWS Glue for IT. if you have Redshift in place you can sync data on it and use with Quicksight.
Note:- if you have replica of your production database you can use it.
It’s big, and growing so option 2 is the way - but I didn’t think you could join S3 and redshift data - or is the suggestion a job that exports the historical just the once, then adds subsequent CSV files for the latest/mutable data?
Hi @Catherine,
It’s been awhile since this thread has been active. Were you able to find a solution for your initial topic from Naveed’s suggestion or did you have any additional questions?
If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.
Hi @Catherine,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for any relevant information that may be needed.
I mean if the table have huge data.).