Support for SQL Server Analysis Services in Amazon RDS for SQL Server

To deploy an SSAS project on RDS

1. 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-cn:s3:::bucket_name/myssasproject.asdatabase', 
   [@rds_file_path='D:\S3\myssasproject.asdatabase'],
   [@overwrite_file=1];

2. 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-cn:s3:::bucket_name/myssasproject.deploymentoptions', 
   [@rds_file_path='D:\S3\myssasproject.deploymentoptions'],
   [@overwrite_file=1];

3. Deploy the project.

   exec msdb.dbo.rds_msbi_task
   @task_type='SSAS_DEPLOY_PROJECT',
   @file_path='D:\S3\myssasproject.asdatabase';

To use SSAS on RDS

1. In SSMS, connect to SSAS using the user name and password for the Active Directory domain.

2. Expand Databases. The newly deployed SSAS database appears.

3. 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.

   1. For Tabular mode, do the following:

      1. Expand the Connections tab.

      2. Open the context (right-click) menu for the connection object, and then choose Properties.

      3. Update the user name and password in the connection string.

   2. For Multidimensional mode, do the following:

      1. Expand the Data Sources tab.

      2. Open the context (right-click) menu for the data source object, and then choose Properties.

      3. Update the user name and password in the connection string.

4. 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.

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'
  GO

  Note

  IDENTITY must be a domain-authenticated login. Replace mysecret with the password for the domain-authenticated login.

To create the SSAS proxy

1. 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

2. 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

3. 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

1. Use the following SQL statement to view the grantees of the proxy.

   USE [msdb]
   GO
   EXEC sp_help_proxy
   GO

2. Use the following SQL statement to view the subsystem grants.

   USE [msdb]
   GO
   EXEC msdb.dbo.sp_enum_proxy_for_subsystem
   GO

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 (your-DB-instance-name.your-AD-domain-name) 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.

  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

To revoke access and delete the proxy

1. 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

2. 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

3. Delete the proxy.

   USE [msdb]
   GO
   EXEC dbo.sp_delete_proxy @proxy_name = N'SSAS_Proxy'
   GO

To have up-to-date SSAS databases on a restored DB instance

1. Back up your SSAS databases to the D:\S3 folder on the source instance.

2. Transfer the backup files to the S3 bucket.

3. Transfer the backup files from the S3 bucket to the D:\S3 folder on the restored instance.

4. Run the stored procedure to restore the SSAS databases onto the restored instance.

   You can also reprocess the SSAS project to restore the databases.