Fact table without unique key ?

View previous topic View next topic Go down

Fact table without unique key ?

Post  VTK on 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

View user profile

Back to top Go down

Re: Fact table without unique key ?

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Fact table without unique key ?

Post  BoxesAndLines on Mon Sep 09, 2013 10:49 am

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

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

View user profile

Back to top Go down

Re: Fact table without unique key ?

Post  VTK on 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

View user profile

Back to top Go down

Re: Fact table without unique key ?

Post  LAndrews on 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

View user profile

Back to top Go down

Re: Fact table without unique key ?

Post  VTK on 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

View user profile

Back to top Go down

Re: Fact table without unique key ?

Post  BoxesAndLines on 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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Fact table without unique key ?

Post  VTK on 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

View user profile

Back to top Go down

Re: Fact table without unique key ?

Post  BoxesAndLines on 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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Fact table without unique key ?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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