SSAS - How to create Analysis Database Snapshot or Cube Snapshot

1.      Background

The purpose of this article is to provide detail information about how to create an Analysis Database Snapshot or cube snapshot in an automated way. We are going to use Powershell script with a SQL agent job to automate the Analysis DB snapshot process. Here SQL Agent job executes PowerShell script that will first script out the source analysis DB into .xmla file and then it will rename the Analysis DB with postfix _YYYYMMDD as well as it change the storage mode from ROLAP to MOLAP wherever it is required in the newly created XMLA file and finally deploy the modified XMLA on Source analysis server instance. In this way, it will create a new analysis DB (replica of existing DB) that can be utilized as snapshot of current analysis DB after processing the database.

2.      Why Analysis Database or Cube Snapshot is required?

There are several reasons for maintaining the Cube Snapshot. Following are the few of the them:

1.      To maintain Analysis DB/cubes on weekly, monthly and annual basis. E.g CubeName_2010, CubeName_2011,CubeName_23032013

2.       Sometimes, we need to maintain read only copy of Cubes for Analysis and Reporting purpose on which only any specific group will have access. Due to this, we can avoid the access to main Cubes.

3.       Snapshots can be utilized for analysis & reporting of historical and timelines data

3.      PowerShell Script for Analysis Database Snapshot

Below is the PowerShell script that will do following operations:

·         It connects to SQL Server Analysis Server instance and script out the given Analysis DB into .XMLA file. Here the output folder is C:\Data

·         It adds the Postfix (_YYYYMMDD) to Analysis Database name, changes ROLAP to MOLAP storage mode and disable the proactive caching.

Note: Script will do all the changes in newly created .xmla file

·         Deployment of Analysis Database with postfix _YYYYMMDD into SQL Analysis instance.

·         Processing of the newly created Analysis database so that we can browse the data.

param

(

[string] $servername,

[string] $DBName,

[string] $OutputFilePath

)

##$serverName = "MyAnalysisServerName"

##$OutputFilePath = "C:\data\"

##$DBName = "MyAnalysisDB"

## load the AMO and XML assemblies into the current runspace

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") > $null

[System.Reflection.Assembly]::LoadWithPartialName("System.Xml") > $null

$dateStamp = (get-Date).ToString("yyyyMMdd")

## connect to the server

$svr = new-Object Microsoft.AnalysisServices.Server

$svr.Connect($serverName)

foreach ($db in $svr.Databases)

{

    if ($db.name.tostring() -eq $DBName)

    {

    ##write-host "---------------------------------------------"

    ##write-host "Scripting: " $db.Name

    $xw = new-object System.Xml.XmlTextWriter("$($OutputFilePath)DBScript_$($db.Name)_

$($dateStamp).xmla", [System.Text.Encoding]::UTF8)

    $xw.Formatting = [System.Xml.Formatting]::Indented

    [Microsoft.AnalysisServices.Scripter]::WriteCreate($xw,$svr,$db,$true,$true)

    $ReplaceFilePath = "$($OutputFilePath)DBScript_$($db.Name)_$($dateStamp).xmla"

    $NewDBName = "$($db.Name)_$($dateStamp)"

    $xw.Close()

    }

}

$svr.Disconnect()

##write-host "Scripting completed: " $NewDBName

##write-host "---------------------------------------------"

##write-host "Started  : Changing Analysis DB name,storage mode and Proactive caching: "

(Get-Content $ReplaceFilePath) |

Foreach-Object {$_ -replace "<StorageMode>Rolap</StorageMode>", "<StorageMode>Molap</StorageMode>"

} |

Set-Content $ReplaceFilePath

(Get-Content $ReplaceFilePath) |

Foreach-Object {$_ -replace "<Enabled>true</Enabled>", "<Enabled>false</Enabled>" } |

Set-Content $ReplaceFilePath

(Get-Content $ReplaceFilePath) |

Foreach-Object {$_ -replace "AnalysisDBName", $NewDBName} |

Set-Content $ReplaceFilePath

##write-host "Completed  :  Changing Analysis DB name,storage mode and Proactive caching: "

###############################################################################################

##write-host "---------------------------------------------"

##write-host "Started  :  Creaitng Cube Snapshot: " $NewDBName

$ReplaceFilePath = $ReplaceFilePath.Replace('"',"")

$servername = $servername.Replace('"',"")

$qry = [string](get-content $ReplaceFilePath)

$amo = "Microsoft.AnalysisServices"

[System.Reflection.Assembly]::LoadWithPartialName($amo) > $null

$svr = New-Object Microsoft.AnalysisServices.Server

$svr.Connect($servername)

$svr.Execute($qry)

##write-host "Completed  :  Creaitng Cube Snapshot: " $NewDBName

###############################################################################################

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") >$NULL

$server = New-Object Microsoft.AnalysisServices.Server

$server.connect($servername)

$database=$server.databases

$advdb=$database[$NewDBName]

$date1=get-date

##write-host "---------------------------------------------"

##write-host "Processing Database : $advdb"

$ProcessType="ProcessFull"

$advdb.Process($ProcessType)

$date2=get-date

##write-host "Process

                                                  -----------------------End of the script----------------------

4.      Automation of Analysis Database/Cube Snapshot

We can automate the Analysis DB/Cube Snapshot creation process by using SQL Agent job. For Automation, We need to run the above mentioned PowerShell script into SQL Agent job step with required input parameters. For running the PowerShell script, we don’t need to copy and paste complete PowerShell script into the job step instead gives the full path of the PowerShell script with required input parameters. Note Step type should be PowerShell         

Analysis Database Snapshot Job Script:

                USE [msdb]

GO

/****** Object:  Job [Analysis_DB_Snapshot]    Script Date: 07/24/2013 16:00:53 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 07/24/2013 16:00:53 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Analysis_DB_Snapshot',

            @enabled=1,

            @notify_level_eventlog=0,

            @notify_level_email=0,

            @notify_level_netsend=0,

            @notify_level_page=0,

            @delete_level=0,

            @description=N'No description available.',

            @category_name=N'[Uncategorized (Local)]',

            @owner_login_name=N'Vishal-PC\Vishal', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Creating snapshot of Analysis DB]    Script Date: 07/24/2013 16:00:54 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Creating snapshot of Analysis DB',

            @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'PowerShell',

            @command=N'G:\PSScript\ScriptASDB_Job.ps1  ".\SQL2012"  "SSAS_Study"  "C:\Data\"',

            @database_name=N'master',

            @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

6.     Conclusion

By using above mentioned steps, we can automate the Analysis Database/Cube snapshot process by using PowerShell script.

Fig Showing Analysis DB and its Snapshots

 

 

----------------------------------------------------End of Document---------------------------------------------------