ProcessFull fails where continuous ProcessUpdate's worked

In some of my past projects I experienced that, even though a daily ProcessUpdate works fine, a weekly scheduled ProcessFull may fail. So I decided to test this behavior on a small dataset to see what is really going on and how a ProcessUpdate is different from a ProcessFull.

Here are my results:

Starting Basis:

System:                  SSAS 2008 SP1 (Build 10.0.2531)
OS:                        Windows 7

Relational Table	Dimension Structure
Attribute Relationships	Browser

               

 

Initial Process:

ProcessFull	Works as expected
ProcessUpdate (after Process Full, nothing changed)	Works as expected
ProcessAdd	Not possible / first load

 

Added new valid row to table:

INSERT INTO dbo.Test_DimProduct VALUES ('P7', 'A2', 'A')

 

 

ProcessFull	Works as expected
ProcessUpdate	Works as expected

 

Update the new row in SQL and change the MainGroup from A to B which is invalid due to the Attribute Relationships:

UPDATE dbo.Test_DimProduct SET MainGroup='B' WHERE Product='P7'

 

ProcessFull	Throws an Error because SubGroup A2 belongs to two MainGroups (A and B) – expected behavior
ProcessUpdate	Does NOT throw any error. SubGroup A2 is moved to the MainGroup of the new records (MainGroup B)

 

ProcessFull detects that SubGroup A2 belongs to more than one MainGroup what is not valid due to the defined Attribute Relationships. So an error is thrown.

ProcessUpdated does not detect that A2 now belongs to more than one MainGroup. Instead SubGroup A2 and also all its descendants are moved to the new MainGroup B. This does not represent the data that is in the underlying table.

Added new invalid row to table:

INSERT INTO dbo.Test_DimProduct VALUES ('P8', 'A2', 'A')

 

ProcessFull	Throws an Error because SubGroup A2 belongs to two MainGroups (A and B) – expected behavior, Same as before
ProcessUpdate	Does NOT throw any error. SubGroup A2 is NOT moved to the MainGroup of the new records (MainGroup A) Instead the MainGroup of the new record is ignored and it gets add below the current SubGroup where the record belongs to.

 

ProcessFull detects that SubGroup A2 belongs to more than one MainGroup what is not valid due to the defined Attribute Relationships. So an error is thrown.

ProcessUpdated still does not detect that A2 belongs to more than one MainGroup. The new record is added to the dimension and the new Product appears below the SubGroup it belongs to. On the other hand, the SubGroup does not appear below the MainGroup where it belongs to regarding the newest record – this is a different behavior as before where an existing row was updated. This does not represent the data that is in the underlying table.

Delete the invalid row where SubGroup A2 was below MainGroup B

DELETE FROM dbo.Test_DimProduct WHERE Product = 'P7'

 

ProcessFull	Works as expected All Attribute Relaitonships are correct again
ProcessUpdate	Works The Product P7 gets removed This also has effects on the SubGroup A2 which is moved back to A

 

ProcessFull works fine because all Attribute Relationships are correct again

ProcessUpdate removes Product P7 correctly and SubGroup A2 is moved back to the correct MainGroup A again.

Conclusion:

ProcessFull always reflects what is really in the underlying table.
ProcessUpdate, under specific circumstances, can lead to a dimension that does not reflect what is in the relational source table. This can further lead to wrong data in the cube. This is can be very fatal if a node and all its descendants get moved to a new parent only because of one new record.

From my expectations, SSAS should throw an error in such cases and ProcessFull and ProcessUpdate should handle those scenarios similar.

---

Gerhard is a BI consultant working with Microsoft tools since June 2006. He is specialized in Analysis Services (SSAS) and Reporting Services (SSRS) but is also familiar with the other components of Microsoft SQL Server. Currently he is working at pmOne, Austria (www.pmOne.com).

---