(FIXED) BOARD 11:  Datareader can destroy your relationship
It was inevitable, all the signs were there. Whilst my wife was planning ways for us to spend time together, I was planning BOARD 10 to 11 migration processes and regression tests. Where she wanted to snuggle up in the evenings, I was finding excuses to sneak off into the study to mess about with BOARD 11. It all exploded when she found out my recent work trip was actually to Italy with...BOARD - 
She gave me a fateful ultimatum, to choose between her and the true object of my desire.
Clearly, the situation was serious. There could be only one obvious choice... 
The immediate outcome of that choice was to discover that BOARD 11 can destroy dimensional relationships too. Now that I have no further distractions, allow me to explain.
BACKGROUND
I build my models in a sequential manner, starting with dimensions. I then build the relationship trees and apply the rule that there can be no action on the dimension entities, only on the codes and descriptions of the attributes of it. Finally, I fill the cubes for which the rule is that there can be no action on entities - only the cube values can carry an action if needed.
Now, my dimensions have a hierarchy and I include, in each successive data reader, the previous dimension in order that I can pull only those members relating to the previous dimension as the data warehouse contains many records from which I need only a limited sub-set. Below can be seen the dimensions BRANCH and LEASE. BRANCH is my primary dimension. All LEASE records must relate to a valid BRANCH code. Image 1 illustrates my relationship structure and Image 3 shows the destroyed structure after the datareaders are run.
Image 1: Relationship structure - before data readers are run
Image 2 illustrates the datareaders used to population BRANCH and LEASE. You will see that LEASE contains BRANCH and that the action for BRANCH is blank. Up to now, I had a very efficient dataload process. However, in BOARD 11, this approach actually destroys the pre-configured relationships.
Image 2: SQL Datareaders used to populate BRANCH and LEASE dimensions
Image 3: Relationship structure after the data readers have been run
For completeness, Image 4 is the dataload procedure used to show where the data readers appear within it.
Image 4: Dadtaload procedure
IMPACT
As it stands, I must remove the BRANCH reference in the LEASE data reader and import all BRANCH and LEASE entities. I tried to use 2 tables with an inner join between the BRANCH and LEASE table but this also failed so others must be experiencing this issue too.
I devise and employ best practice so that a model can be returned to months or even years later by me or my team and we know precisely what to look for where. I want to continue working with the best practice I have developed across all of our models. This issue requires me to re-structure the entire model architecture to compensate for a big inconsistency which effects the fundamental model structure.
Also, this issue will impact everyone else modelling in this way as the cause is not immediately obvious; less so when migrating from a previously perfectly functional model in B10 to B11.
I would be very interested to hear from anyone that has experienced this issue and what they did to mitigate it.
Answers
-
Hi Paul,
we are aware of this problem on the data-readers. We are releasing today a patch that solves this problem.
Many thanks for your feedback.
Best regards,
Andrea
4 -
Excellent - Many thanks to the Lab and support teams.
Kind regards,
Paul Wyatt
1 -
Hi Andrea,
Thank you for the information, we are also interested in this patch
Regards
1 -
I am so happy to announce that my relationship is...FIXED! Yes, I though it was beyond repair and destroyed forever... My wife hates me still but...meh. ...BOARD is fixed
In all sincerity, thank you to the BOARD Lab and the engineers for your support. Your quick responses and supportive correspondence prevented me from embarking on devising a whole new architecture which would have been painful to have to create and implement.
Thanks guys.
5
