Fact table without unique key ?
4 posters
Page 1 of 1
Fact table without unique key ?
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
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
Re: Fact table without unique key ?
No, this is fine. There is no reason a fact table requires a primary key other than to update rows.
Re: Fact table without unique key ?
I would add a surrogate key. I always find the need to get to one row.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Fact table without unique key ?
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
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
Re: Fact table without unique key ?
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.
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
Re: Fact table without unique key ?
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
Re: Fact table without unique key ?
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- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Fact table without unique key ?
Boxed and Lines:
I don't know how you update a row in a table without natural key by using just SK ?
Thanks
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
Re: Fact table without unique key ?
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- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» load a table without unique indentifier
» Relationship to fact from dimension is not unique BK
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» Integrating new fact table which has one to many relationship with the main fact table in existing star schema
» How to create fact table with measures derived from comparing two fact table rows
» Relationship to fact from dimension is not unique BK
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» Integrating new fact table which has one to many relationship with the main fact table in existing star schema
» How to create fact table with measures derived from comparing two fact table rows
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum