Hi,

I have been searching around for any help with my query but have not found anything other than people telling me not to use bitmask fields!

I am capturing row changes manually via a high frequency ETL task.  It works effectively however i am capturing the movement of multiple fields.  A simple example, for Order lines, i have a price, a discount and a date.  I am capturing a 001, 010, 100 respectively for each change.  I would like my users to be able to select from a dimension which has the 3 members in it and they can select one, multiples, or all values (i.e. only want to see rows that have had the date and price changed). 

Obviously if i only had 3 columns i would use bit's and be done with it, i have many different values (currently around 24 and growing).

Hopefully a) its possible, and b) somebody can point me to an example or some help?

Thanks in advance,

Dom

• Edited by Dominic Cotton 15 hours 15 minutes ago

So you have 2 possible designs to consider. 

1) Create a junk dimensions and create a boolean value (yes/no or Change/NoChange etc) for each value that you are tracking. That means to filter on multiple changes you would need to filter on multiple attributes. I'm not sure it that's what you were talking about when you mentioned "if i only had 3 columns i would use bit's and be done with it,", but I'm pretty sure that this is going to be the solution with the best performance.

eg

BitMask   DateChange  DiscountChange  PriceChange

001          No                  No                      Yes
010          No                  Yes                     No
100          Yes                 No                      No
011          No                  Yes                     Yes 
110          Yes                 Yes                     No
101          Yes                 No                      Yes
111          Yes                 Yes                     Yes

2) The only other option I can think of is to implement a many-to-many relationship. Effectively you are saying that 1 fact row can have many changes and one change type can relate to many fact rows. The trick here is that you really need to keep the bridge table under 1-2 million records and with 24 bits that may not be possible unless you can eliminate unused combinations and use the matrix compression technique for minimizing the size of the bridge table.