Support for SQL Server Analysis Services in Amazon RDS for SQL Server
Microsoft SQL Server Analysis Services (SSAS) is part of the Microsoft Business Intelligence (MSBI) suite. SSAS is an online analytical processing (OLAP) and data mining tool that is installed within SQL Server. You use SSAS to analyze data to help make business decisions. SSAS differs from the SQL Server relational database because SSAS is optimized for queries and calculations common in a business intelligence environment.
You can turn on SSAS for existing or new DB instances. It's installed on the same DB
instance as your database engine. For more information on SSAS, see the Microsoft Analysis services
documentation
Amazon RDS supports SSAS for SQL Server Standard and Enterprise Editions on the following versions:
-
Tabular mode:
-
SQL Server 2019, version 15.00.4043.16.v1 and higher
-
SQL Server 2017, version 14.00.3223.3.v1 and higher
-
SQL Server 2016, version 13.00.5426.0.v1 and higher
-
-
Multidimensional mode:
-
SQL Server 2019, version 15.00.4153.1.v1 and higher
-
SQL Server 2017, version 14.00.3381.3.v1 and higher
-
SQL Server 2016, version 13.00.5882.1.v1 and higher
-
Contents
Limitations
The following limitations apply to using SSAS on RDS for SQL Server:
-
RDS for SQL Server supports running SSAS in Tabular or Multidimensional mode. For more information, see Comparing tabular and multidimensional solutions
in the Microsoft documentation. -
You can only use one SSAS mode at a time. Before changing modes, make sure to delete all of the SSAS databases.
For more information, see Changing the SSAS mode.
-
Multi-AZ instances aren't supported.
-
Instances must use self-managed Active Directory or AWS Directory Service for Microsoft Active Directory for SSAS authentication. For more information, see Working with Active Directory with RDS for SQL Server.
-
Users aren't given SSAS server administrator access, but they can be granted database-level administrator access.
-
The only supported port for accessing SSAS is 2383.
-
You can't deploy projects directly. We provide an RDS stored procedure to do this. For more information, see Deploying SSAS projects on Amazon RDS.
-
Processing during deployment isn't supported.
-
Using .xmla files for deployment isn't supported.
-
SSAS project input files and database backup output files can only be in the
D:\S3
folder on the DB instance.
Turning on SSAS
Use the following process to turn on SSAS for your DB instance:
-
Create a new option group, or choose an existing option group.
-
Add the
SSAS
option to the option group. -
Associate the option group with the DB instance.
-
Allow inbound access to the virtual private cloud (VPC) security group for the SSAS listener port.
-
Turn on Amazon S3 integration.
Creating an option group for SSAS
Use the AWS Management Console or the AWS CLI to create an option group that corresponds to the SQL Server engine and version of the DB instance that you plan to use.
Note
You can also use an existing option group if it's for the correct SQL Server engine and version.
The following console procedure creates an option group for SQL Server Standard Edition 2017.
To create the option group
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Option groups.
-
Choose Create group.
-
In the Create option group pane, do the following:
-
For Name, enter a name for the option group that is unique within your AWS account, such as
ssas-se-2017
. The name can contain only letters, digits, and hyphens. -
For Description, enter a brief description of the option group, such as
SSAS option group for SQL Server SE 2017
. The description is used for display purposes. -
For Engine, choose sqlserver-se.
-
For Major engine version, choose 14.00.
-
-
Choose Create.
The following CLI example creates an option group for SQL Server Standard Edition 2017.
To create the option group
-
Use one of the following commands.
For Linux, macOS, or Unix:
aws rds create-option-group \ --option-group-name
ssas-se-2017
\ --engine-namesqlserver-se
\ --major-engine-version14.00
\ --option-group-description "SSAS option group for SQL Server SE 2017
"For Windows:
aws rds create-option-group ^ --option-group-name
ssas-se-2017
^ --engine-namesqlserver-se
^ --major-engine-version14.00
^ --option-group-description "SSAS option group for SQL Server SE 2017
"
Adding the SSAS option to the option group
Next, use the AWS Management Console or the AWS CLI to add the SSAS
option to the option
group.
To add the SSAS option
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Option groups.
-
Choose the option group that you just created.
-
Choose Add option.
-
Under Option details, choose SSAS for Option name.
-
Under Option settings, do the following:
-
For Max memory, enter a value in the range 10–80.
Max memory specifies the upper threshold above which SSAS begins releasing memory more aggressively to make room for requests that are running, and also new high-priority requests. The number is a percentage of the total memory of the DB instance. The allowed values are 10–80, and the default is 45.
-
For Mode, choose the SSAS server mode, Tabular or Multidimensional.
If you don't see the Mode option setting, it means that Multidimensional mode isn't supported in your AWS Region. For more information, see Limitations.
Tabular is the default.
-
For Security groups, choose the VPC security group to associate with the option.
Note
The port for accessing SSAS, 2383, is prepopulated.
-
-
Under Scheduling, choose whether to add the option immediately or at the next maintenance window.
-
Choose Add option.
To add the SSAS option
-
Create a JSON file, for example
ssas-option.json
, with the following parameters:-
OptionGroupName
– The name of option group that you created or chose previously (ssas-se-2017
in the following example). -
Port
– The port that you use to access SSAS. The only supported port is 2383. -
VpcSecurityGroupMemberships
– Memberships for VPC security groups for your RDS DB instance. -
MAX_MEMORY
– The upper threshold above which SSAS should begin releasing memory more aggressively to make room for requests that are running, and also new high-priority requests. The number is a percentage of the total memory of the DB instance. The allowed values are 10–80, and the default is 45. -
MODE
– The SSAS server mode, eitherTabular
orMultidimensional
.Tabular
is the default.If you receive an error that the
MODE
option setting isn't valid, it means that Multidimensional mode isn't supported in your AWS Region. For more information, see Limitations.
The following is an example of a JSON file with SSAS option settings.
{ "OptionGroupName": "
ssas-se-2017
", "OptionsToInclude": [ { "OptionName": "SSAS", "Port": 2383, "VpcSecurityGroupMemberships": ["sg-0abcdef123
"], "OptionSettings": [{"Name":"MAX_MEMORY","Value":"60
"},{"Name":"MODE","Value":"Multidimensional
"}] }], "ApplyImmediately": true } -
-
Add the
SSAS
option to the option group.For Linux, macOS, or Unix:
aws rds add-option-to-option-group \ --cli-input-json file://
ssas-option.json
\ --apply-immediatelyFor Windows:
aws rds add-option-to-option-group ^ --cli-input-json file://
ssas-option.json
^ --apply-immediately
Associating the option group with your DB instance
You can use the console or the CLI to associate the option group with your DB instance.
Associate your option group with a new or existing DB instance:
-
For a new DB instance, associate the option group with the DB instance when you launch the instance. For more information, see Creating an Amazon RDS DB instance.
-
For an existing DB instance, modify the instance and associate the new option group with it. For more information, see Modifying an Amazon RDS DB instance.
Note
If you use an existing instance, it must already have an Active Directory domain and AWS Identity and Access Management (IAM) role associated with it. If you create a new instance, specify an existing Active Directory domain and IAM role. For more information, see Working with Active Directory with RDS for SQL Server.
You can associate your option group with a new or existing DB instance.
Note
If you use an existing instance, it must already have an Active Directory domain and IAM role associated with it. If you create a new instance, specify an existing Active Directory domain and IAM role. For more information, see Working with Active Directory with RDS for SQL Server.
To create a DB instance that uses the option group
-
Specify the same DB engine type and major version that you used when creating the option group.
For Linux, macOS, or Unix:
aws rds create-db-instance \ --db-instance-identifier
myssasinstance
\ --db-instance-classdb.m5.2xlarge
\ --enginesqlserver-se
\ --engine-version14.00.3223.3.v1
\ --allocated-storage100
\ --manage-master-user-password \ --master-usernameadmin
\ --storage-typegp2
\ --license-modelli
\ --domain-iam-role-namemy-directory-iam-role
\ --domainmy-domain-id
\ --option-group-namessas-se-2017
For Windows:
aws rds create-db-instance ^ --db-instance-identifier
myssasinstance
^ --db-instance-classdb.m5.2xlarge
^ --enginesqlserver-se
^ --engine-version14.00.3223.3.v1
^ --allocated-storage100
^ --manage-master-user-password ^ --master-usernameadmin
^ --storage-typegp2
^ --license-modelli
^ --domain-iam-role-namemy-directory-iam-role
^ --domainmy-domain-id
^ --option-group-namessas-se-2017
To modify a DB instance to associate the option group
-
Use one of the following commands.
For Linux, macOS, or Unix:
aws rds modify-db-instance \ --db-instance-identifier
myssasinstance
\ --option-group-namessas-se-2017
\ --apply-immediatelyFor Windows:
aws rds modify-db-instance ^ --db-instance-identifier
myssasinstance
^ --option-group-namessas-se-2017
^ --apply-immediately
Allowing inbound access to your VPC security group
Create an inbound rule for the specified SSAS listener port in the VPC security group associated with your DB instance. For more information about setting up security groups, see Provide access to your DB instance in your VPC by creating a security group.
Enabling Amazon S3 integration
To download model configuration files to your host for deployment, use Amazon S3 integration. For more information, see Integrating an Amazon RDS for SQL Server DB instance with Amazon S3.
Deploying SSAS projects on Amazon RDS
On RDS, you can't deploy SSAS projects directly by using SQL Server Management Studio (SSMS). To deploy projects, use an RDS stored procedure.
Note
Using .xmla files for deployment isn't supported.
Before you deploy projects, make sure of the following:
-
Amazon S3 integration is turned on. For more information, see Integrating an Amazon RDS for SQL Server DB instance with Amazon S3.
-
The
Processing Option
configuration setting is set toDo Not Process
. This setting means that no processing happens after deployment. -
You have both the
andmyssasproject
.asdatabase
files. They're automatically generated when you build the SSAS project.myssasproject
.deploymentoptions
To deploy an SSAS project on RDS
-
Download the
.asdatabase
(SSAS model) file from your S3 bucket to your DB instance, as shown in the following example. For more information on the download parameters, see Downloading files from an Amazon S3 bucket to a SQL Server DB instance.exec msdb.dbo.rds_download_from_s3 @s3_arn_of_file='arn:aws:s3:::
bucket_name
/myssasproject
.asdatabase', [@rds_file_path='D:\S3\myssasproject
.asdatabase'], [@overwrite_file=1]; -
Download the
.deploymentoptions
file from your S3 bucket to your DB instance.exec msdb.dbo.rds_download_from_s3 @s3_arn_of_file='arn:aws:s3:::
bucket_name
/myssasproject
.deploymentoptions', [@rds_file_path='D:\S3\myssasproject
.deploymentoptions'], [@overwrite_file=1]; -
Deploy the project.
exec msdb.dbo.rds_msbi_task @task_type='SSAS_DEPLOY_PROJECT', @file_path='D:\S3\
myssasproject
.asdatabase';
Monitoring the status of a deployment task
To track the status of your deployment (or download) task, call the
rds_fn_task_status
function. It takes two parameters. The first
parameter should always be NULL
because it doesn't apply to SSAS. The
second parameter accepts a task ID.
To see a list of all tasks, set the first parameter to NULL
and the second
parameter to 0
, as shown in the following example.
SELECT * FROM msdb.dbo.rds_fn_task_status(NULL,
0
);
To get a specific task, set the first parameter to NULL
and the second
parameter to the task ID, as shown in the following example.
SELECT * FROM msdb.dbo.rds_fn_task_status(NULL,
42
);
The rds_fn_task_status
function returns the following information.
Output parameter |
Description |
---|---|
|
The ID of the task. |
|
For SSAS, tasks can have the following task types:
|
|
Not applicable to SSAS tasks. |
|
The progress of the task as a percentage. |
|
The amount of time spent on the task, in minutes. |
|
The status of the task. Possible statuses are the following:
|
|
Additional information about the task. If an error occurs during processing, this column contains information about the error. For more information, see Troubleshooting SSAS issues. |
|
The date and time that the task status was last updated. |
|
The date and time that the task was created. |
|
Not applicable to SSAS tasks. |
|
Not applicable to SSAS tasks. |
|
Not applicable to SSAS tasks. |
|
Not applicable to SSAS tasks. |
|
Not applicable to SSAS tasks. |
|
Metadata associated with the SSAS task. |
Using SSAS on Amazon RDS
After deploying the SSAS project, you can directly process the OLAP database on SSMS.
To use SSAS on RDS
-
In SSMS, connect to SSAS using the user name and password for the Active Directory domain.
-
Expand Databases. The newly deployed SSAS database appears.
-
Locate the connection string, and update the user name and password to give access to the source SQL database. Doing this is required for processing SSAS objects.
-
For Tabular mode, do the following:
-
Expand the Connections tab.
-
Open the context (right-click) menu for the connection object, and then choose Properties.
-
Update the user name and password in the connection string.
-
-
For Multidimensional mode, do the following:
-
Expand the Data Sources tab.
-
Open the context (right-click) menu for the data source object, and then choose Properties.
-
Update the user name and password in the connection string.
-
-
-
Open the context (right-click) menu for the SSAS database that you created and choose Process Database.
Depending on the size of the input data, the processing operation might take several minutes to complete.
Topics
Setting up a Windows-authenticated user for SSAS
The main administrator user (sometimes called the master user) can use the
following code example to set up a Windows-authenticated login and grant the
required procedure permissions. Doing this grants permissions to the domain user to
run SSAS customer tasks, use S3 file transfer procedures, create credentials, and
work with the SQL Server Agent proxy. For more information, see Credentials (database engine)
You can grant some or all of the following permissions as needed to Windows-authenticated users.
-- Create a server-level domain user login, if it doesn't already exist USE [master] GO CREATE LOGIN [
mydomain
\user_name
] FROM WINDOWS GO -- Create domain user, if it doesn't already exist USE [msdb] GO CREATE USER [mydomain
\user_name
] FOR LOGIN [mydomain
\user_name
] GO -- Grant necessary privileges to the domain user USE [master] GO GRANT ALTER ANY CREDENTIAL TO [mydomain
\user_name
] GO USE [msdb] GO GRANT EXEC ON msdb.dbo.rds_msbi_task TO [mydomain
\user_name
] with grant option GRANT SELECT ON msdb.dbo.rds_fn_task_status TO [mydomain
\user_name
] with grant option GRANT EXEC ON msdb.dbo.rds_task_status TO [mydomain
\user_name
] with grant option GRANT EXEC ON msdb.dbo.rds_cancel_task TO [mydomain
\user_name
] with grant option GRANT EXEC ON msdb.dbo.rds_download_from_s3 TO [mydomain
\user_name
] with grant option GRANT EXEC ON msdb.dbo.rds_upload_to_s3 TO [mydomain
\user_name
] with grant option GRANT EXEC ON msdb.dbo.rds_delete_from_filesystem TO [mydomain
\user_name
] with grant option GRANT EXEC ON msdb.dbo.rds_gather_file_details TO [mydomain
\user_name
] with grant option GRANT EXEC ON msdb.dbo.sp_add_proxy TO [mydomain
\user_name
] with grant option GRANT EXEC ON msdb.dbo.sp_update_proxy TO [mydomain
\user_name
] with grant option GRANT EXEC ON msdb.dbo.sp_grant_login_to_proxy TO [mydomain
\user_name
] with grant option GRANT EXEC ON msdb.dbo.sp_revoke_login_from_proxy TO [mydomain
\user_name
] with grant option GRANT EXEC ON msdb.dbo.sp_delete_proxy TO [mydomain
\user_name
] with grant option GRANT EXEC ON msdb.dbo.sp_enum_login_for_proxy to [mydomain
\user_name
] with grant option GRANT EXEC ON msdb.dbo.sp_enum_proxy_for_subsystem TO [mydomain
\user_name
] with grant option GRANT EXEC ON msdb.dbo.rds_sqlagent_proxy TO [mydomain
\user_name
] with grant option ALTER ROLE [SQLAgentUserRole] ADD MEMBER [mydomain
\user_name
] GO
Adding a domain user as a database administrator
You can add a domain user as an SSAS database administrator in the following ways:
-
A database administrator can use SSMS to create a role with
admin
privileges, then add users to that role. -
You can use the following stored procedure.
exec msdb.dbo.rds_msbi_task @task_type='SSAS_ADD_DB_ADMIN_MEMBER', @database_name='
myssasdb
', @ssas_role_name='exampleRole
', @ssas_role_member='domain_name
\domain_user_name
';The following parameters are required:
-
@task_type
– The type of the MSBI task, in this caseSSAS_ADD_DB_ADMIN_MEMBER
. -
@database_name
– The name of the SSAS database to which you're granting administrator privileges. -
@ssas_role_name
– The SSAS database administrator role name. If the role doesn't already exist, it's created. -
@ssas_role_member
– The SSAS database user that you're adding to the administrator role.
-
Creating an SSAS proxy
To be able to schedule SSAS database processing using SQL Server Agent, create an SSAS credential and an SSAS proxy. Run these procedures as a Windows-authenticated user.
To create the SSAS credential
-
Create the credential for the proxy. To do this, you can use SSMS or the following SQL statement.
USE [master] GO CREATE CREDENTIAL [SSAS_Credential] WITH IDENTITY = N'
mydomain
\user_name
', SECRET = N'mysecret
' GONote
IDENTITY
must be a domain-authenticated login. Replace
with the password for the domain-authenticated login.mysecret
To create the SSAS proxy
-
Use the following SQL statement to create the proxy.
USE [msdb] GO EXEC msdb.dbo.sp_add_proxy @proxy_name=N'SSAS_Proxy',@credential_name=N'SSAS_Credential',@description=N'' GO
-
Use the following SQL statement to grant access to the proxy to other users.
USE [msdb] GO EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'SSAS_Proxy',@login_name=N'
mydomain
\user_name
' GO -
Use the following SQL statement to give the SSAS subsystem access to the proxy.
USE [msdb] GO EXEC msdb.dbo.rds_sqlagent_proxy @task_type='GRANT_SUBSYSTEM_ACCESS',@proxy_name='SSAS_Proxy',@proxy_subsystem='SSAS' GO
To view the proxy and grants on the proxy
-
Use the following SQL statement to view the grantees of the proxy.
USE [msdb] GO EXEC sp_help_proxy GO
-
Use the following SQL statement to view the subsystem grants.
USE [msdb] GO EXEC msdb.dbo.sp_enum_proxy_for_subsystem GO
Scheduling SSAS database processing using SQL Server Agent
After you create the credential and proxy and grant SSAS access to the proxy, you can create a SQL Server Agent job to schedule SSAS database processing.
To schedule SSAS database processing
-
Use SSMS or T-SQL for creating the SQL Server Agent job. The following example uses T-SQL. You can further configure its job schedule through SSMS or T-SQL.
-
The
@command
parameter outlines the XML for Analysis (XMLA) command to be run by the SQL Server Agent job. This example configures SSAS Multidimensional database processing. -
The
@server
parameter outlines the target SSAS server name of the SQL Server Agent job.To call the SSAS service within the same RDS DB instance where the SQL Server Agent job resides, use
localhost:2383
.To call the SSAS service from outside the RDS DB instance, use the RDS endpoint. You can also use the Kerberos Active Directory (AD) endpoint (
) if the RDS DB instances are joined by the same domain. For external DB instances, make sure to properly configure the VPC security group associated with the RDS DB instance for a secure connection.your-DB-instance-name
.your-AD-domain-name
You can further edit the query to support various XMLA operations. Make edits either by directly modifying the T-SQL query or by using the SSMS UI following SQL Server Agent job creation.
USE [msdb] GO DECLARE @jobId BINARY(16) EXEC msdb.dbo.sp_add_job @job_name=N'SSAS_Job', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @category_name=N'[Uncategorized (Local)]', @job_id = @jobId OUTPUT GO EXEC msdb.dbo.sp_add_jobserver @job_name=N'SSAS_Job', @server_name = N'(local)' GO EXEC msdb.dbo.sp_add_jobstep @job_name=N'SSAS_Job', @step_name=N'Process_SSAS_Object', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'ANALYSISCOMMAND', @command=N'<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Parallel> <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500"> <Object> <DatabaseID>Your_SSAS_Database_ID</DatabaseID> </Object> <Type>ProcessFull</Type> <WriteBackTableCreation>UseExisting</WriteBackTableCreation> </Process> </Parallel> </Batch>', @server=N'localhost:2383', @database_name=N'master', @flags=0, @proxy_name=N'SSAS_Proxy' GO
-
Revoking SSAS access from the proxy
You can revoke access to the SSAS subsystem and delete the SSAS proxy using the following stored procedures.
To revoke access and delete the proxy
-
Revoke subsystem access.
USE [msdb] GO EXEC msdb.dbo.rds_sqlagent_proxy @task_type='REVOKE_SUBSYSTEM_ACCESS',@proxy_name='SSAS_Proxy',@proxy_subsystem='SSAS' GO
-
Revoke the grants on the proxy.
USE [msdb] GO EXEC msdb.dbo.sp_revoke_login_from_proxy @proxy_name=N'SSAS_Proxy',@name=N'
mydomain
\user_name
' GO -
Delete the proxy.
USE [msdb] GO EXEC dbo.sp_delete_proxy @proxy_name = N'SSAS_Proxy' GO
Backing up an SSAS database
You can create SSAS database backup files only in the D:\S3
folder on the DB
instance. To move the backup files to your S3 bucket, use Amazon S3.
You can back up an SSAS database as follows:
-
A domain user with the
admin
role for a particular database can use SSMS to back up the database to theD:\S3
folder.For more information, see Adding a domain user as a database administrator.
-
You can use the following stored procedure. This stored procedure doesn't support encryption.
exec msdb.dbo.rds_msbi_task @task_type='SSAS_BACKUP_DB', @database_name='
myssasdb
', @file_path='D:\S3\ssas_db_backup
.abf', [@ssas_apply_compression=1], [@ssas_overwrite_file=1];The following parameters are required:
-
@task_type
– The type of the MSBI task, in this caseSSAS_BACKUP_DB
. -
@database_name
– The name of the SSAS database that you're backing up. -
@file_path
– The path for the SSAS backup file. The.abf
extension is required.
The following parameters are optional:
-
@ssas_apply_compression
– Whether to apply SSAS backup compression. Valid values are 1 (Yes) and 0 (No). -
@ssas_overwrite_file
– Whether to overwrite the SSAS backup file. Valid values are 1 (Yes) and 0 (No).
-
Restoring an SSAS database
Use the following stored procedure to restore an SSAS database from a backup.
You can't restore a database if there is an existing SSAS database with the same name. The stored procedure for restoring doesn't support encrypted backup files.
exec msdb.dbo.rds_msbi_task @task_type='SSAS_RESTORE_DB', @database_name='
mynewssasdb
', @file_path='D:\S3\ssas_db_backup
.abf';
The following parameters are required:
-
@task_type
– The type of the MSBI task, in this caseSSAS_RESTORE_DB
. -
@database_name
– The name of the new SSAS database that you're restoring to. -
@file_path
– The path to the SSAS backup file.
Restoring a DB instance to a specified time
Point-in-time recovery (PITR) doesn't apply to SSAS databases. If you do PITR, only the SSAS data in the last snapshot before the requested time is available on the restored instance.
To have up-to-date SSAS databases on a restored DB instance
-
Back up your SSAS databases to the
D:\S3
folder on the source instance. -
Transfer the backup files to the S3 bucket.
-
Transfer the backup files from the S3 bucket to the
D:\S3
folder on the restored instance. -
Run the stored procedure to restore the SSAS databases onto the restored instance.
You can also reprocess the SSAS project to restore the databases.
Changing the SSAS mode
You can change the mode in which SSAS runs, either Tabular or Multidimensional. To change the mode, use the AWS Management Console or the AWS CLI to modify the options settings in the SSAS option.
Important
You can only use one SSAS mode at a time. Make sure to delete all of the SSAS databases before changing the mode, or you receive an error.
The following Amazon RDS console procedure changes the SSAS mode to Tabular and
sets the MAX_MEMORY
parameter to 70 percent.
To modify the SSAS option
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Option groups.
-
Choose the option group with the
SSAS
option that you want to modify (ssas-se-2017
in the previous examples). -
Choose Modify option.
-
Change the option settings:
-
For Max memory, enter
70
. -
For Mode, choose Tabular.
-
-
Choose Modify option.
The following AWS CLI example changes the SSAS mode to Tabular and sets the MAX_MEMORY
parameter to 70
percent.
For the CLI command to work, make sure to include all of the required parameters, even if you're not modifying them.
To modify the SSAS option
-
Use one of the following commands.
For Linux, macOS, or Unix:
aws rds add-option-to-option-group \ --option-group-name
ssas-se-2017
\ --options "OptionName=SSAS,VpcSecurityGroupMemberships=sg-12345e67
,OptionSettings=[{Name=MAX_MEMORY,Value=70},{Name=MODE,Value=Tabular}]" \ --apply-immediatelyFor Windows:
aws rds add-option-to-option-group ^ --option-group-name
ssas-se-2017
^ --options OptionName=SSAS,VpcSecurityGroupMemberships=sg-12345e67
,OptionSettings=[{Name=MAX_MEMORY,Value=70},{Name=MODE,Value=Tabular}] ^ --apply-immediately
Turning off SSAS
To turn off SSAS, remove the SSAS
option from its option group.
Important
Before you remove the SSAS
option, delete your SSAS databases.
We highly recommend that you back up your SSAS databases before deleting them and removing
the SSAS
option.
To remove the SSAS option from its option group
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Option groups.
-
Choose the option group with the
SSAS
option that you want to remove (ssas-se-2017
in the previous examples). -
Choose Delete option.
-
Under Deletion options, choose SSAS for Options to delete.
-
Under Apply immediately, choose Yes to delete the option immediately, or No to delete it at the next maintenance window.
-
Choose Delete.
To remove the SSAS option from its option group
-
Use one of the following commands.
For Linux, macOS, or Unix:
aws rds remove-option-from-option-group \ --option-group-name
ssas-se-2017
\ --options SSAS \ --apply-immediatelyFor Windows:
aws rds remove-option-from-option-group ^ --option-group-name
ssas-se-2017
^ --options SSAS ^ --apply-immediately
Troubleshooting SSAS issues
You might encounter the following issues when using SSAS.
Issue | Type | Troubleshooting suggestions |
---|---|---|
Unable to configure the SSAS option. The requested SSAS mode is
|
RDS event | You can't change the SSAS mode if you still have SSAS databases that use the current mode. Delete the SSAS databases, then try again. |
Unable to remove the SSAS option because there are |
RDS event | You can't turn off SSAS if you still have SSAS databases. Delete the SSAS databases, then try again. |
The SSAS option isn't enabled or is in the process of being enabled. Try again
later. |
RDS stored procedure | You can't run SSAS stored procedures when the option is turned off, or when it's being turned on. |
The SSAS option is configured incorrectly. Make sure that the option group membership status
is "in-sync", and review the RDS event logs for relevant SSAS configuration error messages. Following
these investigations, try again. If errors continue to occur, contact AWS Support. |
RDS stored procedure |
You can't run SSAS stored procedures when your option
group membership isn't in the If your option group membership status changes to
Reconfigure the SSAS option, because RDS allows only one SSAS mode at a time, and doesn't support SSAS option removal with SSAS databases present. Check the RDS event logs for configuration errors for your SSAS instance, and resolve the issues accordingly. |
Deployment failed. The change can only be deployed on a server running in
|
RDS stored procedure |
You can't deploy a Tabular database to a Multidimensional server, or a Multidimensional database to a Tabular server. Make sure that you're using files with the correct mode, and verify that the |
The restore failed. The backup file can only be restored on a server running in
|
RDS stored procedure |
You can't restore a Tabular database to a Multidimensional server, or a Multidimensional database to a Tabular server. Make sure that you're using files with the correct mode, and verify that the |
The restore failed. The backup file and the RDS DB instance versions are
incompatible. |
RDS stored procedure |
You can't restore an SSAS database with a version incompatible to the SQL Server instance version. For more information, see Compatibility levels for tabular models |
The restore failed. The backup file specified in the restore operation is damaged or is
not an SSAS backup file. Make sure that @rds_file_path is correctly formatted. |
RDS stored procedure |
You can't restore an SSAS database with a damaged file. Make sure that the file isn't damaged or corrupted. This error can also be raised when |
The restore failed. The restored database name can't contain any reserved words or
invalid characters: . , ; ' ` : / \\ * | ? \" & % $ ! + = ( ) [ ] { } < >, or be longer than 100
characters. |
RDS stored procedure |
The restored database name can't contain any reserved words or characters that aren't valid, or be longer than 100 characters. For SSAS object naming conventions, see Object naming rules |
An invalid role name was provided. The role name can't contain any reserved
strings. |
RDS stored procedure |
The role name can't contain any reserved strings. For SSAS object naming conventions, see Object naming rules |
An invalid role name was provided. The role name can't contain any of the following
reserved characters: . , ; ' ` : / \\ * | ? \" & % $ ! + = ( ) [ ] { } < > |
RDS stored procedure |
The role name can't contain any reserved characters. For SSAS object naming conventions, see Object naming rules |