November 2008 - Posts - Sparks from the Anvil

November 2008 - Posts

SSAS writeback error was ‘an accident with a contraceptive and a time machine’

Those of you that heard the hit radio series 'The Hitchhikers Guide to the Galaxy' or read Douglas Adams' book will recognise the title of my blog entry as been the reason quoted by Zaphod when asked to explain why he was Zaphod Beeblebrox the First and his father was Zaphod Beeblebrox the Third. Apparently it was all down to 'an accident with a contraceptive and a time machine' whereby Zaphod Beeblebrox had travelled back in time and fathered his own ancestor.

In my case the 'time machine' was a source code control system that provided the ability to go 'back in time' and revert my SSAS project to a previous incarnation. My 'accident' was to combine some of the 'past' with the 'present' to end up with a creation of which Frankenstein would have been proud.

The key problem of using a source code control system with an Analysis Services project is that the file-based source code control model just does not fit in with the way SSAS works. For example, adding a security role to a cube not only creates a new role file but also fundamentally alters the content of both the .cube file and all the dimension files for which you define attribute security. So when you come to check-in the project, you find that two or three files have changed when you only expected to alter one! So when it comes to reverting to a previous version you really have to take all the files from a specific date, not just one or two files.

The horror of my own Frankenstein creation did not get recognised until several days after its birth. When testing part of the application's functionality that used cube writeback we got the obscure message

Errors in the high-level relational engine.
Only one-to-many relationships are supported for parent-child dimensions.
The '' table contains many-to-many relationships between parent and child columns.

As you see, the table name was missing from the error message, so we were bemused as to which table was at fault. If the rest of the error message were to be believed, the data was incorrect.  Well we checked the data and it was fine. Moreover the cube did not complain when processing the data.  We deleted and recreated the writeback tables, so they are not at fault. When we disabled writeback, the error message disappeared.  

Well, there is nothing more frustrating or time-consuming than a misleading error message and a bunch of red-herrings. We wasted many, many hours trying to figure out the cause of the error. A search the forums revealed nothing so I posted my own entry – to which no one replied. Eventually the issue was raised with Microsoft. After much teeth gnashing, they found the source of the problem: lack of a primary key on a table in the DSV. Well the primary key was certainly there at some point as the writeback had worked in the past. However, because I had used source control to 'travel back in time' and revert some files to an earlier version, I had clearly got my revisions well and truly mixed up. My 'contraceptive' had not worked and Frankenstein's monster was duly born.

So beware all of you who play with 'contraceptives' and 'time-machines'!

Posted by DrJohn with 1 comment(s)
Filed under: