Association between dim and fact tables based on more than one column

[Anonymous]

Hi All,

I have a fact table and dimension table. In fact table I have 6 columns which are common with dimension columns. I want to make association(inner join) between these two tables. It will create synthetic table. How to overcome this situation

[Anonymous]

Could you share the structure of your 2 tables ?

[marcus_sommer]

If you want to have both tables separately then you will need to create a composite key like:

F1 &'|' & F2 ... as KeyFactDimension // maybe within an autonumber()

Otherwise you could merge both tables per joining or mapping: Mapping as an Alternative to Joining.

- Marcus

[Anonymous]

Assume your Fact table looks like?

FactTable:

Load

Dim1,

Dim2,

Dim3,

Sales,

Revenue

From FactTable;

inner join(FactTable)

DimTable:

Load

Dim1,

Dim2,

Dim3,

Dim4

From DimTable;

I do not think that you will get synthetic key from above script as the result will merge into 1 single FactTable.

Are you facing any issue?

If still you want to remove more than 1 common field then you can do following option:

1) Comment the Field or Delete

2) Rename the Field

3) Composite Key of common field

4) Qualify/Unqualify Statement