many degenerate dimensions to one fact

View previous topic View next topic Go down

many degenerate dimensions to one fact

Post  bfnz on Thu Jul 10, 2014 3:59 pm

I have a situation where I have a degenerate dimension with many rows per one fact.

Basically itís a seal number, and several seal numbers are slapped on a container over a period of time but there is no other information about the seal, no date it was put on, no reason why, and new seals donít replace old seals so they remain active and just accumulate.

For reporting purposes Iíd quite like to pivot them into columns but there is no limit to their number so this isnít possible.

What Iíve done is created a bridge table with a foreign key from the fact included in the bridge table, but it doesnít make sense to me to have two tables when one will do so Iíve made it a bridge to nowhere and just included the degenerate dimension as a column in the bridge table rather than create a separate dimension.

Is this the correct approach?

thanks

bfnz

Posts : 4
Join date : 2014-07-10

View user profile

Back to top Go down

Re: many degenerate dimensions to one fact

Post  nick_white on Fri Jul 11, 2014 7:26 am

Yours is probably as good a solution as any - I guess. Obviously as your fact record will reference multiple records in the bridge table you need to ensure that queries don't count your measures multiple times - but that's no different to any other situation using a bridge table.
The only other possibility I can think of is if you held the seal numbers as a single delimited string - possibly as a degenerate dim in your fact or as a Dim table with effectively just a key and the seals column.
Might be an alternative if you can 'unpack' the delimited string easily and use it in your reports in the way you want.
Obviously would require updates - which may be a performance issue
You'd have to be sure that the your unlimited number of seal numbers would never exceed the column width you'd defined (constrained by the restrictions of your DB).
I'd do it as a DD if the column was small-ish and the seal numbers were used in a lot of reports hitting the fact table. I'd do it in a separate Dim table if the column could end up being large and/or it was infrequently used.
Kimball coves the sort of thing I'm thinking about - search for "Skill Keyword Text String" in his latest DM book - it's around Fig 9.9

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: many degenerate dimensions to one fact

Post  bfnz on Fri Jul 11, 2014 4:40 pm

If I were the report developer i'd prefer to have both options so i could choose how to use it in reports, the lower level grain of the bridge table plus the concatenated text string in one of the dimensions not causing many to one problems in relation to the fact.

bfnz

Posts : 4
Join date : 2014-07-10

View user profile

Back to top Go down

Re: many degenerate dimensions to one fact

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum