Tools and Approaches for Processing (Analysis Services)
Applies To: SQL Server 2016
Processing is an operation in which Analysis Services queries a relational data source and populates Analysis Services objects using that data.
As an Analysis Services system administrator, you can execute and monitor the processing of Analysis Services objects using these approaches:
Run Impact Analysis to understand object dependencies and scope of operations
Process individual objects in SQL Server Management Studio
Process individual or multiple objects in SQL Server Data Tools (SSDT)
Run Impact Analysis to review a list of related objects that will be unprocessed as result of the current action.
Generate and run a script in an Analysis Services XMLA Query window in Management Studio to process individual or multiple objects
Use Analysis Services PowerShell cmdlets
Use control flows and tasks in SSIS packages
Monitor processing with SQL Server Profiler
Program a custom solution using AMO. For more information, see Programming AMO OLAP Basic Objects.
Processing is a highly configurable operation, controlled by a set of processing options that determine whether full or incremental processing occurs at the object level. For more information about processing options and objects, see Processing Options and Settings (Analysis Services) and Processing Analysis Services Objects.
Start Management Studio and connect to Analysis Services.
Right-click the Analysis Services object you want to process and then click Process. You can process data at any of these levels:
Measure Groups or individual partitions in the measure group
Analysis Services objects are hierarchical. If you choose database, processing can occur for all of the objects contained in the database. Whether processing actually occurs will vary depending on the process option you select and the state of the object. Specifically, if an object is unprocessed, processing its parent will result in that object getting processed. For more information about object dependencies, see Processing Analysis Services Objects.
In the Process dialog box, in Process Options, use the default value provided or select a different option from the list. For more information about each option, see Processing Options and Settings (Analysis Services).
Click Impact Analysis to identify and optionally process dependent objects that are affected if the objects listed in the Process dialog box are processed.
Optionally, click Change Settings to modify the processing order, processing behavior relative to specific types of errors, and other settings.
The Process Progress dialog box provides ongoing status for each command. If a status message is truncated, you can click View Details to read the entire message.
Start SQL Server Data Tools (SSDT) and open a project that has been deployed.
In Solution Explorer, under the deployed project, expand the Dimensions folder.
Right-click a dimension, and then click Process. You can right-click multiple dimensions to process multiple objects at once. For more information, see Batch Processing (Analysis Services).
In the Process Dimension dialog box, in the Process Options column under Object list, verify that the option for this column is Process Full. If it is not, under Process Options, click the option, and select Process Full from the drop-down list.
When processing is finished, click Close.
Before you process an Analysis Services object in either SQL Server Data Tools or Management Studio, you can analyze the effect on related objects by clicking Impact Analysis in one of the Process Objects dialog boxes.
Right-click a dimension, cube, measure group, or partition to open a Process Objects dialog box.
Click Impact Analysis. Analysis Services scans the model and reports on reprocessing requirements for objects that are related to the one you selected for processing.
Start Management Studio and connect to Analysis Services.
Right-click the object to be processed and then click Process.
In the Process dialog box, select the process option you want to use. Modify any other settings. Run Impact Analysis to identify any changes you might need to make.
Click Script on the Process Objects screen.
This generates an XMLA script and opens an Analysis Services XMLA Query window.
Close the dialog box. The script contains the processing command and options that were specified in the dialog box.
Optionally, you can continue adding to the script if you want to process additional objects in the same batch. To continue, repeat the previous steps, appending the generated script so that you have a single script for all processing operations. To view an example, see Schedule SSAS Administrative Tasks with SQL Server Agent.
From the menu bar, click Query, and then click Execute.
Starting in this release of SQL Server, you can use Analysis Services PowerShell cmdlets to process objects. The following cmdlets can be run interactively or in script:
Connect to an Analysis Services instance in SQL Server Profiler.
In Events Selection, click Show all events to add all events to the list.
Choose the following events:
Command Begin and Command End to show when processing starts and stops
Error to capture any errors
Progress Report Begin, Progress Report Current, and Progress Report End to report on process status and show the SQL queries used to retrieve the data
Execute MDX Script Begin and Execute MDX Script End to show the cube calculations
Optionally, add lock events if you are diagnosing performance problems related to processing
In Integration Services, create a package that uses the Analysis Services Processing Task to automatically populate objects with new data when you make regular updates to your source relational database.
In the SSIS Toolbox, double-click Analysis Services Processing to add it to the package.
Edit the task to specify a connection to the database, which objects to process, and the process option. For more information about how to implement this task, see Analysis Services Processing Task.