Live Connection to Database for Excel PowerQuery?

Felix Reyes 0
Viewed 22

I currently have approximately 10M rows, ~50 columns in a table that I wrap up and share as a pivot. However, this also means that it takes approximately 30mins-1hour to download the csv or much longer to do a powerquery ODBC connection directly to Redshift.

So far the best solution I've found is to use Python -- Redshift_connector to run update queries and perform an unload a zipped resultset to an S3 bucket then use BOTO3/gzip to download and unzip the file, then finally performing a refresh from the CSV. This resulted in a 600MB excel file compiled in ~15-20 mins.

However, this process still feel clunky and sharing a 600MB excel file among teams isn't the best either. I've searched for several days but I'm not closer to finding an alternative: What would you use if you had to share a drillable table/pivot among a team with a 10GB datastore?

As a last note: I thought about programming a couple of PHP scripts, but my office doesn't have the infrastructure to support that.

Any help would or ideas would be most appreciated!

large-files large-data amazon-redshift excel
1 Answers

Call a meeting with the team and let them know about the constraints, you will get some suggestions and you can give some suggestions

Suggestions from my side:

For the file part

reduce the data, for example if it is time dependent, increase the interval time, for example an hourly data can be reduced to daily data if the data is related to some groups you can divide the file into different parts each file belonging to each group or send them only the final reports and numbers they require, don't send them full data.

For a fully functional app:

you can buy a desktop PC (if budget is a constraint buy a used one or use any desktop laptop from old inventory) and create a PHP/Python web application that can do all the steps automatically create a local database and link it with the application create the charting, pivoting etc modules on that application, and remove the excel altogether from your process you can even use some pre build applications for charting and pivoting part, Oracle APEX is one examples that can be used.

usmanhaq0
Related