New Information - SQL Server 2000 SP3.
When you process a cube, the aggregations designed for the cube are calculated and the cube is loaded with the calculated aggregations and data. Processing a cube involves reading the dimension tables to populate the levels with members from the actual data, reading the fact table, calculating specified aggregations, and storing the results in the cube. After a cube has been processed, users can query it.
Caution Referential integrity of the data warehouse is not verified by Microsoft® SQL Server™ 2000 Analysis Services during processing. For example, if the cube's fact table contains foreign key values that are not present in a joined dimension table's primary key column, the rows that contain those values are not processed. In this case, processing does not produce an error message, but the cube contains incomplete and, therefore, inaccurate data.
Before attempting to browse the cube, you must process the cube if you perform any of the following tasks:
- Building the cube and designing its storage options and aggregations.
- Changing the cube's structure (measures, dimensions, and so on) and saving the changes to the cube.
- Changing the structure of a shared dimension used in the cube.
Also, if data in the cube's data warehouse has been added or changed, processing is recommended in order to ensure accurate results when browsing the cube.
Note Newly processed cubes are visible to end users only after they reconnect to the server computer.
If cubes or dimensions are based on SQL Server data sources that use Windows Authentication, the logon account for the MSSQLServerOLAPService service must have permissions to access the data sources. Otherwise, Analysis Services may fail to process these cubes or dimensions.
Cube Processing Options
Each of the following three processing options is appropriate in different circumstances:
- Full Process
- Incremental update
- Refresh data
In addition to these three mutually exclusive options, you can select a fourth option, Incrementally update the dimensions of this cube, in conjunction with any of these options. This option allows you to incrementally update the cube's dimensions as part of the cube processing.
These options are available in the Process a Cube dialog box, which is displayed when you right-click a cube in the Analysis Manager tree pane and then click Process.
To process a cube
Depending on the size of your cube, processing may take considerable time. After processing completes but before you close the Process dialog box, you can view the SQL statement used to process the cube.
To view the SQL statement used to process a cube
Also, the storage required for temporary files during processing can be substantially larger than the final size of the cube. If during processing you exhaust the free space of the disk containing your temporary file folder, you can specify a folder on another disk with more free space.
To change the temporary file folder used by Analysis Services
Completely Processing a Cube
Full Process is the processing option used to perform a complete load of the cube. All dimension and fact table data is read and all specified aggregations are calculated. You must process a cube with the Full Process option when its structure is new or when the cube, its dimensions, or its measures have undergone structural changes. In addition, virtual and linked cubes also require complete processing after you build them, change their structure, or change one of their shared dimensions. To process a virtual cube or a linked cube, use the Process dialog box.
To process a virtual cube
To process a linked cube
Potential exceptions are cubes in which only changing dimensions have been changed. A changing dimension does not always require its parent cubes to be processed with the Full Process option after the structure of the dimension is changed. However, processing with an alternative option may be required. Changing dimensions include virtual, parent-child, and relational OLAP (ROLAP) dimensions. For more information, see Changing Dimensions.
Processing a cube with the Full Process option can take a substantial amount of time if there is a large fact table and there are many dimensions with many levels and many items in each level. You do not need to load of dimension information if you use only preprocessed shared dimensions in cubes.
If there are changes in the data warehouse schema that affect the structure of cubes, you must change the structure of those cubes and then process them with the Full Process option. If there are changes in or additions to data in the data warehouse, you do not need to completely process cubes. Such changes can be incorporated into existing cubes using the Incremental update or Refresh data processing options, depending on how the data changed.
The Full Process option can be used while users continue to query a previously processed cube; however, after processing has completed, users need to disconnect and reconnect to reestablish access to the cube.
Caution If a shared dimension's structure is updated and saved but not processed, it will be processed automatically when any cube that incorporates the dimension is processed using the Full Process option. At that time any other cubes that incorporate the dimension immediately become unavailable to users and must be processed before they can be used again.
Incrementally Updating a Cube
An incremental update is appropriate when new data is to be added to a cube, but existing data has not changed and the cube structure remains the same. The Incremental update option adds new data and updates aggregations.
An incremental update does not affect the existing data that has already been processed. It usually requires significantly less time than processing with the Full Process option. An incremental update can be performed while users continue to query the cube; after the update is complete, users have access to the additional data without having to disconnect and reconnect.
Because an incremental update creates a temporary partition from the new data and merges it into an existing partition, it is necessary to understand the special considerations that apply to partitions before performing an incremental update.
Refreshing a Cube's Data
The Refresh data option causes a cube's data to be cleared and reloaded and its aggregations recalculated. This option is appropriate when the underlying data in the data warehouse has changed but the cube's structure remains the same.
The Refresh data option can be performed while users continue to query the cube; after the refresh has completed, users have access to the updated data without having to disconnect and reconnect.
Incrementally Updating Dimensions
The Incrementally update the dimensions of this cube option causes the cube's dimensions to be incrementally updated when the cube is processed. This option is valid with the Full Process, Incremental update, or Refresh data option. The Incrementally update the dimensions of this cube option is appropriate when rows have been added to any of the cube's dimension tables since the cube or dimension was last processed.
Cube Properties for Processing
Some properties of a cube are used to control its processing. You can set these properties in the properties pane of Cube Editor or in the Cube Processing Settings dialog box, which is displayed when you click Settings in the Process a Cube dialog box. The following table describes these properties.
|Property name in Cube Editor||Option in Cube Processing Settings dialog box||Description|
|Processing Optimization Mode||Processing Optimization Mode||If you select Regular in Cube Editor or After all aggregations are calculated in the Cube Processing Settings dialog box, new cube data is not available until processing completes. These values are the defaults. If you select Lazy Aggregations in Cube Editor or Immediately after data is loaded in the Cube Processing Settings dialog box, new cube data is available before processing completes; however, because the optimizations are not complete when the new data becomes available, query performance is reduced until the optimizations complete.|
|Stop Processing on Key Errors||Stop processing after encountering missing dimension key errors or Ignore all missing dimension key errors||If you select Yes in Cube Editor or Stop processing after encountering missing dimension key errors in the Cube Processing Settings dialog box, processing is halted and canceled when the specified limit for the number of dimension key errors is exceeded. (See Key Error Limit.) A dimension key error occurs when a fact table row is encountered that contains a foreign key value not present in the joined primary key column of a dimension table.
If you select No in Cube Editor or Ignore all missing dimension key errors in the Cube Processing Settings dialog box, dimension key errors never halt and cancel cube processing regardless of the number of errors encountered. If one or more dimension key errors are encountered, the cube's data does not reflect the entire fact table.
|Key Error Limit||Processing will stop after||Limit for the number of dimension key errors. Cube processing is halted and canceled when the limit is exceeded. The default is 0. If you select a higher number and processing completes, the cube's data does not reflect the entire fact table. This property is ignored if you select No for the Stop Processing on Key Errors property in Cube Editor, or if you select or Ignore all missing dimension key errors in the Cube Processing Settings dialog box.|
|Key Error Log File||File path and name||Path and file name of the log file for dimension key errors.|