Problem
Organizations working with on-premises SQL Server Analysis Services may not be able to guarantee optimized deployment of their models and secure and compliant data, especially during peak usage. The more companies invest in hardware and software licenses, the less the deployment of on-premises SSAS solutions becomes attractive, especially for smaller organizations with limited or narrow budgets.
Solution
To overcome these challenges, Microsoft introduced Azure Analysis Services as a cloud-based solution with the following advantages:
- No need for organizations to manage their infrastructure. Microsoft takes care of tasks like backups, security patches, and hardware maintenance.
- Ability of automatic scaling and high-performance capabilities to handle large datasets and complex queries.
- Possibility of integration with Azure Active Directory with the introduction of the role-based access control technique.
- A pay-as-you-go pricing model to scale resources according to actual usage.
In this article, we will focus on the differences between Azure Analysis Services and On-Premises SQL Server Analysis Services and try to understand the main features, benefits, and considerations for different use cases.
Overview of Azure Analysis Services (AAS) and On-Premises SSAS
AAS is a fully managed platform as a service (PaaS) from Microsoft Azure that provides enterprise-grade analytics in the cloud without the requirement of managing the infrastructure.
On the other hand, SQL Server Analysis Services (SSAS) is part of the SQL Server Business Intelligence suite, offering both multi-dimensional and tabular models for on-premises deployment, but has the requirement of hardware dedication and administration.
Deployment and Infrastructure
One of the most significant distinctions between AAS and on-premises SSAS is the deployment model and infrastructure management.
Azure Analysis Services (AAS)
AAS is a fully managed service, meaning the consumer doesn’t need to bother handling the underlying infrastructure and maintenance tasks. The deployment process is streamlined, and users can scale resources as needed with just a few clicks. This makes it highly convenient for organizations without dedicated IT teams or those who prefer to focus on analysis rather than infrastructure management. AAS still benefits from Microsoft’s continuous updates and patches with a focus on security.
On-premises SSAS
On-premises SSAS requires dedicated hardware and infrastructure planning and must ensure the setup, including maintaining servers, configuring load balancing, and handling backups. This is proof that the approach in question may provide full control of the environment, but we should not deny that it requires IT resources, especially technical expertise.
Data Sources and Connectivity
Azure Analysis Services (AAS)
AAS supports a wide range of data sources for on-premises and especially rising cloud products like Azure SQL Database, Azure Data Lake Storage, and Azure Synapse Analytics. A common detail to mention with Power BI is that AAS includes Power Query for data ingestion from both on-premises and cloud sources, which makes its data integration capabilities more powerful.
On-premises SSAS
Even if many experts claim that the focus of Microsoft nowadays is on AAS, the last updates still provide improvement for connectivity for the on-premises SSAS, like relational and non-relational databases, data warehouses, and other data sources of the same category. However, we should know that integrating on-premises SSAS with cloud-based data sources might require additional configurations and considerations.
Data Modeling and Development
Azure Analysis Services (AAS)
AAS primarily supports only tabular models. Multi-dimensional models are supported in AAS; the only way is to deploy SSAS in Multi-dimensional mode to an Azure VM.
AAS provides an efficient tabular model that utilizes in-memory processing to deliver fast query performance. This model is ideal for self-service analytics and is relatively easier to develop and maintain compared to the multi-dimensional model.
On-premises SSAS
The on-premises SSAS offers both Multi-Dimensional and Tabular models.
Multi-Dimensional Model
- This model works great for big, complex data analysis in businesses. It uses the OLAP engine and the MDX language to query large amounts of data. Multi-dimensional databases treat each attribute of a piece of data as a “separate” dimension. The engine can then locate the intersection of the dimensions and display them. This makes it possible to analyze and compare data in different ways. Attributes can also be separated into multiple sub-attributes. Multi-dimensional databases are opposed to two-dimensional relational databases.
Tabular Model
- Like AAS, on-premises SSAS also supports the tabular model, which has become more popular nowadays. Definitions are available in the previous section.
Performance and Scalability
Azure Analysis Services (AAS)
AAS benefits from Azure’s robust infrastructure. This means it can manage large amounts of data smoothly, ensuring users have a consistent experience.
On-premises SSAS
On-premises SSAS performance relies on the hardware and resources allocated to the server.
Important data volumes and complex queries require careful planning and additional costs.
Security and Compliance
Azure Analysis Services (AAS)
AAS leverages Azure AD for authentication and access control enforced by role-based access control (RBAC) effectively.
On-Premises SSAS
On-premises SSAS supports Windows authentication and can be integrated with Active Directory for user authentication and role management. The setup process for security might require more effort compared to AAS.
Cost Considerations
Azure Analysis Services (AAS)
AAS follows a pay-as-you-go model, meaning that users pay only for the resources they consume hourly. This is considered more cost-flexible as users can scale up or down resources based on actual usage.
On-premises SSAS
On the other hand, the on-premises SSAS requires an investment focused on the hardware, software licenses, and ongoing maintenance. For that purpose, companies must establish strategies for hardware depreciation, software licenses, and even human resources expenses in their cost considerations.
Data Refresh and Availability
Azure Analysis Services (AAS)
Performing automated data refresh in AAS can be achieved with three alternatives.
Refresh with REST API
- With the REST API for AAS, it is possible to set off data refreshes to run in the background. The client apps don’t need to keep long HTTP connections open since the REST API also includes helpful features like automatic retries and grouped updates to make things more reliable.
Refresh with Logic Apps
- Considered a serverless solution scaling with the needs, with Logic Apps, it is possible to call the AAS API to perform the data refresh. Logic Apps also have built-in error handling and retry mechanisms, making the refresh operations more resilient.
Refresh with Azure Automation
- Azure Automation is another option to perform the refresh process for AAS by running runbooks set up on a schedule or trigger by invoking the Analysis Services API to manage the refresh tasks.
- Considered scalable and flexible when it comes to customization, Azure Automation also includes features for error logging with the ability to retry failed tasks.
On-premises SSAS
Refreshing data in an on-premises SSAS setup typically involves a series of automated or manual steps to ensure that the analytical models are up to date with the latest data. This process can be scheduled within SQL Server Agent or managed through custom scripts in PowerShell that interact with SSAS using commands or directly with AMO and TOM libraries. For more dynamic control, users might employ Integration Services packages that orchestrate the refresh alongside other ETL (Extract, Transform, Load) tasks. Error handling, notifications, and logging are crucial to quickly identify and resolve any issues that may arise during the refresh process.
Conclusion
In conclusion, both Azure Analysis Services and On-premises SQL Server Analysis Services offer robust capabilities as analytics solutions but answer to different needs and scenarios. While AAS is more ideal for organizations with the need of a fully managed cloud-based solution, the on-premises SSAS is more suitable for projects with specific compliance requirements.
Next Steps
Check out these related articles
