Hi Virgile, fact tables aren't in the data vault model in the traditional "star schema" sense. Details that would be in a fact table are located between the link and satellite tables.
Let's say I bought a Nike Jumper for 45.00. You'd have:
- Customer_Hub: Unique Customer IDs
- Product_Hub: Unique IDs for products
- Order_Hub: Unique transaction ID
- Order_Link: Links the customer, product and order together
- Customer_Sat: Will contain Date of Birth, email etc.
- Product_Sat: Product details like brand, size, pricing etc
- Order_Sat: Link Key, quantity, total amount, transaction date etc.
If you have multiple types of facts (e.g., sales, shipments, refunds), you would create:
Separate Link tables for each relationship (e.g., SalesLink, ShipmentLink).
Attach corresponding Satellite tables for measures and details (e.g., SalesLink_Sat, ShipmentLink_Sat).
As mentioned above, it does get quite complicated; but this method, you can add new attributes without having to alter tables as well as track history really well.
Hopefully this helps a bit but feel free to DM if you want me to explain further?
Hi, in the data vault model: where are the facts tables stored ? in the Hub? How would you organize several fact tables?
Hi Virgile, fact tables aren't in the data vault model in the traditional "star schema" sense. Details that would be in a fact table are located between the link and satellite tables.
Let's say I bought a Nike Jumper for 45.00. You'd have:
- Customer_Hub: Unique Customer IDs
- Product_Hub: Unique IDs for products
- Order_Hub: Unique transaction ID
- Order_Link: Links the customer, product and order together
- Customer_Sat: Will contain Date of Birth, email etc.
- Product_Sat: Product details like brand, size, pricing etc
- Order_Sat: Link Key, quantity, total amount, transaction date etc.
If you have multiple types of facts (e.g., sales, shipments, refunds), you would create:
Separate Link tables for each relationship (e.g., SalesLink, ShipmentLink).
Attach corresponding Satellite tables for measures and details (e.g., SalesLink_Sat, ShipmentLink_Sat).
As mentioned above, it does get quite complicated; but this method, you can add new attributes without having to alter tables as well as track history really well.
Hopefully this helps a bit but feel free to DM if you want me to explain further?