I am new to SSAS and I'm facing a confusing problem.
I have a regular process for updating dimensions (with a ProcessUpdate).
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"> <Object> <DatabaseID>Central</DatabaseID> <DimensionID>Prestatarios</DimensionID> </Object> <Type>ProcessUpdate</Type> <WriteBackTableCreation>UseExisting</WriteBackTableCreation> </Process>
It has been working fine but in the last run I got the following error:
<root xmlns="urn:schemas-microsoft-com:xml-analysis:empty">
<Exception xmlns="urn:schemas-microsoft-com:xml-analysis:exception" />
<Messages xmlns="urn:schemas-microsoft-com:xml-analysis:exception">
<Error ErrorCode="3238002695" Description="Internal error: The operation terminated unsuccessfully." Source="Microsoft Analysis Services" HelpFile="" />
<Error ErrorCode="3240034307" Description="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 'Sub Grupo'. Table: 'dbo_Prestatarios', Column: 'SubGrupo', Value: 'A00377'. Source attribute: 'Prestatario'. Key column value(s) of the source attribute: '7384538'." Source="Microsoft Analysis Services" HelpFile="" />
<Error ErrorCode="3240034317" Description="Errors in the OLAP storage engine: An error occurred while the 'Prestatario' attribute of the 'Prestatarios' dimension from the 'Central' database was being processed." Source="Microsoft Analysis Services" HelpFile="" />
<Error ErrorCode="3239837702" Description="Server: The current operation was cancelled because another operation in the transaction failed." Source="Microsoft Analysis Services" HelpFile="" />
</Messages>
I googled this and the cause seems to be that in the source data some of the of the attributes has been changed. However, I reviewed it and the offending record has not been updated at all:
Source data before T-SQL processing:
Key | Grupo | GrupoCubo | Correlativo | IDCubo |
---|---|---|---|---|
7384538 | ARIV | A00377 | 2971 | A003772971 |
Source data after T-SQL processing:
Key | Grupo | GrupoCubo | Correlativo | IDCubo |
---|---|---|---|---|
7384538 | ARIV | A00377 | 2971 | A003772971 |
So I'm not sure why is failling. I restored backups, reprocessed, and get the same results.
I would apprreciate very much any suggestion or advice.
Thanks for reading
If anyone has a similar problem, this is how I solved.
I restored the last known "good" backup for both the transactional and OLAP databases related to the process, and reran all the processes in sequence during all the periods needed -- in this case there were only 2 periods to reprocess.
All the processes reexecuted this way ran smoothly and the error didn't appear again.
I'm assuming that the first time there were an execution error at some point (not sure exactly when) that corrupted the OLAP database and we couldn't fix it. Any attempt we did for that (reprocess dimensions / partitions, etc) only generated more errors.
This incident illustrate the importance of taking backups at key points in time as a part of the processes. Fortunately, we have that policy so we had the proper backups to recover.
Thanks for reading.