DegenerateDimension joining Fact tables

View previous topic View next topic Go down

DegenerateDimension joining Fact tables

Post  MarkSe on Fri Sep 21, 2012 4:53 am

i have a couple of Fact tables - ParentProcessFact and ChildProcessFact.

Both contain the SerialNumber as a Degenerate Dimension.

in the ParentProcessFact table, out of c 2m rows, 25,000 have a serial number value
in the ChildProcessFact table, 20,000 have a serial number value that matches the parent
(yes, more parents than children.....)

i would like to do a query that gives me all of the SerialNumber details
- i.e. the ParentFact details and all of the ChildFact details for a specific SerialNumber (assuming details on both tables)

what would be the best way to join these two in a query as i dont want to do a fact table join....
should i extract the SerialNumber and put it into a conformed (junk ?) dimension
or
allocate the parentprocessFacts to each childprocessfact record
or
rely on SQL queries aggregate / group on the SerialNumber
or
is there another (better) option ?




MarkSe

Posts : 5
Join date : 2011-07-21

View user profile

Back to top Go down

Re: DegenerateDimension joining Fact tables

Post  ngalemmo on Mon Sep 24, 2012 2:16 pm

First, you need to get away from the idea that dimensions and degenerate dimensions are somehow different. They are not. A dimension simply has more attributes, while a degenerate dimension has only 1. So, putting a degenerate dimension into a junk dimension does nothing.

Second, dimensional conformance has nothing to do with keys or tables. Conformance occurs through common attribute values, not common keys. So, degenerate dimensions are just as conforming as any other dimensions.

As for what to do, I have no idea. You listed both allocating parent measures and aggregating child measures, so it is not clear what, exactly, you are trying to do. Generally speaking, aggregating is much, much simpler than allocating... both technically and politically. As far as joining facts, since it appears you have data in both tables that you want to use, I don't see how you can avoid combining the data. UNION will work for you if you want to avoid a full outer join.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

thx

Post  MarkSe on Tue Sep 25, 2012 6:21 am

apologies for the poor description of what we are trying to do
and thx for the response
your comments on degenerate dimensions and conformance has helped me revisit / rethink the design
(and to try and stay away from allocating


MarkSe

Posts : 5
Join date : 2011-07-21

View user profile

Back to top Go down

Re: DegenerateDimension joining Fact tables

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