1

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

  • How many users are using SSAS? It's quite common for data assets to get "rusty" - they aren't maintained, no one uses them, but no one has the leadership to turn them off. I suggest you stop the SSAS cubes for two months and see how many complaints you get (Alternatively you can be more managed about it and check with users etc.). Technically, Power BI is at the stage where standalone it can replace small SSAS implementations, but you do need to remodel everything (I assume you are using SSAS multidimensional). – Nick.McDermaid Apr 13 at 1:39
  • If you are using enterprise SSAS then you might find the models are too big to migrate to just Power BI. But really it's dead effort migrating seldom used SSAS Mutlidimensional models to a new version of SQL Server - try to decomission if you can. – Nick.McDermaid Apr 13 at 1:40
1

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.