ssas还原数据库
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.
SSAS数据库管理的不同方面将在本文中详细介绍。 我们将研究备份,处理和同步。
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.
SQL Server Analysis Service通常由用于存储,多维数据集,维度和挖掘结构的OLAP数据库组成。 尽管在SSAS数据库中没有生成新数据,但是您需要特别注意SSAS数据库管理。 如果不是,则可能必须重建整个OLAP数据库。
SSAS数据库备份 (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.
与使用应用程序插入和读取数据的数据库不同,SSAS数据库不是数据源。 通常,SSAS数据库将每天更新,因此它不需要复杂的SSAS数据库备份过程。 但是,在多维数据集处理或数据挖掘结构处理之前,作为最佳实践,最好备份现有的SSAS数据库。
Few options can be used in SQL Server to perform SSAS database backup.
在SQL Server中,很少有选项可以用来执行SSAS数据库备份。
From SQL Server Management Studio (SSMS), by connecting to the SSAS instance, you have the option of taking an SSAS database backup.
从SQL Server Management Studio(SSMS),通过连接到SSAS实例,您可以选择进行SSAS数据库备份。
Right-click the SSAS database and select the Backup option you will get the following screenshot:
右键单击SSAS数据库,然后选择“备份”选项,您将获得以下屏幕截图:
This will backup the selected SSAS database to a specific path. By using the scripting option, relevant script will be generated as shown below:
这会将所选的SSAS数据库备份到特定路径。 通过使用脚本选项,将生成相关脚本,如下所示:
<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.
但是,作为管理员,您希望计划此操作,而不是手动执行。 在SQL Server中,您将恢复到SQL Server代理来满足计划要求。
Let us see how the SQL Server Agent can be used to create an SSAS database backup.
让我们看看如何使用SQL Server代理创建SSAS数据库备份。
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.
为此,将命名SQL Server代理作业,创建SSAS数据库管理,并包含步骤。 包括SQL Server Analysis Service命令类型,并且以上脚本用作命令。
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.
在此步骤中,将SQL Server代理服务帐户用作凭据。 因为它是唯一可用的选项,所以您需要确保服务代理帐户应具有必要的权限才能备份SSAS数据库。
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.
执行SSAS数据库备份的另一种方法是使用SQL Server集成服务(SSIS)。 由于SSIS用作ETL工具,最好将SSAS数据库包含在SSIS包中,以便在ETL进程或Cube进程之前可以备份SSAS数据库。 这将使SSAS数据库管理更加高效,因为在处理OLAP数据库失败的情况下,您可以通过还原它来恢复到备份SSAS数据库。
To perform the SSAS database backup, we can use the Analysis Services Execute DDL Task control flow task in SSIS.
要执行SSAS数据库备份,我们可以使用SSIS中的Analysis Services执行DDL任务控制流任务。
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.
可以配置Analysis Services的执行DDL任务,如以下屏幕快照所示。 SQL Server Agent中使用的脚本也可以在此控件中重用。 创建与SSAS实例的连接后,可以将脚本插入DDL页面中的“源”,如下所示。
SSAS数据库流程 (SSAS Database Process)
Another task of the SSAS Database Management is processing the cubes and data mining tasks.
SSAS数据库管理的另一个任务是处理多维数据集和数据挖掘任务。
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.
为了促进数据挖掘结构的处理,我们可以使用SSIS中的Analysis Services处理任务控制任务。
The following screenshot shows how to process the cubes and dimensions as an SSAS database management task.
以下屏幕截图显示了如何将多维数据集和维度作为SSAS数据库管理任务进行处理。
First, you need to make a connection to the SSAS database and in the following example, the UOK_2020 SSAS database is used.
首先,您需要连接到SSAS数据库,在以下示例中,使用了UOK_2020 SSAS数据库。
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.
您可以选择要处理的整个多维数据集,也可以单独处理对象。 通过单独选择,以后可以取消选择过时的对象。 这将使SSAS数据库管理更加容易。
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.
对于SSAS多维数据集处理,可以选择挖掘模型和挖掘结构,如上面的屏幕快照所示。
Similar to the SSAS database backup, we can execute this from a XMLA script as shown in the below script:
与SSAS数据库备份类似,我们可以从XMLA脚本执行此操作,如以下脚本所示:
<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:
由于您只能处理Analysis控制任务中的一个多维数据集或数据挖掘结构,因此您需要组合多个控制任务,如以下屏幕截图所示:
SSAS数据库同步 (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.
SSAS数据库管理中的另一个重要任务是服务器之间的SSAS同步。 使用同步选项,可以为SSAS数据库提供高可用性。 SSAS数据库同步在某种程度上等效于数据库复制,您可以在其中同步多维数据集,维度和数据挖掘结构。 但是,与数据库复制不同,您将无法同步选定的对象。 相反,您必须同步整个SSAS数据库。
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:
通过右键单击SSAS服务器SQL Server Management Studio中的“数据库”文件夹,您将获得由向导驱动的“同步”选项。 以下是向导的第一个屏幕:
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.
在SSAS数据库同步中,必须在不同的SSAS服务器之间进行同步。 这意味着您无法将数据库同步到同一服务器中。 此外,您需要从目标服务器启动同步,并且在向导中,需要提供源SSAS服务器和数据库名称。 您不能为目标SSAS数据库提供其他名称。 目标SSAS数据库也将使用相同的名称。
The next wizard screen will provide you with the option of security and the compression setting as shown below:
下一个向导屏幕将为您提供安全性选项和压缩设置,如下所示:
![SSS Database synchronization Wizard 2/3](https://www.sqlshack.com/wp-content/uploads/2020/06/sss-database-synchronization-wizard-2-3-e1591008077792.png)
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.
除了将SSAS数据库中的角色和成员复制到目标服务器外,您还可以在同步期间进行压缩。 如果要通过网络同步SSAS数据库,这将是非常有用的选择,这样可以减少网络带宽。
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:
您可以决定现在是否应该执行同步,还是要将命令保存到XMLA脚本中,如下所示:
<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.
您可以在SSIS包的“ Analysis Services执行DDL任务”中使用此脚本,并相应地进行提示。
一站式SSIS封装 (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:
我们已经讨论了SSAS数据库管理的三个方面,即备份,处理和同步。 在实际的实现中,我们需要将它们一起使用,以便于计划。 我们可以使用SSIS包,如以下屏幕截图所示:
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.
如上面的屏幕快照所示,我们需要在执行此过程之前执行SSAS数据库备份。 由于我们可以并行备份它们,因此我们使用了Sequence容器。 完成所有备份后,接下来是处理多维数据集和数据挖掘结构。 处理完成后,next将与其他服务器同步。
结论 (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.
SSAS数据库用于利用多维数据集和数据挖掘结构分析和预测数据。 由于这些结构中存在不同的对象,因此讨论SSAS数据库管理非常重要。 我们讨论了备份,流程和同步这三个方面。 所有这三个任务都可以通过SSIS完成,然后我们可以据此安排时间。
ssas还原数据库