MSDN Documentation

Processing Models in SQL Server Analysis Services Multidimensional Modeling

Processing is the crucial step in SQL Server Analysis Services (SSAS) Multidimensional models where data from relational sources is loaded into the multidimensional structures (cubes, dimensions, partitions). This process involves reading data from the source, transforming it if necessary, and then storing it in an optimized format for querying.

Understanding Processing Types

SSAS offers various processing methods, each suited for different scenarios:

• Full Process: This is the most comprehensive type. It deletes all existing data and metadata (if specified) and then rebuilds the entire object from scratch. Use this when significant structural changes have occurred or for a complete data refresh.
• Process Default: This option analyzes the current state of the object and applies the most appropriate processing method. For example, it might perform a full process on a dimension if its structure has changed, but an incremental update on a fact table.
• Process Recalc: This type recalculates aggregations based on existing data without re-reading the source data. It's useful when only calculations or measures need to be updated.
• Process Add: This method adds new data to existing tables or partitions. It's efficient for incremental updates where only new records need to be incorporated.
• Process Update: This option updates existing data and can also add new data. It's a common choice for regular data refreshes.
• Process Clear: This removes all processed data from the object but retains the metadata. This is useful before performing a new full or incremental load.
• Process Full (Metadata Only): This rebuilds the metadata structure of the object but does not load any data.
• Process Clear (Metadata Only): This removes all metadata but not the processed data.

Processing Strategies

Effective processing relies on understanding your data refresh requirements and the relationships within your model:

• Dimension Processing: Dimensions are typically processed before fact tables. This ensures that when fact data is loaded, it can correctly reference dimension members.
  • Attribute Relationships: The order in which dimension attributes are processed matters, especially with many-to-one or one-to-many relationships. Ensure parent attributes are processed before child attributes.
  • Key Columns: Ensure key columns in dimensions are unique. Duplicate keys can lead to processing errors or unexpected results.
• Cube/Partition Processing:
  • Fact Tables: Fact tables should be processed after all related dimensions have been processed.
  • Partitions: Processing can be done at the partition level, allowing for granular control over data refreshes. This is especially useful for large fact tables where you might only need to reprocess recent data.
  • Aggregations: Aggregations are processed during cube processing. You can choose to process them fully or incrementally.

Processing Methods in SSAS Tools

You can initiate processing through various tools:

• SQL Server Management Studio (SSMS): Right-click on the database, cube, dimension, or partition and select "Process...".
• SQL Server Data Tools (SSDT) / Visual Studio: In the Solution Explorer, right-click on the project or specific object and select "Process...".
• AMO (Analysis Management Objects): Programmatically control processing using the AMO object model.
• XMLA (XML for Analysis): Execute XMLA scripts to initiate processing commands.

Example: Processing a Dimension in SSMS

1. In SSMS, connect to your SSAS instance.
2. Right-click on the dimension you want to process.
3. Select "Process...".
4. In the "Process Dimension" dialog box, choose the "Processing Option" (e.g., "Process Default").
5. Specify the "Relineation option" (e.g., "Process all data").
6. Click "OK".

Troubleshooting Processing Errors

Common issues during processing include:

• Data Type Mismatches: Ensure data types in your source align with SSAS data types.
• Duplicate Keys: Primary keys in dimensions must be unique.
• Referential Integrity Violations: Fact data referencing non-existent dimension keys.
• Source System Errors: Issues with the underlying relational database or data sources.
• Permissions: The SSAS service account needs appropriate read permissions on the data sources.

Review the processing logs in SSMS for detailed error messages.