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

DegenerateDimension joining Fact tables

2 posters

Go down

DegenerateDimension joining Fact tables Empty DegenerateDimension joining Fact tables

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

Back to top Go down

DegenerateDimension joining Fact tables Empty Re: DegenerateDimension joining Fact tables

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

DegenerateDimension joining Fact tables Empty thx

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

Back to top Go down

DegenerateDimension joining Fact tables Empty Re: DegenerateDimension joining Fact tables

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