I am in the process of creating a data warehouse using SQL Server for my company.
I have created a POC with a simple SQL Relational database with a few(about 10) stored Procs for the ETL process.
Now that I have business buy in to step it up to a production state, where I will be tripling the data, it's the right time to ensure the technology I use is best suited for our needs and is aligned with general best practices.
We will be processing about 500k records per day, with a maximum guess at about a million a day. The POC then aggregated all this data down into 5-minute chunks per client per day for the last 4 years. Total being a bit over 500k time slots, for each fact (POC had 4 fact tables) for each customer (Lets plan for 25). So for the POC, we are looking at approximately 50 million rows across all the facts by client and time. That leaves final views of the data at minor amounts, given that the last 6 months satisfies 85% of all questions, that means we should be sitting with result sets of approx 250k rows for 85% of the time if not less. Given that I expect to triple data, let's call it a maximum of 1 Million rows to satisfy 85% of queries, split across about 8 to 10 fact tables with about 4 or 5 dimension tables.
With that all said and done, as mentioned, I am in a very fortunate position where I can now choose the technology that is used for the backend of the warehouse. I have never really had the opportunity to really get entangled in SSAS and feel like its a technology I am lacking.
Is SSAS still a popular choice for Data Warehouses or has its popularity declined over the past few years? Is it still used as much as it was 5 years ago?
SHORT VERSION: Is SSAS still something worth learning/investing time and effort into?
We are currently on SQL 2016 and this will be built on SQL Server 2017
Thank you for all your input.
Best Answer
In my opinion, yes - it is still a technology that is completely valid. Even more so if we are discussing Tabular rather than Multi-Dimensional SSAS implementations. For example, VertiPaq (the database engine that runs Tabular Models) is the same engine that can be found in PowerBI and any skills you learn using SSAS/TOM/DAX will help you if you ever decide to learn that as well. Its also good to note that PowerBI, Excel, SSRS and many other tools can connect to SSAS/Tabular to obtain data to build dashboards and reports.
I personally have been using SSAS/Tabular since early 2014 and I have already rolled out 3 large-scale implementations (I'm currently working on my 4th). My current company uses SSAS/Tabular as the Analytics Engine for its core product. It can be integrated with many other products other than just SQL Server by using PowerQuery to obtain data from other sources.
If all you were planning to do was to have a data warehouse with a star schema (or even snowflake schema) then Tabular is a good choice as it offers fantastic performance (hardware dependent) and amazing compression. You should be aware though that it is a pure in-memory analytics engine, so if you choose to go down that route, be prepared to procure lots of RAM. To estimate how much RAM you will need, a typical Tabular installation will be 1/10th the size of the originating data (subject to good modelling principles).
I have a Tabular Model which contains circa 800 million rows of data in the main fact table and around 16 million rows in each of the dimension tables. It takes up about 48GB of RAM, but queries against it are sub-second when applying a filter and less that 30 seconds when just dumping the entire table. Unlike SQL Server, partitioning in a Tabular Model does not speed up the query as the data is not on disk, but it does speed up your ETL process as you can load individual partitions.
With all that being said, I still have a SQL Server data warehouse which stores data that I don't want in my Tabular Model. As with any technology, choose the right tool for the job. If you want something that can give you high performance analytics Tabular is a great choice, but does require you to learn a few more technologies (C# & PowerShell are a must in my opinion as they help with deployment, management and builds). However, if you don't have the budget, resources or need for the features that a Tabular installation offers, then a traditional data warehouse or a multi-dimensional SSAS installation may be the better way to go.