Deploy tabular databases in SSAS on AWS RDS SQL Server

In this article, we are going to explore Analysis Service for AWS RDS SQL Server in detail.

Amazon offers various database services to support completely managed database infrastructure. We can deploy RDS for SQL Server, but it supports only database service until now. If we look at the relational database Microsoft SQL Server, it offers the following useful services as well:

SQL Server Analysis Service – SSAS
SQL Server Reporting Service – SSRS
SQL Server Integration Service – SSIS

But RDS limitation to database service was stopping a few users from migrating on it. Recently AWS announced that we can now run the Analysis Service on AWS RDS SQL Server as well.

Prerequisites

Supported editions

RDS supports SQL Server Analysis Service on the following SQL Server editions:

SQL Server 2016 Standard or Enterprise edition (minimum version:13.00.5426.0.v1)
SQL Server 2017 Standard or Enterprise edition(minimum version:14.00.3223.3.v1)

Follow the article AWS RDS SQL Server – Launching a new database instance to launch a new AWS RDS SQL Server instance.

You can verify the SQL instance edition in the RDS dashboard, as shown below:

Domain Joined RDS instance

Join this RDS instance with an Amazon managed Active directory.

Configure EC2 instance as a member of Domain

Create an EC2 instance, and it should be part of the same active directory.

For requirement 2 and 3, follow the articles below:

Once you follow steps 2 and 3, you can use the Windows authentication to connect to AWS RDS SQL Server:

Integrate an Amazon S3 bucket with AWS RDS SQL Server

We require an S3 bucket to transfer tabular database models, backups between the AWS instance and S3 bucket. I have already associated the SQLshackdemo bucket with my RDS instance.

In the below image, we can see the sqlshackrole role associated with the RDS instance for the S3 integration feature. This role should have permissions to do tasks in the S3 bucket for RDS instance.

Refer to the article Integrating an AWS RDS SQL Server with Amazon S3 bucket to go through the integration process.

SSMS installation

Launch EC2 instance and install SQL Server Management Studio to connect with RDS DB and Analysis Service instance

Visual Studio with SQL Server Data Tools (SSDT)

We require Visual Studio 2019 along with SQL Server data tools for creating database projects. You can install Visual Studio in the EC2 instance and enable the extension for the Analysis Service project.

Refer to the article Download SQL Server Data Tools (SSDT) for Visual Studio for more details.

Note: Complete all prerequisites specified so far in this article to go further.

Enable SSAS on the AWS RDS SQL Server

SQL Server Analysis Service is an Online analytical processing (OLAP) application. In a traditional SQL Server, we get two options to choose, Tabular or Multi-dimensional. RDS supports the tabular model.

To enable the Analysis Service in RDS, we need to add an option group in the RDS instance. In the RDS dashboard, click on Options groups. You can see existing options groups as well on this page.

Click on Create group to configure a new option group:

Create a new Option group for RDS

On this Create option group page, enter the name and description. We also need to select the SQL engine (standard, enterprise, express) along with the SQL version:

It creates the option group, but it is not configured for any services yet. We need to select the newly created option group and click on Add option:

Add SQL Server Analysis Service in the option group

In the option details page, select the SSAS option from the drop-down. It gives you brief information that this option enables SQL Server Analysis Services on the DB instance.

Analysis Service runs on port 2383, so this port should be allowed in the security groups (AWS firewall) from the EC2 instance. Refer to article security groups for your VPC for more detail.

We also need to configure the Max memory for the Analysis Service. You might be aware that Analysis Service is a memory-intensive application, and you might do bulk data processing, so max memory should be configured appropriately. Analysis Service instance runs on the same RDS database, so if we specify a high memory, it might impact memory available for RDS instance. I allocated 50% memory for the SSAS instance.

The RDS instance and options group must remain in the same VPC. You can verify the RDS instance VPC in the connectivity & Security tab:

Select the RDS instance VPC (in my case vpc-9c710fe6) in the security groups as shown below:

Scroll down, and you can schedule to add the option group immediately or on next scheduled maintenance window:

It adds the SSAS in the newly created option group, and you get a message for it as well:

Modify RDS instance to use SSAS option group

Now, lunch SSMS in EC2 instance and try to connect the Analysis Services using the RDS endpoint. We get the error message that connection did not succeed:

The above error comes because we have not assigned an option group for the RDS instance. Click on modify on RDS instance and select the option group (in my case SSASforRDS) we created earlier:

We can view the modification summary for the option group in the below image. Apply the changes immediately. It restarts the RDS instances and enables Analysis Services for the selected SQL instance:

Now, connect to SSAS service again from the EC2 SSMS using Windows authentication. We do not see any database in this instance by default:

Right-click on the connected instance and view the properties of the Analysis Server. You can see its hostname, Server mode, version, and supported compatibility level:

Create an Analysis Service tabular project for RDS

We will create a sample project for this demonstration. We already installed Visual Studio for creating projects. Go to start and launch Visual Studio 2019 and create a new project for Analysis Service tabular database:

Specify a project name, solution name, and the directory that holds all the required project files:

Click on Create. In the next tabular model designer, select the option Integrated workspace:

It creates a solution and project. Right-click on the project solution and view its properties. In the processing option, select the value Do Not Process:

You can also view the database name TestProject in the below screenshot. We require this database name in the following SQL queries:

In the Visual Studio, go to Build > Build Solution:

Now, go to the directory that we specified earlier while creating the project. Here, we see the project solution file and a folder holding project files:

Open the folder, and you can see two files highlighted below:

Model.asdatabase
Model.deploymentoptions

Open the S3 bucket that is integrated with AWS RDS SQL Server and upload the files into S3:

We use the msdb.dbo.rds_download_from_s3 stored procedure to download the project files from the S3 bucket to the RDS instance. Connect to the AWS RDS SQL instance in the SSMS and execute the following queries. In these queries, we specified the project file name that we uploaded in the S3 bucket:

exec msdb.dbo.rds_download_from_s3 @s3_arn_of_file='arn:aws:s3:::sqlshackdemo/Model.asdatabase' , @rds_file_path='d:\S3\model.asdatabase' , @overwrite_file=1

exec msdb.dbo.rds_download_from_s3 @s3_arn_of_file='arn:aws:s3:::sqlshackdemo/Model.deploymentoptions' , @rds_file_path='d:\S3\model.deploymentoptions' , @overwrite_file=1

It starts the tasks to download the files from the S3 bucket to the RDS instance folder:

We can check the status of the tasks using the stored procedure msdb.dbo.rds_fn_task_status using the task ID we got earlier. The first parameter is always a NULL value:

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

We can see that both tasks completed successfully:

Now, we are ready to deploy the tabular project in the RDS instance. It uses the msdb.dbo.rds_msbi_task stored procedure with the following arguments:

@task_type: SSAS_DEPLOY_PROJECT
@file_path: It is the RDS instance project path. It always uses directory D:\S3

1	exec msdb.dbo.rds_msbi_task @task_type='SSAS_DEPLOY_PROJECT', @file_path='d:\S3\Model.asdatabase';

It also starts a task for the RDS instance. Once we track it using the msdb.dbo.rds_fn_task_status, its status shows success after some time. It might take longer, depending on the project:

We also need to give permissions to the user that connects with the AWS RDS SQL Server Analysis Service. To do so, we use the stored procedure msdb.dbo.rds_msbi_task with the following arguments:

@task_type: We need to specify SSAS_ADD_DB_ADMIN_MEMBER in this argument
@Database_name: it is the database name that we specified while creating the Visual Studio project
@ssas_role_name: Specify a role name to create in the Analysis Service database
@ssas_role_member: Specify the Windows user that we use to connect RDS using Windows authentication

1 2	exec msdb.dbo.rds_msbi_task @task_type='SSAS_ADD_DB_ADMIN_MEMBER', @database_name='Testproject', @ssas_role_name='Sample', @ssas_role_member='sqlshackdemo\rajendra.gupta';

We can see this stored procedure successfully added specified user in the specified role:

Now, refresh the Analysis Server connection in SSMS. We can see the TestProject database in the Analysis Service of AWS RDS SQL Server:

Conclusion

In this article, we explored that AWS RDS now supports SQL Server Analysis Service as well. We can configure it for RDS instance and deploy projects using Visual Studio, and RDS stored procedures. We deployed a primary tabular database in this demonstration.

Follow the article Creating your first SSAS tabular model database to create a tabular model database in RDS.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023