SQL Server Analysis Services – Administering and Monitoring Analysis Services

Exclusive offer: get 50% off this eBook here
SQL Server Analysis Services 2012 Cube Development Cookbook

SQL Server Analysis Services 2012 Cube Development Cookbook — Save 50%

More than 100 recipes to develop Business Intelligence solutions using Analysis Services with this book and ebook

$35.99    $18.00
by Baya Dewald Paul Turley Steve Hughes | December 2013 |

In this article, by Baya Dewald, one of the three authors of the book SQL Server Analysis Services 2012 Cube Development Cookbook we will cover:

  • SSAS instance con figuration options
  • Creating and dropping databases
  • Monitoring SSAS instance using Activity Viewer
  • Monitoring SSAS instance using DMVs
  • Cancelling a session
  • Checking whether cubes are accessible
  • Checking SSAS object sizes programmatically
  • Backup and restore
  • Synchronization
  • Detaching and attaching databases

(For more resources related to this topic, see here.)

If your environment has only one or a handful of SSAS instances, they can be managed by the same database administrators managing SQL Server and other database platforms. In large enterprises, there could be hundreds of SSAS instances managed by dedicated SSAS administrators. Regardless of the environment, you should become familiar with the configuration options as well as troubleshooting methodologies. In large enterprises, you might also be required to automate these tasks using the Analysis Management Objects (AMO) code.

Analysis Services is a great tool for building business intelligence solutions. However, much like any other software, it does have its fair share of challenges and limitations.

Most frequently encountered enterprise business intelligence system goals include quick provision of relevant data to the business users and assuring excellent query performance. If your cubes serve a large, global community of users, you will quickly learn that SSAS is optimized to run a single query as fast as possible. Once users send a multitude of heavy queries in parallel, you can expect to see memory, CPU, and disk-related performance counters to quickly rise, with a corresponding increase in query execution duration which, in turn, worsens user experience. Although you could build aggregations to improve query performance, doing so will lengthen cube processing time, and thereby, delay the delivery of essential data to decision makers. It might also be tempting to consider using ROLAP storage mode in lieu of MOLAP so that processing times are shorter, but MOLAP queries usually outperform ROLAP due to heavy compression rates. Hence, figuring out the right storage mode and appropriate level of aggregations is a great balancing act. If you cannot afford using ROLAP, and query performance is paramount to successful cube implementation, you should consider scaling your solution. You have two options for scaling, given as follows:

  • Scaling up: This option means purchasing servers with more memory, more CPU cores, and faster disk drives
  • Scaling out: This option means purchasing several servers of approximately the same capacity and distributing the querying workload across multiple servers using a load balancing tool

SSAS lends itself best to the second option—scaling out. Later in this article you will learn how to separate processing and querying activities and how to ensure that all servers in the querying pool have the same data.

SSAS instance configuration options

All Analysis Services configuration options are available in the msmdsrv.ini file found in the config folder under the SSAS installation directory. Instance administrators can also modify some, but not all configuration properties, using SQL Server Management Studio (SSMS).

SSAS has a multitude of properties that are undocumented—this normally means that such properties haven't undergone thorough testing, even by the software's developers. Hence, if you don't know exactly what the configuration setting does, it's best to leave the setting at default value. Even if you want to test various properties on a sandbox server, make a copy of the configuration file prior to applying any changes.

How to do it...

To modify the SSAS instance settings using the configuration file, perform the following steps:

  1. Navigate to the config folder within your Analysis Services installation directory. By default, this will be C:\Program Files\Microsoft SQL Server\MSAS11.instance_name\OLAP\Config.
  2. Open the msmdsrv.ini file using Notepad or another text editor of your choice. The file is in the XML format, so every property is enclosed in opening and closing tags.
  3. Search for the property of interest, modify its value as desired, and save the changes.
  4. For example, in order to change the upper limit of the processing worker threads, you would look for the <ThreadPool><Process><MaxThreads> tag sequence and set the values as shown in the following excerpt from the configuration file:

    <Process>       <MinThreads>0</MinThreads>       <MaxThreads>250</MaxThreads>      <PriorityRatio>2</PriorityRatio>       <Concurrency>2</Concurrency>       <StackSizeKB>0</StackSizeKB>       <GroupAffinity/>     </Process>

To change the configuration using SSMS, perform the following steps:

  1. Connect to the SSAS instance using the instance administrator account and choose Properties. If your account does not have sufficient permissions, you will get an error that only administrators can edit server properties.
  2. Change the desired properties by altering the Value column on the General page of the resulting dialog, as shown in the following screenshot:

     

  3. Advanced properties are hidden by default. You must check the Show Advanced (All) Properties box to see advanced properties. You will not see all the properties in SSMS even after checking this box. The only way to edit some properties is by editing msmdsrv.ini as previously discussed.
  4. Make a note of the Reset Default button in the bottom-right corner. This button comes in handy if you've forgotten what the configuration values were before you changed them and want to revert to the default settings. The default values are shown in the dialog box, which can provide guidance as to which properties have been altered.
  5. Some configuration settings require restarting the SSAS instance prior to being executed. If this is the case, the Restart column will have a value of Yes.
  6. Once you're happy with your changes, click on OK and restart the instance if necessary. You can restart SSAS using the Services.msc applet from the command line using the NET STOP / NET START commands, or directly in SSMS by choosing the Restart option after right-clicking on the instance.

How it works...

Discussing every SSAS property would make this article extremely lengthy; doing so is well beyond the scope of the book. Instead, in this section, I will summarize the most frequently used properties.

Often, synchronization has to copy large partition datafiles and aggregation files. If the timeout value is exceeded, synchronization fails. Increase the value of the <Network><Listener><ServerSendTimeout> and <Network><Listener><ServerReceiveTimeout> properties to allow a longer time span for copying each file.

By default, SSAS can use a lazy thread to rebuild missing indexes and aggregations after you process partition data. If the <OLAP><LazyProcessing><Enabled> property is set to 0, the lazy thread is not used for building missing indexes—you must use an explicit processing command instead. The <OLAP><LazyProcessing><MaxCPUUsage> property throttles the maximum CPU that could be used by the lazy thread. If efficient data delivery is your topmost priority, you can exploit the ProcessData option instead of ProcessFull. To build aggregations after the data is loaded, you must set the partition's ProcessingMode property to LazyAggregations. The SSAS formula engine is single threaded, so queries that perform heavy calculations will only use one CPU core, even on a multiCPU computer. The storage engine is multithreaded; hence, queries that read many partitions will require many CPU cycles. If you expect storage engine heavy queries, you should lower the CPU usage threshold for LazyAggregations.

By default, Analysis Services records subcubes requested for every 10th query in the query log table. If you'd like to design aggregations based on query logs, you should change the <Log><QueryLog><QueryLogSampling> property value to 1 so that the SSAS logs subcube requests for every query.

SSAS can use its own memory manager or the Windows memory manager. If your SSAS instance consistently becomes unresponsive, you could try using the Windows memory manager. Set <Memory><MemoryHeapType> to 2 and <Memory><HeapTypeForObjects> to 0. The Analysis Services memory manager values are 1 for both the properties. You must restart the SSAS service for the changes to these properties to take effect.

The <Memory><PreAllocate> property specifies the percentage of total memory to be reserved at SSAS startup. SSAS normally allocates memory dynamically as it is required by queries and processing jobs. In some cases, you can achieve performance improvement by allocating a portion of the memory when the SSAS service starts.

Setting this value will increase the time required to start the service. The memory will not be released back to the operating system until you stop the SSAS service. You must restart the SSAS service for changes to this property to take effect.

The <Log><FlightRecorder><FileSizeMB>and <Log><FlightRecorder><LogDurationSec> properties control the size and age of the FlightRecorder trace file before it is recycled. You can supply your own trace definition file to include the trace events and columns you wish to monitor using the <Log><FlightRecorder><TraceDefinitionFile> property. If FlightRecorder collects useful trace events, it can be an invaluable troubleshooting tool. By default, the file is only allowed to grow to 10 MB or 60 minutes. Long processing jobs can take up much more space, and their duration could be much longer than 60 minutes. Hence, you should adjust the settings as necessary for your monitoring needs. You should also adjust the trace events and columns to be captured by FlightRecorder. You should consider adjusting the duration to cover three days (in case the issue you are researching happens over a weekend).

The <Memory><LowMemoryLimit> property controls the point—amount of memory used by SSAS—at which the cleaner thread becomes actively engaged in reclaiming memory from existing jobs. Each SSAS command (query, processing, backup, synchronization, and so on) is associated with jobs that run on threads and use system resources. We can lower the value of this setting to run more jobs in parallel (though the performance of each job could suffer). Two properties control the maximum amount of memory that a SSAS instance could use. Once memory usage reaches the value specified by <Memory><TotalMemoryLimit>, the cleaner thread becomes particularly aggressive at reclaiming memory. The <Memory><HardMemoryLimit> property specifies the absolute memory limit—SSAS will not use memory above this limit. These properties are useful if you have SSAS and other applications installed on the same server computer. You should reserve some memory for other applications and the operating system as well. When HardMemoryLimit is reached, SSAS will disconnect the active sessions, advising that the operation was cancelled due to memory pressure.

All memory settings are expressed in percentages if the values are less than or equal to 100. Values above 100 are interpreted as kilobytes. All memory configuration changes require restart of the SSAS service to take effect.

In the prior releases of Analysis Services, you could only specify the minimum and maximum number of threads used for queries and processing jobs. With SSAS 2012, you can also specify the limits for the input/output job threads using the <ThreadPool><IOProcess> property.

The <Process><IndexBuildThreshold> property governs the minimum number of rows within a partition for which SSAS will build indexes. The default value is 4096. SSAS decides which partitions it needs to scan for each query based on the partition index files. If the partition does not have indexes, it will be scanned for all the queries. Normally, SSAS can read small partitions without greatly affecting query performance. But if you have many small partitions, you should lower the threshold to ensure each partition has indexes.

The <Process><BufferRecordLimit> and <Process><BufferMemoryLimit> properties specify the number of records for each memory buffer and the maximum percentage of memory that can be used by a memory buffer. Lower the value of these properties to process more partitions in parallel. You should monitor processing using the SQL Profiler to see if some partitions included in the processing batch are being processed while the others are in waiting.

The <ExternalConnectionTimeout> and <ExternalCommandTimeout> properties control how long an SSAS command should wait for connecting to a relational database or how long SSAS should wait to execute the relational query before reporting timeout. Depending on the relational source, it might take longer than 60 seconds (that is, the default value) to connect. If you encounter processing errors without being able to connect to the relational source, you should increase the ExternalConnectionTimeout value. It could also take a long time to execute a query; by default, the processing query will timeout after one hour. Adjust the value as needed to prevent processing failures.

The contents of the <AllowedBrowsingFolders> property define the drives and directories that are visible when creating databases, collecting backups, and so on. You can specify multiple items separated using the pipe (|) character.

The <ForceCommitTimeout> property defines how long a processing job's commit operation should wait prior to cancelling any queries/jobs which may interfere with processing or synchronization. A long running query can block synchronization or processing from committing its transaction. You can adjust the value of this property from its default value of 30 seconds to ensure that processing and queries don't step on each other.

The <Port> property specifies the port number for the SSAS instance. You can use the hostname followed by a colon (:) and a port number for connecting to the SSAS instance in lieu of the instance name. Be careful not to supply the port number used by another application; if you do so, the SSAS service won't start.

The <ServerTimeout> property specifies the number of milliseconds after which a query will timeout. The default value is 1 hour, which could be too long for analytical queries. If the query runs for an hour, using up system resources, it could render the instance unusable by any other connection. You can also define a query timeout value in the client application's connection strings. Client setting overrides the server-level property.

There's more...

There are many other properties you can set to alter SSAS instance behavior. For additional information on configuration properties, please refer to product documentation at http://technet.microsoft.com/en-us/library/ms174556.aspx.

Creating and dropping databases

Only SSAS instance administrators are permitted to create, drop, restore, detach, attach, and synchronize databases. This recipe teaches administrators how to create and drop databases.

Getting ready

Launch SSMS and connect to your Analysis Services instance as an administrator. If you're not certain that you have administrative properties to the instance, right-click on the SSAS instance and choose Properties. If you can view the instance's properties, you are an administrator; otherwise, you will get an error indicating that only instance administrators can view and alter properties.

How to do it...

To create a database, perform the following steps:

  1. Right-click on the Databases folder and choose New Database. Doing so launches the New Database dialog shown in the following screenshot.
  2. Specify a descriptive name for the database, for example, Analysis_Services_Administration. Note that the database name can contain spaces. Each object has a name as well as an identifier. The identifier value is set to the object's original name and cannot be changed without dropping and recreating the database; hence, it is important to come up with a descriptive name from the very beginning. You cannot create more than one database with the same name on any SSAS instance.
  3. Specify the storage location for the database. By default, the database will be stored under the \OLAP\DATA folder of your SSAS installation directory. The only compelling reason to change the default is if your data drive is running out of disk space and cannot support the new database's storage requirements.

  4. Specify the impersonation setting for the database. You could also specify the impersonation property for each data source. Alternatively, each data source can inherit the DataSourceImpersonationInfo property from the database-level setting. You have four choices as follows:
    • Specific user name (must be a domain user) and password: This is the most secure option but requires updating the password if the user changes the password
    • Analysis Services service account
    • Credentials of the current user: This option is specifically for data mining
    • Default: This option is the same as using the service account option
  5. Specify an optional description for the database.
  6. As with majority of other SSMS dialogs, you can script the XMLA command you are about to execute by clicking on the Script button.

To drop an existing database, perform the following steps:

  1. Expand the Databases folder on the SSAS instance, right-click on the database, and choose Delete.
  2. The Delete objects dialog allows you to ignore errors; however, it is not applicable to databases. You can script the XMLA command if you wish to review it first.
  3. An alternative way of scripting the DELETE command is to right-click on the database and navigate to Script database as | Delete To | New query window.

Monitoring SSAS instance using Activity Viewer

Unlike other database systems, Analysis Services has no system databases. However, administrators still need to check the activity on the server, ensure that cubes are available and can be queried, and there is no blocking. You can exploit a tool named Analysis Services Activity Viewer 2008 to monitor SSAS Versions 2008 and later, including SSAS 2012. This tool is owned and maintained by the SSAS community and can be downloaded from www.codeplex.com.

Activity Viewer allows viewing active and dormant sessions, current XMLA and MDX queries, locks, as well as CPU and I/O usage by each connection. Additionally, you can define rules to raise alerts when a particular condition is met.

How to do it...

To monitor an SSAS instance using Activity Viewer, perform the following steps:

  1. Launch the application by double-clicking on ActivityViewer.exe.
  2. Click on the Add New Connection button on the Overview tab. Specify the hostname and instance name or the hostname and port number for the SSAS instance and then click on OK.
  3. For each SSAS instance you connect to, Activity Viewer adds a new tab. Click on the tab for your SSAS instance. Here, you will see several pages as shown in the following screenshot:

    • Alerts: This page shows any sessions that met the condition found in the Rules page.
    • Users: This page displays one row for each user as well as the number of sessions, total memory, CPU, and I/O usage.
    • Active Sessions: This page displays each session that is actively running an MDX, Data Mining Extensions (DMX), or XMLA query. This page allows you to cancel a specific session by clicking on the Cancel Session button.
    • Current Queries: This page displays the actual command's text, number of kilobytes read and written by the command, and the amount of  CPU time used by the command. This page allows you to cancel a specific query by clicking on the Cancel Query button.
    • Dormant Sessions: This page displays sessions that have a connection to the SSAS instance but are not currently running any queries. You can also disconnect a dormant session by clicking on the Cancel Session button.
    • CPU: This page allows you to review the CPU time used by the session as well as the last command executed on the session.
    • I/O: This page displays the number of reads and writes as well as the kilobytes read and written by each session.
    • Objects: This page shows the CPU time and number of reads affecting each dimension and partition. This page also shows the full path to the object's parent; this is useful if you have the same naming convention for partitions in multiple measure groups. Not only do you see the partition name, but also the full path to the partition's measure group. This page also shows the number of aggregation hits for each partition. If you find that a partition is frequently queried and requires many reads, you should consider building aggregations for it.
    • Locks: This page displays the locks currently in place, whether already granted or waiting. Be sure to check the Lock Status column—the value of 0 indicates that the lock request is currently blocked.
    • Rules: This page allows defining conditions that will result in an alert. For example, if the session is idle for over 30 minutes or if an MDX query takes over 30 minutes, you should get alerted.

How it works...

Activity Viewer monitors Analysis Services using Dynamic Management Views (DMV). In fact, capturing queries executed by Activity Viewer using SQL Server Profiler is a good way of familiarizing yourself with SSAS DMV's.

For example, the Current Queries page checks the $system.DISCOVER_COMMANDS DMV for any actively executing commands by running the following query:

SELECT SESSION_SPID,COMMAND_CPU_TIME_MS,COMMAND_ELAPSED_TIME_MS,   COMMAND_READ_KB,COMMAND_WRITE_KB, COMMAND_TEXT FROM $system.DISCOVER_COMMANDS WHERE COMMAND_ELAPSED_TIME_MS > 0 ORDER BY COMMAND_CPU_TIME_MS DESC

The Active Sessions page checks the $system.DISCOVER_SESSIONS DMV with the session status set to 1 using the following query:

SELECT SESSION_SPID,SESSION_USER_NAME, SESSION_START_TIME,   SESSION_ELAPSED_TIME_MS,SESSION_CPU_TIME_MS, SESSION_ID FROM $SYSTEM.DISCOVER_SESSIONS WHERE SESSION_STATUS = 1 ORDER BY SESSION_USER_NAME DESC

The Dormant sessions page runs a very similar query to that of the Active Sessions page, except it checks for sessions with SESSION_STATUS=0—sessions that are currently not running any queries. The result set is also limited to top 10 sessions based on idle time measured in milliseconds.

The Locks page examines all the columns of the $system.DISCOVER_LOCKS DMV to find all requested locks as well as lock creation time, lock type, and lock status. As you have already learned, the lock status of 0 indicates that the request is blocked, whereas the lock status of 1 means that the request has been granted. Analysis Services blocking can be caused by conflicting operations that attempt to query and modify objects. For example, a long running query can block a processing or synchronization job from completion because processing will change the data values. Similarly, a command altering the database structure will block queries. The database administrator or instance administrator can explicitly issue the LOCK XMLA command as well as the BEGIN TRANSACTION command. Other operations request locks implicitly.

The following table documents most frequently encountered Analysis Services lock types:

Lock type identifier

Description

Acquired for

2

Read lock

Processing to read metadata.

4

Write lock

Processing to write data after it is read from relational sources.

8

Commit shared

During the processing, restore or synchronization commands.

16

Commit exclusive

Committing the processing, restore, or synchronization transaction when existing files are replaced by new files.

 

SQL Server Analysis Services 2012 Cube Development Cookbook More than 100 recipes to develop Business Intelligence solutions using Analysis Services with this book and ebook
Published: December 2013
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

Monitoring SSAS instance using DMVs

In the previous section you learned about a few DMVs referenced by Activity Viewer. Analysis Services supports many other DMVs not used by Activity Viewer. You can query DMVs directly using the SSMS or ASCMD command-line utility.

How to do it...

You can query DMVs by executing simple SELECT statements in the MDX query window within SSMS by performing the following steps:

  1. Navigate to FILE | New | Analysis Services MDX Query.
  2. Connect to your SSAS instance as an administrator.
  3. Paste the DMV queries you captured using SQL Server Profiler (see the previous section, Monitoring SSAS instance using Activity Viewer). The SELECT statements that you can use for querying DMVs are somewhat limited:
    • You can only query one DMV at a time and cannot join multiple DMVs.
    • You can extract a subset of all the rows by specifying the TOP keyword and the ORDER BY clause.
    • You can extract a subset of all the columns by explicitly listing column names or get all the columns using the star (*) operator.
    • You cannot use the GROUP BY clause to aggregate data. You can set up a linked server from the SQL Server to Analysis Services and import DMV data into a relational table first if you wish to use GROUP BY or any other syntax elements not supported by SSAS DMVs.

How it works...

In this section I will discuss a few DMVs that are particularly useful for monitoring and troubleshooting Analysis Services along with the SELECT statements used to query them.

To get a list of all the cubes and dimensions within the current database, you can query $system.MDSCHEMA_CUBES. Dimensions will be returned in the result set because you can query each dimension as though it were a cube using the $Dimension_Name notation. To obtain additional dimension specific metadata, such as the dimension's default hierarchy, dimension type, and whether dimension is visible, check the $system.MDSCHEMA_DIMENSIONS DMV. Additional metadata for dimension hierarchies is available in $system.MDSCHEMA_HIERARCHIES. You can query all the MDSCHEMA_* DMVs as well as most other DMVs by simply running the SELECT * FROM DMV_NAME query.

To get a list of traces that currently monitor your SSAS instance, check $system.DISCOVER_TRACES as follows:

SELECT * FROM $system.DISCOVER_TRACES

You can find additional trace related metadata in DISCOVER_TRACE_COLUMNS and DISCOVER_TRACE_EVENT_CATEGORIES. To list the transactions running on the current instance, check DISCOVER_TRANSACTIONS.

You can use $system.DISCOVER_DIMENSION_STAT to get the member counts for each attribute. You must specify the database name and dimension name when querying this DMV. For example, the following query gets attribute member counts for the Promotion dimension in the AdventureWorks_Sample database:

SELECT * FROM SYSTEMRESTRICTSCHEMA (    $SYSTEM.DISCOVER_DIMENSION_STAT, DIMENSION_NAME = 'promotion'  , DATABASE_NAME='AdventureWorks_Sample')  ORDER BY Attribute_Count DESC

The following table shows the results:

DATABASE_NAME

DIMENSION_NAME

ATTRIBUTE_NAME

ATTRIBUTE_COUNT

AdventureWorks_ Sample

Promotion

Discount Percent

11

AdventureWorks_ Sample

Promotion

End Date

11

AdventureWorks_ Sample

Promotion

Start Date

9

AdventureWorks_ Sample

Promotion

Promotion Type

7

AdventureWorks_ Sample

Promotion

Min Quantity

7

AdventureWorks_ Sample

Promotion

Max Quantity

6

AdventureWorks_ Sample

Promotion

Promotion Category

4

You can query $system.DISCOVER_PARTITION_STAT much the same way as DISCOVER_DIMENTION_STAT, except you must provide cube name as well as measure group name and partition name, as shown in the following code snippet. The result will be the aggregation name and aggregation size. SELECT * FROM SYSTEMRESTRICTSCHEMA (

  $SYSTEM.DISCOVER_PARTITION_STAT, PARTITION_NAME =
    'Internet_Sales_2005', CUBE_NAME = 'Adventure Works',
      MEASURE_GROUP_NAME = 'Internet Sales',
        DATABASE_NAME='AdventureWorks_Sample')

You can query $system.DISCOVER_PERFORMANCE_COUNTERS to obtain the current value of a specific performance counter, for example: SELECT * FROM SYSTEMRESTRICTSCHEMA(

   $SYSTEM.DISCOVER_PERFORMANCE_COUNTERS, PERF_COUNTER_NAME =
    '\MSOLAP$SQL2012:Threads\Processing Pool Busy I/O job
      Threads')

There's more...

As you learned in this section, DMVs contain a lot of useful information about SSAS metadata and internals even though the product does not have any system databases. Please reference product documentation for the full list of DMVs at http://msdn.microsoft.com/en-us/library/hh230820.aspx.

Cancelling a session

If you have managed SQL Server or other relational database systems, you should be familiar with the unambiguous KILL command that disconnects the offending session and terminates the query executed on that session. Analysis Services has a similar statement, though it uses a somewhat milder term, CANCEL, perhaps more appropriate for the type of operation it performs. After all, the impact of terminating the session isn't always immediate even in relational systems. If a user is running a large transaction affecting thousands or millions of records, it could take a long time to roll such transactions back. The CANCEL command is also different from KILL in a sense that it doesn't immediately terminate the session or query. Rather it tags the session to be terminated. SSAS periodically checks sessions and takes action on those sessions that have been tagged.

How to do it...

You can terminate a session using SSMS or ASCMD by performing the following steps:

  1. Query $system.DISCOVER_SESSIONS, as shown earlier in this article, to find the server process identifier (SPID) of the session you wish to cancel.
  2. Execute the XMLA command, similar to the following, to tag the session to be cancelled (substitute the correct SPID value):

    <Cancel xmlns = "http://
      schemas.microsoft.com/analysisservices/2003/engine">   <SPID>2790</SPID> </Cancel>

Checking whether cubes are accessible

Microsoft does not provide any database consistency checker (DBCC) command for SSAS. However, this does not imply that Analysis Services objects could never get corrupted. In order to assure whether your cubes can be queried, you can set up a job that periodically runs very lightweight queries against each cube within your SSAS plant.

How to do it...

  1. To check whether your cubes are available for querying, you could use the ASCMD utility to run a query similar to the following:

    WITH member measures.is_accessible AS "1" SELECT measures.is_accessible ON 0 FROM [adventure works]

Checking SSAS object sizes programmatically

Most SSAS databases contain data for a finite time span instead of perpetually accumulating data. After a while, data becomes stale and irrelevant for frequent analysis and should be either purged or migrated to a historical database. Since the volumes of the data we work with tends to grow quickly, we need to have a way of quickly checking the available disk space as well as the size of each object.

How to do it...

Microsoft supplies a couple of handy commands you could execute directly from the SSMS MDX query window to obtain the free disk space amount on the data drive.

  1. You can execute SYSTEMGETLOGICALDrives within SSMS or from the ASCMD utility. The command doesn't take any parameters, as shown in the following code snippet:

    SYSTEMGETLOGICALDRIVES

    The following table shows the result:

    Drive

    Free Space

    E:

    5309

    The output of the SYSTEMGETLOGICALDRIVES command depends on the AllowedBrowsingFolders property value. Had I included C:\ in the AllowedBrowsingFolders property value, the output would include both C and E drives.

  2. You could use the SYSTEMGETSUBDIRS command to get subdirectories of a folder included in the AllowedBrowsingFolders property. For example, the following command retrieves folders for each database found under the SSAS data folder:

    SYSTEMGETSUBDIRS 'E:\Program Files\Microsoft SQL Server\MSAS11.SQL2012\OLAP\Data'

There's more...

As you can tell, the commands discussed in this section are very useful but somewhat limited because they don't provide the size of each data folder or each partition. SSMS allows obtaining the estimated size for one partition at a time (simply right-click on the partition and choose Properties); going through hundreds or thousands of partitions to collect the size of each is unacceptable. Fortunately, we can obtain the necessary information with a little effort, as shown in the following PowerShell script:

# script to obtain partition and measure group sizes #load the Analysis Services assembly first so
we can instantiate Analysis Services object: [Reflection.Assembly]::LoadFrom("E:\Program Files\Microsoft SQL Server\
110\SDK\Assemblies\Microsoft.AnalysisServices.dll")|out-null $instance="Julia-PC\SQL2012" $amoServer= new-object Microsoft.AnalysisServices.Server #connect to the instance $amoServer.Connect($instance) #connect to the db and cube: $db=$amoServer.databases.GetByName("AdventureWorks_Sample") $cube = $db.cubes.GetByName("Adventure Works") #loop over measure group partitions: foreach ($mg in $cube.MeasureGroups)     {     $mg_size = 0     $mg_name = $mg.name     write-host "checking partitions in $mg_name measure group: "     foreach ($partition in $mg.partitions)         {         $size = $partition.EstimatedSize;         $size = $size / 1024 / 1024         $size = [math]::round($size, 3)         $partition_name = $partition.name;         write-host "partition $partition_name is $size MB"         $mg_size = $mg_size + $size         }         write-host "measure group $mg_name is $mg_size MB"     } #disconnect from the server $amoServer.disconnect()

The output from this code will look similar to the following:

checking partitions in Internet Sales measure group: partition Internet_Sales_2005 is 0.029 MB partition Internet_Sales_2006 is 0.08 MB partition Internet_Sales_2007 is 0 MB measure group Internet Sales is 3.145 MB checking partitions in Internet Orders measure group: partition Internet_Orders_2005 is 0.739 MB partition Internet_Orders_2006 is 0.569 MB partition Internet_Orders_2007 is 0.501 MB measure group Internet Orders is 2.243 MB

Scaling out SSAS solution

As you learned in the introductory section of this article, Analysis Services is tuned for running a single query as fast as possible. This means that a single large query can use much of the available system resources and force other queries to wait. As a cube developer or database administrator, you need to take time to educate users on how to best use SSAS's power without bringing the production server to its knees. However, even fine-tuned cube design and queries leave room for scalability issues—a single server computer can only have a finite amount of system resources and, hence, can only serve a limited number of user requests. Additionally, you will often find that processing and querying activities conflict with each other; much like in any other database system, data readers can block writers and vice versa.

You have several methods at your disposal for scaling out Analysis Services solutions, given as follows:

  • Backup databases on the processing instance and restore the databases to the querying instances
  • Synchronize databases from the processing instances to querying instances
  • Detach databases from the processing instance and attach them to querying instances in the read-only mode

Let's learn how to implement these scale out methods while considering the benefits and drawbacks of each.

SQL Server Analysis Services 2012 Cube Development Cookbook More than 100 recipes to develop Business Intelligence solutions using Analysis Services with this book and ebook
Published: December 2013
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

Backup and restore

Assuring data availability is the primary responsibility of each database administrator, so collecting and validating SSAS database backups is essential for your job security. You can only collect full database backups—Analysis Services does not support backups at individual cube, measure group, or dimension level nor does it support incremental or differential backups. Hence, when building SSAS databases, it is imperative to consider which data sets need to be backed up together. For example, if 80 percent of user queries examine current data and only 20 percent check historical data, you could implement two databases—one for historical and the other for recent data. If historical data changes rarely, you may only need to backup the historical database once a month, whereas the database with daily changes should be backed up more frequently. Much like other database platforms, when thinking about your SSAS backup strategy, you should consider how much data loss is acceptable. Some organizations consider Analysis Services backups optional because the underlying relational data source can be used to rebuild the MOLAP database. Although this is a valid statement, rebuilding SSAS database structure and reprocessing all objects could take considerably longer than restoring it from a valid backup. In addition to being one of the methods for assuring continuous data availability, backup and restore could also be used for scaling out SSAS implementations.

How to do it...

To collect Analysis Services database backup, perform the following steps:

  1. Connect to the instance using SSMS, expand the Databases folder, right-click on the database of interest, and choose Back Up. Doing so activates the Backup Database - AdventureWorks_Sample dialog shown in the following screenshot.
  2. Supply the full path and name of the backup file. The file extension must be .abf. If you do not explicitly specify the path, the backup file will be stored in the backup directory as defined in the msmdsrv.ini configuration file.
  3. Specify whether the backup should be compressed. Analysis Services initially reserves more space than it actually requires for storing the backup and subsequently compresses the file (if the Apply compression box is checked).
  4. Specify whether the backup should be encrypted with a password. Use this option judiciously; if you forget or misplace the password, the backup file is useless because you won't be able to restore the database from it. However, if you don't encrypt your backups, you need to store them securely, else someone could restore your backup and access all your organization's data.
  5. Specify if the existing backup file should be overwritten and whether any remote partitions should be backed up. Remote partitions are seldom used.
  6. Take a note of the advice included at the bottom of the Remote partition backup location dialog regarding the ROLAP, HOLAP, and MOLAP storage modes and corresponding backup actions. You can script the XMLA backup command or execute directly by clicking on the OK button.

     

After you collect the backup, you can restore it to another server for scaling out or to the same server in case the database becomes corrupted. Perform the following steps to restore the database:

  1. Connect to the instance where you wish to restore the database, expand the Databases folder, right-click on the Databases folder, and choose Restore. This activates the Restore Database dialog shown in the following screenshot.
  2. Specify the full path and name of the backup file. You can click on the Browse... button to find the file (this will only allow you to browse the folders specified in the SSAS instance properties); alternatively, you can type in the entire string into the textbox.
  3. Specify the name of the database you wish to restore. You could restore over an existing database, in which case you must check the Allow database overwrite box or you can restore by creating a new database.
  4. Specify the security settings for the restore operation; you can either copy all security roles or skip security. This option only applies to existing databases. For example, if you are restoring the production database backup to a test SSAS instance, the security settings in the two environments might be different. In this case, you can skip security during restore.
  5. If the backup file was encrypted, you must specify the password.
  6. The Storage location textbox allows specifying an optional alternate/nondefault location for database files. By default, files will be copied to the \OLAP\data folder of the SSAS installation directory specified in the SSAS instance properties.
  7. The Partitions page of the dialog allows restoring remote partitions as well as specifying alternate locations for individual partitions. Use this flexibility with care; placing datafiles in a nondefault location makes debugging difficult.

How it works...

Analysis Services creates a temporary folder—the folder name is globally unique identifier (GUID)—for the database being restored and extracts all datafiles into this folder. After all the files have been copied, the existing database folder is deleted, and the temporary folder is renamed from GUID to the actual database name.

Keep in mind that if you're restoring an existing database it will continue to remain online—available for queries—while the restore command is running. Therefore, you will need sufficient disk space to hold both the existing and new copy of the database until the restore command copies all files to the temporary data folder. So, if you are restoring a 100 GB database, you will need at least 200 GB to continue using the existing database while it is being restored.

If you host multiple databases on the same instance, you can run multiple backup/restore commands in parallel. However, you cannot process or make any structural changes to the database being backed up since doing so will cause the backup command to fail.

Synchronizing databases

Analysis Services database synchronization is a very useful feature that allows the database administrator to copy database files from the source to target SSAS instance. Synchronization is always incremental; it only copies the files that are different on the source instance as compared to the same files on the target instance. This enables the administrator to ensure that the source and target instance have the same data with minimal effort. Because synchronization is incremental, it could be much faster than collecting the backup and restoring the full database to the target instance.

Synchronization always works in the pull mode—the command is executed on the target instance. Synchronization is also single threaded, which means that it only copies one file at a time and there is no way to force copying multiple files in parallel. This normally isn't an issue for dimension files because these tend to be relatively small. However, partition data and aggregation files can get quite large and copying only one file at a time could be prohibitively slow. You can synchronize an existing database or synchronize to an instance that currently does not have the database; in this case, SSAS will have to copy every file to the target.

How to do it...

To synchronize a database, perform the following steps:

  1. Connect to the target instance using SSMS, right-click on the Databases folder, and choose Synchronize. Skip the Welcome screen.
  2. Specify the source SSAS instance and the database you wish to copy. The same screen allows you to choose the destination (target) data folder. Unless your data drive is out of disk space and you must choose an alternate location, use the default values and click on Next.
  3. Review the list of partitions on the next screen to get an idea of how much data will need to be copied.
  4. The screen that follows allows you to choose whether data should be compressed during synchronization; this is the default and recommended option. Additionally, you can choose to copy all security settings, ignore security, or transfer only the security roles without including the role members. Much like the case when restoring the database, security options are useful when transferring data from production to QA or test environments.
  5. The Synchronization wizard allows you to execute synchronization right away or save the command to a file. You do not have the option of scripting the command to the query window or clipboard. The Synchronize XMLA command will look like the following:

    <Synchronize xmlns:xsi = "http://www.w3.org/2001/XMLSchema-
      instance" xmlns:xsd = "http://www.w3.org/2001/XMLSchema"
        xmlns = "http://schemas.microsoft.com/analysisservices/
          2003/engine">   <Source>     <ConnectionString>Provider = MSOLAP.5;Data Source =
          julia-pc\sql2012;Integrated Security = SSPI;Initial
            Catalog = AdventureWorks_Sample</ConnectionString>     <Object>       <DatabaseID>AdventureWorks_Sample</DatabaseID>     </Object>   </Source>   <SynchronizeSecurity>CopyAll</SynchronizeSecurity>   <ApplyCompression>true</ApplyCompression> </Synchronize>

How it works...

As you already know, the Synchronize command runs on the target instance. However, you cannot process or make changes to the source database while it is being synchronized. This is implemented through an explicit BEGIN TRANSACTION command on the source instance. Once all the files are copied over to the target instance data folder and synchronization is ready to commit its work on the target instance, SSAS issues an explicit ROLLBACK TRANSACTION command on the source instance to release locks and resume processing.

The Synchronize command generates an in-memory list of all database files on both the source and target instances (presuming, of course, that the target instance already has the database you're about to sync). Synchronization next compares the metadata on two instances and comes up with the list of files that have a different version number, name, or size on the two instances. The files that are different are copied to a temporary folder on the target instance. The temporary folder uses a GUID as the name. Once all new files are copied, the existing files (identical on the source and target) are copied from the target instance's existing database folder to the temporary data folder. Once the GUID folder has all the files, SSAS deletes the existing database folder and renames the GUID folder to have the same name as the database. Synchronization also increments the data folder version number.

Much like the restore function, synchronization keeps the existing database online while new files are being copied. Therefore, if you're copying the large majority of database files, you will need free space to support two copies of the same database at the same time.

As you already learned, synchronization compares file version numbers, in addition to file sizes and creation dates. If you want to avoid having to transfer all files, it is best not to process any items on the target instance because doing so will change the file version numbers even if the file contents do not change.

Due to its incremental design, synchronization can be considerably faster than backup and restore. On the other hand, synchronization failure (perhaps due to a network outage) can often lead to target database corruption. If the target database is corrupted, we must either restore it from a backup or drop the corrupted version and run a full synchronization from another instance. Yet another disadvantage of synchronization is that it does not create a copy of the database as the backup command does. If the source database has incorrect data (or even corruption issue), synchronization quietly transfers the problem to the target instance.

Detaching and attaching databases

You are now familiar with backup and restore as well as synchronization methods for scaling out SSAS solutions. However, as you learned in the previous section, synchronization is single threaded and must transfer only one file at a time. Fortunately, you can use the Detach and Attach features along with the Robocopy command for copying files to ensure the faster delivery of files from the source to target instance.

When you detach a database, SSAS leaves the data folder in its current location and creates a detach logfile. You can copy the entire data folder to another host or another instance's data folder and reattach the database. Keep in mind, however, that unlike synchronization, the Detach and Attach methods do not copy the data for you; you must come up with your own routine (script) for copying data. Additionally, you can attach a previously detached database in the read/write mode to only one instance. For all other instances, you must attach the database in the read-only mode. For most environments, this is acceptable because large databases rarely use write-back, and data must be processed on only one instance.

The Detach and Attach operations simply update the server metadata; they don't have to copy files or change security. Hence, both are very fast.

How to do it...

To detach a database, perform the following steps:

  1. Connect to the SSAS database of interest using SSMS, right-click on the database, and choose Detach. This activates the Detach Database dialog.
  2. You can specify the password that will be used for encrypting the detached database. As with the backup command, document the password in a secure location if you intend to encrypt the database. You can script the Detach command—it will look similar to the following code snippet:

    <Detach xmlns = "http://schemas.microsoft.com/
      analysisservices/2003/engine">   <Object>     <DatabaseID>ssas_cookbook_chapter3</DatabaseID>   </Object> </Detach>

  3. After you run the command, you will find a file named ssas_cookbook_chapter3.detach_log in the Database folder.

To attach a previously detached database, perform the following steps:

  1. Connect to the target SSAS instance (or the same instance from which you previously detached the database) and choose Attach.
  2. If you encrypted the database prior to detaching, you must provide the password.
  3. Specify the full path to the folder with the detached database. You have an option to attach the database from its current location (without copying any files) or from a different location after you copy the database files to that location.
  4. If you script the Attach command, it will look similar to the following code snippet:

    <Attach xmlns = "http://schemas.microsoft.com/
      analysisservices/2003/engine">   <Folder>E:\Program Files\Microsoft SQL Server\
        MSAS11.SQL2012\OLAP\Data\ssas_cookbook_chapter3.0.db
          </Folder>   <ReadWriteMode xmlns = "http://schemas.microsoft.com/
        analysisservices/2008/engine/100">ReadOnly
          </ReadWriteMode> </Attach>

  5. If you prefer to attach in the read-write mode, simply replace Readonly with ReadWrite within the command previously shown.

How it works...

As you learned, the Detach and Attach operations are nearly instantaneous because they only modify the SSAS instance metadata and do not have to copy any files. In order to make the same files available on different hosts, you must copy the files yourself, either manually or through code. Robocopy (Robust File Copy) is a command-line tool, which you could use to copy files. It is available as part of the Windows Resource Kit. Robocopy is multithreaded, out of the box on Windows 2008. If you use prior versions of the Windows operating system, you cannot take advantage of multithreading but you can be creative and launch multiple instances of Robocopy in parallel, each copying one set of data. One approach is to copy all the files with a certain extension in the same Robocopy session; this approach is not particularly effective because it copies dimension files very quickly but larger partition files still have to wait. A better approach is to launch a separate Robocopy session for each partition. Reference the Robocopy documentation for necessary switches and syntax examples.

The Detach/Robocopy/Attach method could be the fastest way of ensuring that multiple SSAS instances have the same data. However, unlike synchronization, this method does not provide an automated way of copying files. Additionally, this method does not make a copy of the database (unlike the backup command); hence, it could quietly transfer incorrect data or corruption issues from source to target. Remember too that we can only have one copy of the database attached in the read-write mode—all other copies must be read-only. Once a database is in the read-only mode, you cannot process any objects or write data back to any object. Furthermore, you can no longer drop the database. If you need to drop the read-only database, you must detach it first and then manually delete its files from the data folder.

Each of the scale-out methods requires a third-party solution for balancing the querying load across multiple SSAS instances. The common implementation of load balancing distributes queries using the Round-robin approach; however, this approach could lead to one instance being heavily loaded, while others are virtually idle due to the lightweight queries. A better approach is to check CPU and/or memory usage on each host and send each query to the host with least resource utilization. Please refer to the SQLCAT team blog for more information regarding query load balancing and scaling out SSAS solutions at http://www.sqlcat.com.

Summary:

This article starts off by providing an overview of Analysis Services con fi guration options. Next, you learn how to create and drop databases, monitor and troubleshoot Analysis Services instances, and check the size of each object. Additionally, you will learn various methods for scaling out your multidimensional business intelligence solutions using backup and restore, synchronization, and the detach and attach options.

Resources for Article:


Further resources on this subject:


About the Author :


Baya Dewald

Baya Dewald has been working with Microsoft SQL Server for over 15 years and with Analysis Services since its debut in 1999. He has architected, developed, and administered databases with millions of users and cubes measured in terabytes. Throughout his career, he has held various certifications, including Microsoft Certified Solution Developer, Microsoft Certified Systems Engineer, and Microsoft Certified Database Administrator. He has managed DBA teams and published over 100 technical articles in SQL Server Magazine, SQL Professional, Tech Target, and InformIT. He has also contributed to several technical books as a co-author and technical reviewer. He can be reached at baya.dewald@gmail.com.

Paul Turley

Paul Turley is a mentor with SolidQ and is a Microsoft SQL Server MVP. He consults, writes, speaks, teaches, and blogs about business intelligence and reporting solutions. He works with companies around the world to visualize and deliver critical information to make informed business decisions. He is the director of the Oregon SQL PASS chapter and user group, and the lead author of Professional SQL Server 2012 Reporting Services and 11 other books by Wrox and Microsoft Press. Paul blogs at SqlServerBiBlog.com.

Steve Hughes

Steve Hughes is a Practice Lead at Magenic. In his current role, he develops strategy and helps guide data, business intelligence, collaboration, and data integration development using Microsoft technologies, including SQL Server, SharePoint, and BizTalk. He continues to deliver data and business intelligence solutions using these platforms. He has been working with technology for over 15 years with much of that time spent on creating business intelligence solutions. He is passionate about using data effectively and helping customers understand that data is valuable and profitable. He also has experience working with a large software company as a service vendor and there received awards for customer satisfaction and engineering excellence.

In 2010, he received the Consultant of the Year honor at Magenic. He has delivered multiple presentations on SQL server, data architecture, and business intelligence over the years. Steve can often be found at Professional Association for SQL Server (PASS) events where he serves as a regional mentor and is active with the Minnesota SQL Server User Group (MNPASS). He shares his insights from the field on his blog at http://dataonwheels.wordpress.com.

Books From Packt


Getting Started with SQL Server 2012 Cube Development
Getting Started with SQL Server 2012 Cube Development

Instant Microsoft SQL Server Analysis Services 2012 Dimensions and Cube
Instant Microsoft SQL Server Analysis Services 2012 Dimensions and Cube

Instant SQL Server Analysis Services 2012 Cube Security
Instant SQL Server Analysis Services 2012 Cube Security

Expert Cube Development with Microsoft SQL Server 2008 Analysis Services
Expert Cube Development with Microsoft SQL Server 2008 Analysis Services

Instant MDX Queries for SQL Server 2012
Instant MDX Queries for SQL Server 2012

MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook
MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook

Beginners Guide to SQL Server Integration Services Using Visual Studio 2005
Beginners Guide to SQL Server Integration Services Using Visual Studio 2005

SQL Server 2012 Reporting Services Blueprints
SQL Server 2012 Reporting Services Blueprints


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
x
U
r
m
3
S
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software