Processing a multidimensional model (Analysis Services)
Applies to: SQL Server Analysis Services Azure Analysis Services Power BI Premium
Processing is the step, or series of steps, in which Analysis Services loads data from a relational data source into a multidimensional model. For objects that use MOLAP storage, data is saved on disk in the database file folder. For ROLAP storage, processing occurs on demand, in response to an MDX query on an object. For objects that use ROLAP storage, processing refers to updating the cache before returning query results.
By default, processing occurs when you deploy a solution to the server. You can also process all or part of a solution, either ad hoc using tools such as Management Studio or SQL Server Data Tools, or on a schedule using Integration Services and SQL Server Agent. When making a structural change to the model, such as removing a dimension or changing its compatibility level, you will need to process again to synchronize the physical and logical aspects of the model.
This topic includes the following sections:
Processing requires administrative permissions on the Analysis Services instance. If you are processing interactively from SQL Server Data Tools or Management Studio, you must be a member of the server administrator role on the SQL Server Analysis Services instance. For processing that runs unattended, for example using an SSIS package that you schedule through SQL Server Agent, the account used to run the package must be a member of the server administrator role. For more information about setting administrator permissions, see Grant server admin rights to an Analysis Services instance.
The account used to retrieve data is specified in the data source object, either as an impersonation option if you are using Windows authentication, or as the user name on the connection string if using database authentication. The account must have read permissions on relational data sources used by the model.
The project or solution must be deployed before you can process any objects.
Initially, during the early stages of model development, deployment and processing occur together. However, you can set options to process the model later, after you deploy the solution. For more information about deployment, see Deploy Analysis Services Projects (SSDT).
Choosing a tool or approach
You can process objects interactively using a client application such as SQL Server Data Tools or Management Studio, or a scripted operation that run as a SQL Server Agent job or SSIS package.
How you process a database varies considerably depending on whether the model is in active development or in production. Once a model is deployed to a production server, processing must be tightly controlled to ensure the integrity and availability of multidimensional data. Because objects are interdependent, processing typically has a cascading effect across the model as other objects are also processed or unprocessed in tandem. If some objects are left in an unprocessed state, queries for that data will not resolve, breaking any reports or applications that use it. When developing a strategy for processing a production database, consider using script or SSIS packages that you have debugged and tested to avoid operator error or overlooked steps.
For more information, see Tools and Approaches for Processing (Analysis Services).
Processing affects the following SQL Server Analysis Services objects: measure groups, partitions, dimensions, cubes, mining models, mining structures, and databases. When an object contains one or more objects, processing the highest-level object causes a cascade of processing all the lower-level objects. For example, a cube typically contains one or more measure groups (each of which contains one or more partitions) and dimensions. Processing a cube causes processing of all the measure groups within the cube and the constituent dimensions that are currently in an unprocessed state. For more information about processing SQL Server Analysis Services objects, see Processing Analysis Services Objects.
While the processing job is working, the affected SQL Server Analysis Services objects can be accessed for querying. The processing job works inside a transaction and the transaction can be committed or rolled back. If the processing job fails, the transaction is rolled back. If the processing job succeeds, an exclusive lock is put on the object when changes are being committed, which means the object is temporarily unavailable for query or processing. During the commit phase of the transaction, queries can still be sent to the object, but they will be queued until the commit is completed.
During a processing job, whether an object is processed, and how it will be processed, depends on the processing option that is set for that object. For more information about the specific processing options that can be applied to each object, see Processing Options and Settings (Analysis Services).
Cubes that contain unprocessed elements have to be reprocessed before they can be browsed. Cubes in SQL Server Analysis Services contain measure groups and partitions that must be processed before the cube can be queried. Processing a cube causes SQL Server Analysis Services to process constituent dimensions of the cube if those dimensions are in an unprocessed state. After an object has been processed the first time, it must be reprocessed either partially or in full whenever one of the following situations occurs:
The structure of the object changes, such as dropping a column in a fact table.
The aggregation design for the object changes.
The data in the object needs to be updated.
When you process objects in SQL Server Analysis Services, you can select a processing option, or you can enable SQL Server Analysis Services to determine the appropriate type of processing. The processing methods made available differ from one object to another, and are based on the type of object. Additionally, the methods available are based on what changes have occurred to the object since it was last processed. If you enable SQL Server Analysis Services to automatically select a processing method, it will use the method that returns the object to a fully processed state in the least time. For more information, see Processing Options and Settings (Analysis Services).