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?
You can track updates by giving any value which is greater than the maximum value in your identity column.
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 .
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.
@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 ?
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
