Hello everyone , My problem is : I 'm designing a data warehouse for a bank as my final year project using sql server data tools and sql server . one of my data mart captures the income and expenses of the bank . my fact table composed of these attributes:
[PROD_CHARGE_ID],[CUSTOMER_ID],[CURRENCY_ID],[SECTOR_ID] ,[INDUSTRY_ID],[AGENCY_ID],[CATEGORY_ID],[COUNTRY_ID],[BOOKING_DATE_ID] ,[ACCOUNT_OFFICER],[RISK_CLASS],[COMPANY],[AMOUNT_FOREIGN],[AMOUNT_LOCAL]
The dimensions are:
DimCustomer , DimCurrency,DimSector,DimIndustry,DimAgency,DimCategory , DimCountry and Dimdate
The problem is that the combination of the Foreign key of dimensions doesn't allow me to have a unique composite primary key for the Fact table . what's the solution in this case ? do I have to set an identity key for the Fact table? or can I use the primary key of the table Source IncomeAndExpenses from where I extracted the measures and dimensions?
Answer by erlokeshsharma08 ·
I think creating a new surrogate key (identity column or a guid column) is the recommended approach theoretically.
If there is datekey somewhere in your source data, than adding that might help you in creating primary key.