I need a data processing and visualization tool that will allow me to
- pull data from sources (sql-oracle, sql-server, API, excel files, one-drive) or just grab files from an FTP
- process that data (perform calculations and joins between datasets, filtering, sorting, replace old files, etc.)
- visualize that data and present it on a web page
- allow diving into data underneath the top level presentations
I also need the tool to be nimble enough that when a user requests a modification, it won't be too difficult to change on a regular basis. We have a tool in place currently but the problem is it's kind of a black box, only our contractors know how to use it and it's a small unknown tool so we're pretty much stuck relying on these contractors for development help. We have capable IT staff but no one is really willing to learn this black box tool.
I have looked at tools like Power BI and a couple others and it looks like all these tools only allow simple joins by keys (join where y.key = x.key). But I need complex joins. For example, I want to summarize the values in table y where y.time is between x.start and x.end. This is easy to do within one database, but I need it done between datasets on different databases. It doesn't look like Power BI or other programs like it can do that. Any suggestions?