Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Fact table without unique key ?

4 posters

Go down

Fact table without unique key ? Empty Fact table without unique key ?

Post  VTK Mon Sep 09, 2013 12:28 am

I would like to know if it's OK to design a transnational fact table without unique key(s) meaning there are no column(s) in the fact table which gives you a unique record. so you can't say 1 row by this by this....
Grain is commission transactions and unfortunately there is nothing in the source system which tracks the transaction.
It's kind of violating the 4 step process as you can't identify your grain as 1 row by ...
I know this makes this table as either inserts only or truncate and reload every time with all history and also it's not easy to join to this fact table to drill across table.
Is this method accepted in the kimball methodology ?
What are the other problems we might have with this approach ?

Thanks





VTK

Posts : 50
Join date : 2011-07-15

Back to top Go down

Fact table without unique key ? Empty Re: Fact table without unique key ?

Post  ngalemmo Mon Sep 09, 2013 2:41 am

No, this is fine. There is no reason a fact table requires a primary key other than to update rows.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Fact table without unique key ? Empty Re: Fact table without unique key ?

Post  BoxesAndLines Mon Sep 09, 2013 10:49 am

I would add a surrogate key. I always find the need to get to one row.
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Fact table without unique key ? Empty Re: Fact table without unique key ?

Post  VTK Mon Sep 09, 2013 1:10 pm

ngalemmo - So, We don't need to define the grain of the fact table with 1 row by column(s) statement and we can simply say 1 row by business process ?

BoxesAndLines - We can add SK but it won't be useful to identify a unique row based on the business.

Thanks

VTK

Posts : 50
Join date : 2011-07-15

Back to top Go down

Fact table without unique key ? Empty Re: Fact table without unique key ?

Post  LAndrews Mon Sep 09, 2013 1:52 pm

Grain is best described in business terms, not technical columns.

For your example, the fact grain can be described as "one row for each commission transaction".

As ngalemmo said, there is no need to have a primary key on a fact table.

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

Back to top Go down

Fact table without unique key ? Empty Re: Fact table without unique key ?

Post  VTK Tue Sep 10, 2013 11:53 am

Boxes and Lines : Can you give me couple of reasons why we need SK in the fact table ?

VTK

Posts : 50
Join date : 2011-07-15

Back to top Go down

Fact table without unique key ? Empty Re: Fact table without unique key ?

Post  BoxesAndLines Tue Sep 10, 2013 3:39 pm

Ngalemmo already gave you one, the need to update a row. Additionally, if the grain of my fact table cannot be defined by dimension relationships, then my grain is too low. In your case, I would aggregate all transactions to the lowest grain accessible by defined dimensions. I also have been known to join fact tables, especially ones at the same grain and a single unique column can come in handy. I also like to capture data quality metrics and publish them on a dashboard. Here the users can drill down to find the actual offending rows in the fact. The design is much simpler if I can access any fact table via a single column surrogate key.
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Fact table without unique key ? Empty Re: Fact table without unique key ?

Post  VTK Wed Sep 11, 2013 11:08 am

Boxed and Lines:

I don't know how you update a row in a table without natural key by using just SK ?

Thanks

VTK

Posts : 50
Join date : 2011-07-15

Back to top Go down

Fact table without unique key ? Empty Re: Fact table without unique key ?

Post  BoxesAndLines Thu Sep 12, 2013 9:12 am

If you are updating a fact row, something has gone wrong. And when you are fixing the bad rows, you won't be using the SK to access the row. OTOH, if you want someone to "look" at a specific row for some reason, you can provide that person with a unique row identifier.
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Fact table without unique key ? Empty Re: Fact table without unique key ?

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum