What is the best approach between AAS Tabular model or SSAS Multidimensional in Azure Data Platform with Azure Synapse

0
votes

We have on prem SQL Server Analysis Services (SSAS) multidimension with lot of custom complex calculation, lot of measure group, complex model with many more features. We process per day few billion rows and have custom Excel add-in to connect custom pivot as well as standard Pivot table functionality used to create reports, run ad-hoc queries etc. and many more.

Below are the possible solutions in Azure

Approach 1: Azure Synapse, SSAS Multidimensional (ROLAP), Excel and Power BI. Note that SSAS Multidimensional will run as IaaS which will be host in VM. Desktop excel/excel 365 will be able to connect and Cloud Power BI.

Approach 2: Azure Synapse, Azure Analysis Services Tabular model direct query, Excel and Power BI. Desktop excel/excel 365 will be able to connect and Cloud Power BI.

Question: Which approach will be based on the huge data volume, processing, complex logic, maintenance and custom calculation?
Can users access these cloud-based data cubes specially SSAS multidimensional either via their desktop Excel or via Excel 365? How will be the performance ROLAP vs DAX on direct query mode? What will be cost of moving and processing fairly large amounts of data?

1
azuressasssas-tabularazure-synapse
How large is your data? How large are your dimensions? How frequently does the data in Synapse change? – GregGalloway
@GregGalloway - Data is around more than 12 TB. Having more than 20 dimensions. Few dimensions are having more than 400millions rows. Every 1 hour data change in Synapse. – Durgadas

1 Answers

1
votes

With 12TB of data you will probably be looking at 500 - 1200GB of compressed Tabular model size unless you can reduce the model size by not keeping all of history, pruning unused rows from dimensions, and skipping unnecessary columns. That’s extremely large even for a Tabular model that’s only processed weekly. So I agree an import model wouldn’t be practical.

My recommendation would be a Tabular model. A ROLAP Multidimensional model still requires MOLAP dimensions to perform decently and your dimension sizes and refresh frequency will make that impractical.

So a Tabular model in Azure Analysis Services in DirectQuery mode should work. If you optimize Synapse well you should hopefully get query response times in the 10-60 second range. If you do an amazing job you could probably get it even faster. But performance will be largely dependent on Synapse. So materialized views, enabling query resultset cache, ensuring proper distributions and ensuring good quality Columnstore compression will be important. If you aren’t an expert in Synapse and Azure Analysis Services, find someone who is to help.

In Azure Analysis Services, ensure you mark relationships to enforce referential integrity which will change SQL queries to inner joins which helps performance. And keep the model and the calculations as simple as possible since your model is so large.

Another alternative if you want very snappy interactive dashboard performance for previously anticipated visualizations would be to use Power BI Premium instead of Azure Analysis Services and to do composite models. That allows you to create some smaller agg tables which are imported and respond fast to queries at an anticipated grain. But then other queries will “miss” aggs and run SQL queries against Synapse. Phil Seamark describes aggregations in Power BI well.