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

More Info

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

If you currently run SSAS on Amazon EC2, you can reduce costs by utilizing SSAS directly on Amazon RDS for SQL Server, effectively consolidating workloads to operate on the same RDS DB instance as your SQL Server database. However, it’s important to consider potential performance ramifications when consolidating.

Online Analytical Processing (OLAP) solutions improve data warehouses and other relational databases by reducing the need for real-time processing, facilitating quick and efficient analysis and reporting. This efficiency is largely achieved through the preprocessing and storage of a variety of combinations of dimensions and hierarchies before analysis.

SSAS serves as a Microsoft business intelligence tool for crafting enterprise-level OLAP solutions. Beyond optimized analytical queries and calculations, SSAS delivers semantic data models necessary for client applications such as Excel, Power BI, SQL Server Reporting Services (SSRS), and various other reporting and data visualization platforms. The Tabular mode is designed for in-memory databases, enhancing column-based query efficiency and providing superior data compression. Conversely, the Multidimensional mode employs cubes and dimensions that can be annotated and extended to accommodate intricate query constructions.

This article will guide you through the process of configuring and utilizing SSAS in either Tabular or Multidimensional mode on Amazon RDS for SQL Server database instances. For more detailed insights, you can also check out this blog post.

Configuring SSAS on Amazon RDS for SQL Server

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

• Your Amazon RDS for SQL Server instance must be either the SQL Server Standard or Enterprise editions under the specified engine versions:

• The instance must be connected to an AWS Managed Microsoft Active Directory to enable Windows Authentication. Refer to the guide on Setting Up Windows Authentication for SQL Server DB Instances for detailed instructions.
• Amazon S3 integration must be enabled for transferring SSAS models and backups between the instance and an S3 bucket. Ensure you have a valid login for the Microsoft SQL Server for Amazon RDS instance with the necessary permissions to download files from Amazon S3.

Creating an Option Group with the SSAS Option

For detailed steps on creating an option group with the SSAS option, see “Working with Option Groups.” Here are the key steps involved:

1. Name the option group SSAS.
2. Specify the maximum memory percentage that SSAS can occupy on your Microsoft SQL Server for Amazon RDS instance. Since SSAS Tabular is memory-intensive, a higher max memory allocation can impact SQL Server operations on the same instance, so consider the total memory available.
3. Define the SSAS server mode. Note that only one mode can be used at a time. To switch the mode or remove the SSAS option, you must delete all existing SSAS databases to avoid errors. If the Mode option is absent, your major engine version may not support Multidimensional mode. The default is Tabular.
4. Choose an existing security group or create a new one. Ensure that the security group allows inbound traffic on the SSAS port.

Connecting to SSAS

Once you have added the SSAS option, you should be able to connect to SSAS on the RDS instance. Follow these steps:

1. Launch SQL Server Management Studio (SSMS).
2. From the File menu, select “Connect Object Explorer.” A window will appear for server connection.
3. Choose “Analysis Services” as the server type.
4. Enter the endpoint for your RDS instance as the server name.
5. Select “Windows Authentication.”
6. Click “Connect.”

After a brief moment, SSMS should connect to SSAS on your DB instance. If you encounter connection issues, verify that the instance’s security group allows traffic from your computer on port 2383.

Deploying and Processing SSAS Projects

Direct deployment of SSAS projects from 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 a new project in SQL Server Data Tools (or open an existing one).
2. Establish the necessary roles in your project, adding domain members to those roles. Ensure that domain users can connect to SSAS on the RDS instance to perform operations on the deployed model.
3. In the Solution Explorer, select the solution and access properties.
4. Under Deployment Options, choose “Do Not Process” for Processing Option. After model deployment, you can process it directly through SSMS.
5. Build your project in Solution Explorer. During the build, SQL Server Data Tools generates the file in the project’s output folder (default is Bin). You’ll need the .asdatabase and .deploymentoptions files for deployment.
6. Create or use an existing S3 bucket (e.g., sample-s3-bucket).
7. Upload your project files to that bucket, ensuring Amazon S3 integration is enabled for the instance.
8. Open SSMS, connect to your RDS SQL Server instance, and execute the following stored procedure to download 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;

9. Monitor the status of your Amazon S3 integration tasks by calling the rds_fn_task_status function until the status indicates SUCCESS. This function takes two parameters; the first should be NULL and the second is the task ID. If you provide 0 as the task ID, it will display all tasks:

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

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

For additional helpful resources, consider exploring this excellent resource.

SEO Metadata: