MSDN Community

Hi everyone,

I'm encountering a persistent error when trying to refresh a specific partition in my SQL Server Analysis Services (SSAS) project (2019 version).

The error message is as follows:

Errors in the OLAP storage engine: The operation on the 'SalesOrderDetail' measure group, partition 'SalesOrderDetail_2023' failed. Errors in the OLAP storage engine: The process of the 'SalesOrderDetail' measure group, partition 'SalesOrderDetail_2023' failed. Errors in the OLAP storage engine: An internal error occurred. Errors in the OLAP storage engine: The value cannot be null. Parameter name: value

I've tried the following troubleshooting steps:

• Verifying the connection string to the source data.
• Checking the integrity of the source SQL tables.
• Rebuilding the entire SSAS database.
• Clearing the cache for the affected measure group.
• Ensuring all source columns used in calculations are not nullable or have proper handling.

The problem seems to be specific to this partition. Other partitions in the same measure group and other measure groups in the cube refresh without any issues.

Has anyone experienced a similar issue or can offer guidance on what might be causing this "value cannot be null" error within the storage engine for a specific partition?

Hi Jane,

This "value cannot be null" error within the OLAP storage engine can be tricky. Often, it points to an issue with how data is being processed at a granular level, even if the overall table integrity looks fine.

Could you check the following within the specific partition's processing settings:

1. Attribute relationships: Ensure there are no broken or circular relationships involving the dimensions linked to the 'SalesOrderDetail' measure group.
2. Calculations/MDX expressions: If there are any specific MDX calculations or formulas directly applied to this measure group or its measures, temporarily disable them to see if the error persists. A null value within a complex calculation can cause this.
3. Source data types: While you checked the SQL tables, sometimes SSAS interprets data types differently. For instance, a string field that sometimes contains only spaces or special characters might be problematic if not handled.
4. Partition key column: If you are using a partition key, ensure its values are valid and not null for the rows being processed in that partition.

I'd also recommend using the XMLA (XML for Analysis) window in SQL Server Management Studio (SSMS) to process the specific partition and look for more detailed error messages that might not surface in the standard SSAS project processing output.

Thanks, Robert. I will investigate those areas.

Regarding attribute relationships, they appear to be correctly defined and I don't see any obvious circular dependencies.

For calculations, the measures in this group are relatively simple aggregations (SUM, COUNT). There are no complex MDX scripts directly on the measures themselves.

I'll pay closer attention to data types and the partition key. The partition is based on a date range, so the key is a date column. I'll specifically check for any rogue nulls or invalid date formats in that column for the current partition's data.

Processing via XMLA is a good suggestion. I'll try that and see if it reveals more granular details.

Jane, I had a similar "value cannot be null" error once. It turned out to be a very obscure issue with a specific value in a dimension attribute that was being mapped to a measure. It wasn't a direct null in the source table, but SSAS was trying to perform an operation with a calculated value that resulted in null for a single record.

My fix involved:

1. Identifying the specific record(s) causing the issue by processing in small batches or using row-level error logging if your SSAS version supports it.
2. Examining the data for those specific records across all related tables and dimensions.
3. Often, it requires adding a default value or a conditional check in the SSAS model (e.g., using `IIF` or `COALESCE` in a calculated member or attribute definition) to handle these edge cases gracefully.

Good luck!

Update: Found the culprit! It was indeed a specific data point.

Following Robert's advice, I processed the partition via XMLA, which gave a slightly more descriptive error pointing to a specific dimension key that was causing a null aggregation. It turned out to be a single row in the source data where a foreign key to a dimension was null, which SSAS couldn't handle when trying to aggregate.

I've updated the ETL process to ensure that this foreign key is not null (by setting a default or logging/rejecting the record), and the partition refresh now works flawlessly.

Thank you all for your valuable input and suggestions!