How to choose a primary key for a fact table when the composite key is not unique

Question

question

AkIn asked May 10, '18

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?

ssis data-warehouse sql-server 2017

10 |1200

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

Answer 1 · 2018-05-11T07:58:50Z

erlokeshsharma08 answered May 11, '18

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

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

Answer 2 · 2018-05-14T18:17:49Z

AkIn answered May 14, '18

@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

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

Answer 3 · 2018-05-15T07:57:44Z

erlokeshsharma08 answered May 15, '18

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

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

Answer 4 · 2018-05-15T08:10:19Z

AkIn answered May 15, '18

@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

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

Answer 5 · 2018-05-15T09:12:04Z

erlokeshsharma08 answered May 15, '18

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

10 |1200

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

question