Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

many degenerate dimensions to one fact

2 posters

Go down

many degenerate dimensions to one fact Empty many degenerate dimensions to one fact

Post  bfnz 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

Back to top Go down

many degenerate dimensions to one fact Empty Re: many degenerate dimensions to one fact

Post  nick_white 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 : 364
Join date : 2014-01-06
Location : London

Back to top Go down

many degenerate dimensions to one fact Empty Re: many degenerate dimensions to one fact

Post  bfnz 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

Back to top Go down

many degenerate dimensions to one fact Empty Re: many degenerate dimensions to one fact

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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