After our DBA got sacked for poor performance, I am now our accidental DBA. I have been pushing to upgrade our SQL Server instance from 2012 to 2017 so I can use the clustered column store indices in our warehouse and downgrade from enterprise to standard at the same time.
We also had been paying a lot of money for enterprise analysis services. We have about SSAS databases that are used in a handful of PowerBI reports and remain mainly unused. I wanted to ask the community if people still find a need for OLAP cubes with the ever-increasing performance improvements in SQL Server and other databases in general.
Im sure there are many variables involved including volume data of data and the number of queries but does anyone have any general insights?
Thanks
Best Answer
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.