Natural key and fact tables
I am new to sizing modeling. I believe you guys can help me with the following doubts.
In a production system, I have a transaction table like a sales table. The unique identifier is a primary key called SaleId. Example:
My doubt is that when modeling a fact table, should the SaleID be included in the fact table as a NaturalKey?
Also does the fact table have to have a SurrogateKey?
Please feel free to send me the link as a reference. thanks in advance
source to share
Technically speaking, this is probably not a natural key - it looks like a generated system. However, it is sometimes very important to store the system generated identifier in the Factor for use as a degenerate dimension. These are usually cases where either business users see this system-generated identifier (order numbers, account numbers, purchase order numbers, etc.), or there is no other useful way to identify some of the strings that might be grouped together.
If the users of your BI solutions are likely to want to drill down on the information and see it for sale, then SaleID might well be a good candidate for this treatment. Consider if they have another way to achieve this level - could a customer be linked to two separate sales on the same day? If so, would your users like to view them as two separate sales? You may need to talk to them to find out what will be helpful to them. If for some reason you cannot get a clear answer, I would say keep it - there is little harm in there and you can always remove it later if not in use.
Here the Kimball group will take on "degenerate sizes" if you don't know how they work at all:
http://www.kimballgroup.com/2003/06/design-tip-46-another-look-at-degenerate-dimensions/
When it comes to surrogate fact table keys, I always use them. As Kimball Design Council # 81 points out that they are sometimes useful, which is something I would rather include in the beginning and not use than understand later, which would be useful to have. Point 2 - where you might want to make updates by inserting new lines and deleting old ones - certainly refers to the work I've done.
source to share
The requirement for a primary key in a fact table depends on the type of the fact table. Transactional facts that are never updated do not need this. Periodic snapshots are probably not needed if the current period is not an up-to-date measure. Accumulative snapshots definitely need it.
source to share