question

AkIn avatar image
AkIn asked

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?

ssisdata-warehousesql-server 2017
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

erlokeshsharma08 avatar image
erlokeshsharma08 answered

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

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

AkIn avatar image
AkIn answered

@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 ?

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

erlokeshsharma08 avatar image
erlokeshsharma08 answered
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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

AkIn avatar image
AkIn answered

@erlokeshsharma08

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 .

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

erlokeshsharma08 avatar image
erlokeshsharma08 answered

You can track updates by giving any value which is greater than the maximum value in your identity column.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.