Administering Analysis Services

Now let's take a look at some of the changes in the administration of Analysis Services. Almost every aspect of administration has some noteworthy changes in SQL Server 2005.

One of the first notable areas is the location of the server/instance configuration properties. The properties associated with Analysis Services are more accessible than in previous implementations. Some were previously available only through registry entries. Now you can right-click the Analysis Services instance in Object Explorer and select Properties to launch the Analysis Server Properties dialog. The configurations display now closely resembles that in SQL Server, with a display of the current value, default value, and whether a restart of the service is required. The Show Advanced (All) Properties check box at the bottom of the screen exposes additional configuration values. Figure 13.7 shows the Analysis Server Properties dialog.

Figure 13.7. The Analysis Server Properties dialog provides a convenient way to view and edit Analysis Services instance configuration properties.

Full explanation of all available properties is beyond the scope of this chapter, but the following sections describe what you need to do in order to implement UBO.

Implementing UBO

UBO is mentioned previously as a means of designing custom aggregations based on a workload. The first step is to provide a workload to Analysis Services. You accomplish this by turning on the query log. Several properties are available for controlling query log behavior. Best practice continues to indicate placing the query log in a SQL Server database. To do this, you must enter a connection string in the Log\QueryLog\ QueryLogConnectionString property by using the Analysis Server Properties dialog. You select the Value cell and click the ellipsis (...) button to specify the server connection details. The name of the SQL Server table is controlled by the Log\QueryLog\ QueryLogTableName property. You need to modify the Log\QueryLog\QueryLogSampling property for the duration of the query sampling period in order to sample queries more frequently. This property defaults to 10, but you can lower this value to sample every nth query instead of every 10th. There is a slight performance hit involved with this, so it is good to set the property back to the original value or higher when sampling is complete.

One final step is necessary in order to have Analysis Services create the log table in SQL Server. You need to check the Show Advanced (All) Properties check box to expose the Feature\CreateQueryLogTable property. Then you need to set this property value to TRue. When you look at the Restart column for this property, you should see that this requires a restart of Analysis Services in order to take effect. After you restart, your table is created in SQL Server, and you see rows appear in the log after Analysis Services cubes have been queried.

The UBO Wizard has not changed significantly since Analysis Services 2000. It can be invoked through SSMS. If you have sampled a workload by following the steps just outlined, the UBO Wizard allows you to filter on information, such as queries run by a specific user or queries that exceeded a specified duration, and it attempts to design new aggregations from which those queries would benefit. Note that UBO aggregation design still follows the rules for good aggregation design, which weigh the cost of an aggregation versus the benefit.

In Analysis Services 2000, you could automate UBO by using the DSO COM API via the CubeAnalyzer and PartitionAnalyzer objects. This continues to be true in Analysis Services 2005 but is made easier by the fact that SSIS can now expand beyond ActiveX scripting. Some of the limitations of VBScript required that the NextAnalysisStep method be invoked externally in a Visual Basic wrapper. This is not the case when you're using the Script task in SSIS.

Analysis Services Database Backup

You can back up Analysis Services databases to an operating system file to create a snapshot of the database at a point in time. This is similar to the archive functionality in Analysis Services 2000, but with additional options. The backup options include an indication as to whether to overwrite the destination file if it exists, whether to apply compression, and whether to encrypt the backup file. You can also indicate whether to include security information with the backup. This is useful on production backups, but you can turn it off when you're backing up a database with the intent to restore it on a different system. You can also indicate remote partitions in the Backup Database dialog. This is something that had to be handled separately in Analysis Services 2000. You connect to an Analysis Services instance by using Object Explorer in SSMS. Then you right-click any database and select Back Up to open the Backup Database dialog.

Cube and Mining Model Processing

Cube and mining model processing is the same in concept in SQL Server 2005 as in earlier implementations. The introduction of UDM changes this in terms of how manual a process it is. There are also a few new processing options, such as Process Index, Process Data, and Unprocess, that allow you to get more specific about what information is to be acted on. Also note that object processing is now done in parallel by default. For instance, when you process a cube, all partitions in all measure groups in the cube are processed in parallel. There are also new tasks in SSIS to more specifically define and perform the processing of cubes, dimensions, and mining models.