Synthetic Key Issue

[#1]

Happy Friday Everyone!! I've got a right issue here and I can't work it out. Basically I want the two fact tables to relate to the two dimension tables independently and I don't know how to do it whilst keeping the field names the same. How do I avoid creating the synthetic key whilst keeping things quick....there is over 30 million rows in the back end of this!

Kind Regards,

Miles

[Anonymous]

How about concatenating your 2 Fact Tables into a single Fact table ?

[#3]

so lets do that....I come back to my original problem - I have the key twice in my fact table, Where the item was sold at and where it was taken at. I can't name the field twice as the same name....and then again I don't want two dimensions ie when I click on country I just want to click on country and it be the same for both

[arsal_90]

Use Link Table methodology

[#5]

with 30 million rows though it gets really slow

[arsal_90]

LinkTable:

Load distinct FK_DimLocation,

                    FK_DimCurrency,

                    FK_DimLocation&'-'&FK_DimCurrency as Key

Resident Fact1;

concatenate (LinkTable)

Load distinct FK_DimLocation,

                    FK_DimCurrency,

                    FK_DimLocation&'-'&FK_DimCurrency as Key

Resident Fact2;

Make a composite key in both fact tables like that ( FK_DimLocation&'-'&FK_DimCurrency as Key) and drop that fields from both facts

[sunny_talwar]

How about qualifying the two fact tables so that they don't have the same field names without needing you to change the name of each of the field name manualy?

Qualify *;

UnQualify someColumns ; (based on the idea that some columns may be allowed to link)

TableName:

LOAD yourColumns

FROM XYZ;

UnQualify *;

HTH

Best,

S

[arsal_90]

use autonumber(FK_DimLocation&'-'&FK_DimCurrency,'LOCCUR') as Key

please replace all composite keys with this

Now i pretty sure your performance will not down

[arsal_90]

LinkTable:

Load distinct FK_DimLocation,

                    FK_DimCurrency,

                    autonumber(FK_DimLocation&'-'&FK_DimCurrency,'LOCCUR') as Key

Resident Fact1;

concatenate (LinkTable)

Load distinct FK_DimLocation,

                    FK_DimCurrency,

                   autonumber(FK_DimLocation&'-'&FK_DimCurrency,'LOCCUR') as Key

Resident Fact2;

Make a composite key in both fact tables like that (autonumber(FK_DimLocation&'-'&FK_DimCurrency,'LOCCUR') as Key) and drop that fields from both facts

[arsal_90]

Miles let me know if this solution fulfill your requirement