I too have spent quite a few years working in this area. I have primarily been involved in building tools to make this kind of analysis easier - but it is very difficult.

As you mention, the primary issue is that almost all business data is very complex. There are very few areas (in my experience), where simple queries offer much value, because there are so many niggling business rules that lead to exceptions and counter exceptions to rules.

This means that "self service" analytics are pretty much impossible. There are often very few people in an organization who actually know how to define even seemingly simple measures like profit or revenue.

This means you need consultants who can learn the business rules, and who also understand the data platform. This makes these projects expensive, and makes it hard for platforms to win in the market.

For the most part in my experience, the best way forward is to choose a database engine (MySQL, Postgress, Oracle, DB2, SQL Server), get the data into it (itself difficult!), then write a bunch of SQL to incorporate the business rules. Then if you are feeling fancy (and still have budget!), built a nice HTML front end on it.

Its not very sexy, but it does work. I would love it if there was an easier way, but I am yet to find it.