Fact table primary key

4
votes

I have a fact table with 8 foreign keys (referencing 8 dimensions), but even a combination of all eight keys does not uniquely identify a row. Do I need to add another attribute from the original data (i.e. "project-id" attribute, which is useless for anything), so that I can have a primary key, or I can leave fact table as it is, without a primary key?

3
data-warehouse
Give us more information. What are the keys and what is the reality behind? There are maybe some design patterns applicable to your situation already. - Tomas Greif
@twn08 This is actually a homework assignment. I was given data in this csv file and my task is to make a star schema. I don't have any other info about the data, so I need to figure out dimension and fact tables myself. - Marko
Well, you can't really create dimensional model without understanding your data. I can see that these are some projects in some countries over time. Maybe you can list attributes in that file, their meaning and possible values and your current solution. There has to be some primary key in every table. - Tomas Greif

3 Answers

6
votes

The first rule of a fact table is to declare your grain - what uniquely identifies a row.

It sounds like you haven't declared your grain for this table. If the grain of the table is "one row per project", then you need to include project as a degenerate dimension in the table.

1
votes

Every table must have a primary key. That's relational rule #1.

You can always add a surrogate key, but I like the idea of a fact table having attributes that satisfy a unique constraint. I second your idea: add more attributes until you have a unique constraint.

0
votes

Along with those 8 foreign key include a simple surrogate key (like a row index) to each row. This will identify every row of the fact table uniquely

For a surrogate key you may start from an index say 1 for the first row and then increment the index by one each time you make a new entry to the fact table