Do I still need OLAP cubes after a SQL Server Upgrade
on this link: A quick overview of SSAS for DBAs you can find relevant information about SSAS that can help you to see why there are so many advantages using SSAS.

You also need to manage permissions, as you mentioned Power BI, SSAS interacts very well with Power BI, in many environments I restrict the Power BI views by managing the permissions using AD and SSAS.

SSAS also needs maintenance like backups and integrity checks, so yes, there is an overhead if you want to maintain it properly, and include a recover strategy.

You mentioned columnstore indexes and SSAS cubes and you can find a similar question here.

this was a general insight. If you put more focus on your question, you will get more objective and detailed answers.