Setting Up Microsoft SQL Server Analysis Services on Amazon RDS for SQL Server

More Info

As of January 7, 2022, this article has been updated to reflect the addition of Multidimensional mode to the Amazon RDS for SQL Server SSAS solution. You can now set up Microsoft SQL Server Analysis Services (SSAS) in either Tabular or Multidimensional mode using Amazon RDS for SQL Server. SSAS operates within a Single-AZ configuration for both Standard and Enterprise editions across the 2016, 2017, or 2019 SQL Server major versions.

If you are currently using SSAS on Amazon EC2, shifting to Amazon RDS for SQL Server can help reduce costs by consolidating your workloads on a single RDS DB instance, which also hosts your SQL Server database. However, it’s crucial to consider potential performance impacts that may arise from this consolidation.

Online Analytical Processing (OLAP) solutions enhance data warehouses and relational databases by reducing the need for on-the-fly processing, facilitating quick and effective analysis and reporting. This is largely achieved by preprocessing and storing various combinations of dimensions and hierarchies in advance, making subsequent analysis more efficient.

SSAS serves as a business intelligence tool by Microsoft, aimed at developing enterprise-level OLAP solutions. Beyond optimizing analytical queries and calculations, SSAS also provides semantic data models that client applications, such as Excel, Power BI, SQL Server Reporting Services (SSRS), and other reporting tools, require. The Tabular mode is designed for in-memory databases that support efficient column-based queries and offer superior data compression. Conversely, the Multidimensional mode utilizes cubes and dimensions that can be annotated and extended to accommodate complex query structures.

This article will guide you through the configuration and utilization of SSAS in either Tabular or Multidimensional mode on Amazon RDS for SQL Server DB instances.

SSAS Configuration on Amazon RDS for SQL Server

To set up SSAS on Amazon RDS for SQL Server, the following prerequisites must be met:

1. The Amazon RDS for SQL Server instance must be running on either the Standard or Enterprise editions, under the specified engine versions:
   • For Tabular mode: 13.00.5426.0.v1 and above, 14.00.3223.3.v1 and above, 15.00.4043.16.v1 and above
   • For Multidimensional mode: 13.00.5882.1.v1 and above, 14.00.3381.3.v1 and above
2. The instance must be integrated with an AWS Managed Microsoft Active Directory to enable Windows Authentication. For more information, refer to the instructions on Setting Up Windows Authentication for SQL Server DB Instances.
3. Amazon S3 integration must be enabled to facilitate the transfer of SSAS models and backups between the instance and an S3 bucket. Ensure you have a valid login for Microsoft SQL Server on your Amazon RDS instance, along with permissions to download files from Amazon S3. For detailed steps, see Integrating an Amazon RDS for SQL Server DB Instance with Amazon S3.

Creating an Option Group with the SSAS Option

To create an option group with the SSAS option, follow the steps outlined in Working with Option Groups:

• For the Option name, select SSAS.
• For Max memory, specify the maximum memory percentage that SSAS can utilize on your Microsoft SQL Server for Amazon RDS instance. Since SSAS Tabular is memory-intensive, allocating too much memory could adversely affect SQL Server operations running on the same instance.
• For Mode, designate the server mode of SSAS. Note that only one SSAS mode can be used concurrently. To switch modes or remove the SSAS option, ensure all existing SSAS databases are deleted; otherwise, an error will occur. If the Mode option isn’t visible, your major engine version may not support Multidimensional mode (sqlserver-ee 15.00, sqlserver-se 15.00). The default setting is Tabular.
• For Security groups, select from your existing security groups or create a new one. Every DB instance must have at least one security group attached, and this group must allow inbound traffic on the SSAS port.

Connecting to SSAS

After adding the SSAS option, you should be able to connect to SSAS on your RDS instance. Ensure you’re logged into a domain-joined computer as a domain user. Follow these steps:

1. Open SQL Server Management Studio (SSMS).
2. From the File menu, select Connect Object Explorer… to open the connection window.
3. Choose Analysis Services for Server type.
4. Enter your RDS instance’s endpoint for Server name.
5. Select Windows Authentication for Authentication.
6. Click Connect.

If you encounter any connection issues, verify that the instance’s security group permits traffic from your computer on port 2383. For additional details, consult the Security Group Considerations.

Deploying and Processing SSAS Projects

Direct deployment of SSAS projects from SQL Server Data Tools (SSDT) to an RDS DB instance is not possible. Instead, you need to transfer your model files to the DB instance and execute the SSAS stored procedure.

1. Create or open a project in SQL Server Data Tools.
2. Set up the necessary roles within your project and include domain members in those roles. Ensure that any domain user who needs to perform operations on the deployed model is part of the project.
3. In the Solution Explorer tab, select the solution and access its properties.
4. Under Deployment Options, set Processing Option to Do Not Process. You can process your model directly through SSMS after deployment.
5. Build your project in the Solution Explorer tab. This will generate the required files in the output folder of the Analysis Services project (the default output folder is Bin). For deployment, you will need <project name>.asdatabase and <project name>.deploymentoptions files.
6. Create an S3 bucket (or use an existing one) for your project files. This article will use sample-s3-bucket.
7. Upload your project files to the bucket, ensuring Amazon S3 integration is enabled for the instance.
8. Connect to your RDS SQL Server instance via SSMS.
9. Execute the following stored procedure to download the project files from the S3 bucket to the local Amazon S3 folder on the instance:

exec msdb.dbo.rds_download_from_s3 @s3_arn_of_file='arn:aws:s3:::sample-s3-bucket/testmodel.asdatabase' , @rds_file_path='d:S3testmodel.asdatabase' , @overwrite_file=1
exec msdb.dbo.rds_download_from_s3 @s3_arn_of_file='arn:aws:s3:::sample-s3-bucket/testmodel.deploymentoptions' , @rds_file_path='d:S3testmodel.deploymentoptions' , @overwrite_file=1

10. To monitor your Amazon S3 integration tasks, use the rds_fn_task_status function until the status indicates SUCCESS. This function accepts two parameters; set the first parameter to NULL and the second to the task ID (passing 0 as the task ID will display a list of all tasks). Use the following code:

SELECT * FROM msdb.dbo.rds_fn_task_status(null,2);

11. Finally, call the SSAS_DEPLOY_PROJECT stored procedure to deploy the model into SSAS.

For further insights, check out more related content on this topic through this engaging blog post.