Archive

Posts Tagged ‘SSAS – Attribute Relations’

SSAS – Attribute rigid relationships with process update

August 26, 2011 3 comments

Attribute Relations are one of the most and interesting concept while developing SSAS cubes. The processing of dimensions using SSIS task has significant considerations while choosing rigid relationship.
 
Scenario: Processing of a dimension having rigid relationships using SSIS task.
 
Step 1: I have reverse engineered the existing SSAS cube “AdventureWorks2008R2” from my local database to get the solution file to see the existing relations.
Open the Date Dimension to get the attribute relation information as shown
 

Date Dimension

Date Dimension


 
On Attribute Relations –> The attribute relations are set to rigid relations (the bold arrow mark shown)
 
Rigid Relationships

Rigid Relationships


 
Step 2: Developed SSIS package to process the Date Dimension as
 
Dimension Processing Task

Dimension Processing Task


Processing Task Settings

Processing Task Settings


 
Step 3: Execute the SSIS package without any changes to existing data in the source data table.
The package executed successfully as below.
 
Execution of task

Execution of task


 
Step 4: Browsing the Date Dimension using SSMS as
 
Dimension browsing

Dimension browsing


 
Step 5: Deleted one record [DateKey = 20121202] from the source table as

-- Step 4: Delete the record with DateKey = '20101202'
DELETE  FROM [AdventureWorksDW2008R2].[dbo].[DimDate]
WHERE   [DateKey] = '20101202'

Deleting source record

Deleting source record


 
Step 6: Processing the SSIS process update task, this process needs to update the target dimension with removing record in SSAS engine similar to deleted source record.
Process the SSIS task as
 
Process update failure

Process update failure


 
Error Message:
 
Error on processing with Deleted source DateKey = ‘20101202’
 
SSIS package “SSASDimProcess.dtsx” starting.
Error: 0xC1000007 at ASP DimDate Process Update, Analysis Services Execute DDL Task: Internal error: The operation terminated unsuccessfully.
Error: 0xC11F0003 at ASP DimDate Process Update, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: Rigid relationships between attributes cannot be changed during incremental processing of a dimension. The error occurred when processing attribute ‘Day of Week’. . Source attribute: ‘Date’. Key column value(s) of the source attribute: ‘20101202’.Error: 0xC11F000D at ASP DimDate Process Update, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while the ‘Date’ attribute of the ‘Date’ dimension from the ‘Adventure Works DW 2008R2’ database was being processed.
Error: 0xC11F0006 at ASP DimDate Process Update, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
Error: 0xC11C0002 at ASP DimDate Process Update, Analysis Services Execute DDL Task: Server: The operation has been cancelled.
Task failed: ASP DimDate Process Update
SSIS package “SSASDimProcess.dtsx” finished: Success.
 
Step 7: Changing the attribute relationship to flexible (the transparent arrow mark shown) as
Right Click on relation arrow –> RelationshipType –> Flexible
 
Flexible relationships

Flexible relationships


 
On processing the SSIS task, the dimension has got success as
 
Execution of task

Execution of task


 
Points to consider:
 
1. Attribute relations relates to the processing of dimensions
2. Rigid Relationship dimensions will not accept any changes to source data
3. If we use rigid relationship consider either process only once with no further changes to data or full process every time.

Advertisements
Categories: SSAS Tags: