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 · May 11, 2018 at 07:58 AM
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
Answer by AkIn · May 14, 2018 at 06:17 PM
@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 ?
Answer by erlokeshsharma08 · May 15, 2018 at 07:57 AM
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.
Answer by AkIn · May 15, 2018 at 08:10 AM
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 .
Answer by erlokeshsharma08 · May 15, 2018 at 09:12 AM
You can track updates by giving any value which is greater than the maximum value in your identity column.