when providing SaaS how do you handle (big) customers that requests access to their data for BI applications?<p>most BI solutions like power bi struggles hard to obtain data from HTTP-APIs as they are paged, aggregated in other ways, authenticated with OAuth and so on.
integrations are very easy if access to a sql layer would be given instead.
But of course direct database access is also a struggle if using a multi tenant environment which would need at least some kind of row-level-security and a own read-replica. Also the customer needs to know every detail of the database schema which makes internal changes harder again and is an unnecessary detail.<p>Is there any other way then filling a second database event based with the correct „business-based“ schema (like a cache) and provide direct access to this? Is there any option to provide an „sql“ api to the application architecture?
In rough order of effort (and inverse order of intrusiveness to your environment):<p>You could use a view (or views) and a read-only user. The view could limit access to a single tenant and also abstract away changes to the schema if needed.<p>If you want more separation, you could use a read replica, again with a view.<p>You could also build an of ETL workflow to extract the tenants data, transform it if needed, and push it somewhere else.<p>Finally, make sure they pay for this. I'd scope out what they need and quote a price before doing anything except exploratory work.<p>We have an internal feature (we've yet to build) to offer a nightly database export of customers' data. We'll just send it to an s3 bucket they provide and let them take the data how they want. But this is a feature that will be restricted to our enterprise plans, most likely.
I would not add such access to your turnkey SAAS instrastructure. Perhaps you can offer a read-replica as an add-on but the headache will be massive when it comes to CI/CD with little ROI for most of your customers.<p>This is an opportunity, I think, for you to sell an Enterprise Appliance version of your application at 1000X the price. Such whales (customers willing to pay for custom BI) can host it internally, in a private-cloud or as a manged private-cluster (copy). If you go add-on replica route - then perhaps replicating data to Analytic Database with nosql type storage format will be ideal in solving schema problems and decoupling the access.
If they're that big can you provision their "own" copy of your infrastructure or at least the database?<p>And then foist handling database changes off on them.