JOIN 2 FACT tables

• fasttrack

  SSCertifiable

  Points: 6071

  More actions

  November 23, 2007 at 1:52 am

  #200904

  Hello,

  I have the following Datawarehouse problem (SQL Server 2005 and SSIS 2005):

  2 Fact tables and both with some foreign surrogate keys of dimensions (some of them with common dimensions, other with different dimensions).

  I have to create another FACT table that is an aggregation and join table between the 2 ones.

  the dimension are more than one and some of them in common the other ones different dim tables

  eg

  fact1

  FKdimKey1

  FKdimKey2

  FKdimkey3

  FKdimKey5

  FKdimKey6

  Fact1Col1

  Fact1Col2

  Fact1Col3

  Fact1Col4

  fact 2

  FKdimkey1

  FKdimKey2

  FKdimKey4

  Fact1Col1

  Fact1Col2

  Fact1Col3

  FKdimKey1 and FKdimKey2 are in common the other ones link to differernt dimensions

  How can I do?

• iBuildDW

  Mr or Mrs. 500

  Points: 536

  More actions

  November 29, 2007 at 6:07 am

  #755368

  net (11/23/2007)

  ---

  fact1

  FKdimKey1

  FKdimKey2

  FKdimkey3

  FKdimKey5

  FKdimKey6

  Fact1Col1

  Fact1Col2

  Fact1Col3

  Fact1Col4

  fact 2

  FKdimkey1

  FKdimKey2

  FKdimKey4

  Fact1Col1

  Fact1Col2

  Fact1Col3

  FKdimKey1 and FKdimKey2 are in common the other ones link to different dimensions

  Are Fact1Col1, Fact1Col2,Fact1Col3 the same measure?

  Are they additive between the two tables or do you need to determine which are already in the fact1 table?

  The summary table should be at the granularity common to the two tables FKdimKey1,FKdinKey2,sum(Fact1Col1),sum(Fact1Col2),sum(Fact1Col3),sum(Fact1Col4)

  because there is no way to aggregate Fact1Col4 at the FKdimKey4 level. Unless that detail is elsewhere.