With big data warehouse, more often than not, the fancy dashboards would not run fast enough without a dedicate pipeline that materialize data before hand. Too many useless dashboards and your data team would not have enough manpower to do anything else other than operating the existing pipelines. As a result, new dashboards become slower than old dashboards due to lack of maintenance/optimization, and everyone complain about the poor performance.
A lot of what users need can be done this way in my experience. Also giving their people access to customise reports/exports can go even further. I'd advise against direct access to “real” production though: have the dashboard and custom reports run against a (probably readonly) replica of some sort¹ so runaway reports don't impact live app performance.
[1] via replication, log shipping, etc... — all major databases should support at least one method that can achieve this with minimal latency in updating the replica under normal conditions
In my experience, no, as the company gets to any complexity and size. The production DB is often optimized for use by the ORM/language of choice or there's twenty of them for microservices. This makes it really awkward to do some things in SQL. So now your engineering team is getting requests to keep changing the prod DB schema so analytics can be done more easily. More over the prod DB is likely 10% of the data your business needs. There's a reason companies like Fivetran exist to slurp fifty data sources into a warehouse. Marketing wants google and facebook and mailchimp data. Operations wants saleforce data. Finance wants stripe data. And they want all of this tied to the inhouse data.
Sure you can make reports in each of those services but then you need to tie them together and then people complain of differences in metrics and then you have a bad time.
edit: Not to mention analytics on what users do. Always fun when there's a billion row table in your production db for that.
I was generally able to create better results via power bi, ssrs, or shiny
* Provide a clean minimal view of the data that other teams can pivot table on top of or download in the BI tool. We do onboarding, support and all that for this data.
* Provide dashboards and reports for more complicated ongoing questions that require data that's not cleaned yet.
We don't use Looker but based on the sales demo it seems optimized for this type of workflow. The core analytics team maintains the data and complex workflows while other teams have business analysts for day to day questions.