I've implemented self-serve analytics at three organizations successfully. It's important your data is well organized, well labeled/defined, and the BI tool needs to allow you to configure guardrails around how data is queried. I have used Looker as the BI tool at all three organizations. End users do not need to even define joins. They just select the grouping and aggregate columns they want to return and click run, then configure their visualization from the data. It is true that some users are not data literate and still will not run their own queries, but from my experience a lot of non-technical business users love being able to easily explore data themselves, and the business receives a lot of value from it.
I was always told it's because as you age a year becomes a smaller portion of your life. When you are 35 a year is 1/35th of your life, compared to 1/5th as a 5 year old.