You are likely misusing your BI tool, and trying to bend it to do something it wasn't meant to do. This is a typical symptom of missing well-defined core business data models. Most of the time spent building the report is used on basic data cleaning instead of analysis. I remember my days with Periscope - in theory, it was meant for last-mile analysis and charting. But we had to use it for data modeling, creating these middle layers of clean data we would materialize.
Naturally, the tool wasn’t bending to it, and there were many delays, and sometimes the data would not even refresh. We should have done the heavy lifting elsewhere and not used the shared resources.
I am biased, but I 100% believe in separating analysis and data “prepping” into their own domains.
By performing data transformations outside of your BI tool, you can speed up data processing, reduce the burden on the tool, and improve overall data quality. You also maintain control over business-critical data definitions and are one step closer to achieving data enlightenment - the semantic layer that powers all your data questions.
You can customize the process to meet your needs and ensure your data is clean and accurate. Here however, it’s essential to understand your own data journey - do you REALLY need heavy customization, or can predefined templates solve 80% of your questions?
The logical question is then where and how. Products like dbt have pioneered the idea of having a dedicated transformation layer. Recently Google/Looker announced doubling down on Looker Modeler, a way to build the semantic layer outside Looker itself.
In conclusion, data transformation is a critical step in the data analysis. While many BI tools offer data transformation capabilities, it's best to do this work outside the tool.
Simple transformations, pre-aggregation, window functions, data pivoting and melting, and pre-joins are best performed outside a BI tool. Doing the transformation work outside of a BI tool allows you to maintain control over your data and enable reusing it easier.
It also has an unverified impact on your team's motivation and stress levels ;-)