Data Warehousing and Business Intelligence

21 August 2015

Flip Flopping in Dimension Tables

Filed under: Analysis Services — Vincent Rainardi @ 7:01 am

When a dimension is populated from two different sources, a phenomena known as Flip Flopping could occur. Source1 populates an attribute with Value1, whereas Source2 populates the same attribute with Value2. Everyday, the ETL creates a new row with the attribute = Value1. Then a few seconds later it closes that row and creates another new row with the attribute = Value2.

Flip Flop

Today is 21/8/2015, current time: 7:35am. ProductKey 3156 is yesterday’s rows. The ETL this morning closed this row at 4:12:27. It then created a new row (3157) because it thought that the size of the tumble dryer was 7 kg (sourced from Source1). 4 seconds later, the ETL read Source2 and the size there was 6 kg. So it closed 3157 and created 3158 with size = 6 kg, which is now the active row.

To solve this, either update the source system to agree on the same size (best approach), or customise the ETL to ignore the size data from one of the two sources.

Advertisements

3 Comments »

  1. well, it will depend on how you will use your data….

    Forget any update on the source system and…. why you will discard or ignore the size? I don’t know the model or the business, but, what would you do if you can’t do it? Maybe the same product with different sizes should be managed like two different products…..

    Hope it will help.

    OVC

    Comment by OVC — 24 August 2015 @ 10:52 pm | Reply

  2. Hi Vincent,

    Couldn’t be good to have an MDM system that has those attributes on a “single version” and it’s matching keys to every source? With that business users would be in charge on mappings (I mean data steward) and the process could be even good to solve data quality issues on the ETL (I mean to feed a Data Quality process).

    Regards

    Comment by Oscar Lopez — 13 September 2015 @ 8:40 am | Reply

    • Yes that would be ideal Oscar (having an MDM). But in the real world the situation is mostly not ideal 🙂

      Comment by Vincent Rainardi — 14 September 2015 @ 6:32 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: