DegenerateDimension joining Fact tables
2 posters
Page 1 of 1
DegenerateDimension joining Fact tables
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 ?
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
Re: DegenerateDimension joining Fact tables
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.
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.
thx
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
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
Similar topics
» Joining fact tables
» Joining two Fact tables???
» Joining fact tables
» Joining Fact tables
» Joining Multiple Fact Tables
» Joining two Fact tables???
» Joining fact tables
» Joining Fact tables
» Joining Multiple Fact Tables
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum