How to choose a primary key for a fact table when the composite key is not unique
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?
Hi
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.
Thanks
Lokesh
@erlokeshsharma08 Yes there is a datekey in my source data but I can't use this as primary key because isn't a business key .Actually my problem is my source table lacks a business key . You talked about the guid colum , what is it exactly ?
DECLARE @myid uniqueidentifier = NEWID();
Hi @AkIn, it is used to generate random values. Refer to the above code for declaring a variable.
On hind sight I don't think it would be good solution, go for identity column instead.
I'm thinking if my fact table is lacking a business key so How I'm going to update it whenever there are update in the source table . Thank you for answering me .
You can track updates by giving any value which is greater than the maximum value in your identity column.
