There are different aspects of SSAS Database Management that we will be looked into detail in this article. We will be looking at backup, processing, and synchronization.
SQL Server Analysis Service typically consists of OLAP databases that are used to store, Cubes, Dimensions, and Mining Structures. Though there is no new data generated at the SSAS database, you need special attention to SSAS Database Management. If not, you may have to rebuild the entire OLAP database.
SSAS Database backup
Unlike databases where applications are used to insert and read data, the SSAS database is not a data source. Typically, the SSAS database will be updated daily, hence it does not require a sophisticated SSAS database backup procedure. However, before the cube process or data mining structure processing, as a best practice, it is better to backup the existing SSAS database.
Few options can be used in SQL Server to perform SSAS database backup.
From SQL Server Management Studio (SSMS), by connecting to the SSAS instance, you have the option of taking an SSAS database backup.
Right-click the SSAS database and select the Backup option you will get the following screenshot:
This will backup the selected SSAS database to a specific path. By using the scripting option, relevant script will be generated as shown below:
|
1 2 3 4 5 6 7 8 |
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <DatabaseID>SQLShack</DatabaseID> </Object> <File>D:\Temp\SQLShack.abf</File> <AllowOverwrite>true</AllowOverwrite> <Password>12qw#E$R</Password> </Backup> |
As shown in the above screenshot, file overwrites, compression, and encryption options are available.
However, as an administrator, you would like to schedule this rather than manually executing it. In SQL Server, you will revert to SQL Server Agent for the scheduling requirements.
Let us see how the SQL Server Agent can be used to create an SSAS database backup.
For this, SQL Server Agent job is named, SSAS Database Management is created and step is included. SQL Server Analysis Service Command Type is included and the above script is used as the command.
In this step, the SQL Server Agent Service Account is used as the credentials. Since it is the only option available, you need to make sure that the service agent account should have the necessary permission to take a backup of the SSAS database.
Another option to perform the SSAS Database backup is by using SQL Server Integration Services (SSIS). Since SSIS is used as the ETL tool, it is better to include the SSAS database into an SSIS package so that before the ETL process or the Cube process, you can backup the SSAS database. This will make SSAS database management much efficient as in case of failure in the processing of OLAP Database, you can revert to the backup SSAS database by restoring it.
To perform the SSAS database backup, we can use the Analysis Services Execute DDL Task control flow task in SSIS.
Analysis Services Execute DDL Task can be configured as shown in the following screenshot. The script that was used in SQL Server Agent can be reused in this control as well. After creating the connection to the SSAS instance, a script can be inserted to the Source in the DDL page as shown below.
SSAS Database Process
Another task of the SSAS Database Management is processing the cubes and data mining tasks.
These cubes or data mining structures have to be processed periodically. Typically, cubes need to be refreshed after a data load to the data warehouse. However, data mining structures do not need to be processed frequently. Anyway, data mining structures have to be processed even at low frequency.
To facilitate the processing of data mining structures, we can use the Analysis Services Processing Task control task in SSIS.
The following screenshot shows how to process the cubes and dimensions as an SSAS database management task.
First, you need to make a connection to the SSAS database and in the following example, the UOK_2020 SSAS database is used.
You can either select the entire cube to process or you can process objects individually. By choosing individually, you can unselect the obsolete objects later. This is will make the SSAS Database Management much easier.
During the cube process, there are multiple options such as Process Default, Process Full, Process Data, Process Clear, Process Add, and Process Index.
Similar to the cube process, you can process the data mining structures as shown in the below screenshot:
For the SSAS cube processing, you can choose Mining models and Mining structures as shown in the above screenshot.
Similar to the SSAS database backup, we can execute this from a XMLA script as shown in the below script:
|
1 2 3 4 5 6 7 8 9 10 11 |
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Parallel> <Process> <Object> <DatabaseID>SQLShack</DatabaseID> </Object> <Type>ProcessFull</Type> <WriteBackTableCreation>UseExisting</WriteBackTableCreation> </Process> </Parallel> </Batch> |
Since you can process only one cube or data mining structure from the Analysis control task hence you need to combine multiple control tasks as shown in the below screenshot:
SSAS Database Synchronization
Another important task in the SSAS Database Management is the synchronization of SSAS between Servers. With the synchronization option, you are providing high availability for the SSAS database. SSAS database synchronization is equivalent to the Database replication to some extent where you can synchronize cubes, dimensions, and data mining structures. However, unlike database replication, you will not be able to synchronize selected objects. Instead, you have to synchronize the entire SSAS database.
By right-clicking the Database folder in the SQL Server Management Studio of the SSAS Server, you will get the synchronize option which is wizard-driven. The following is the first screen of the wizard:
In the SSAS database synchronization, synchronization has to be done between different SSAS Servers. This means you cannot synchronize the database into the same Server. Further, you need to start the synchronization from the destination Server and in the wizard, you need to provide the source SSAS Server and the database name. You cannot provide a different name to the destination SSAS database. The same name will be used for the destination SSAS database as well.
The next wizard screen will provide you with the option of security and the compression setting as shown below:
Apart from the copying of roles and members in the SSAS Database to the destination Server, you can compress during the synchronization. This is a very helpful option if you are synchronizing the SSAS database over the network so the network bandwidth will be reduced.
The last option is to choose the method of synchronization:
You can decide whether synchronization should be done now or you want to save the command into an XMLA script as shown below:
|
1 2 3 4 5 6 7 8 9 10 |
<Synchronize> <Source> <ConnectionString>Provider=MSOLAP.8;Data Source=.;Integrated Security=SSPI;Initial Catalog=SQLShack</ConnectionString> <Object> <DatabaseID>SQLShack</DatabaseID> </Object> </Source> <SynchronizeSecurity>SkipMembership</SynchronizeSecurity> <ApplyCompression>true</ApplyCompression> </Synchronize> |
You can use this script in a Analysis Services Execute DDL Task of a SSIS package and sechdule it accordingly.
All in one SSIS Package
We have discussed three aspects, backup, processing, and synchronization for SSAS Database Management. In a real-world implementation, we need to use them together so that it is easy to schedule. We can use an SSIS package as shown in the following screenshot:
As shown in the above screenshot, we need to perform an SSAS database backup before the process. Since we can backup them in parallel, we have used a Sequence container. After all the backups are completed next is to process the cubes and data mining structures. After the processing is completed, next will synchronize with a different Server.
Conclusion
SSAS Database is to analyze and predict data utilizing cubes and data mining Structures. Since different objects exist in these structures, it is important to discuss the SSAS Database Management. We discussed three aspects that are Backup, Process, and Synchronization. All those three tasks can be done from SSIS and from that we can schedule it accordingly.
View all posts by Dinesh Asanka
- Testing Type 2 Slowly Changing Dimensions in a Data Warehouse - May 30, 2022
- Incremental Data Extraction for ETL using Database Snapshots - January 10, 2022
- Use Replication to improve the ETL process in SQL Server - November 4, 2021