AnsweredAssumed Answered

(FIXED) BOARD 11:  Datareader can destroy your relationship

Question asked by Paul Wyatt - Avison Young UK on May 25, 2019
Latest reply on May 29, 2019 by Paul Wyatt - Avison Young UK

 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 - Busted Quotes. QuotesGram

 

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.

Outcomes