Montly Snapshot views with transactional Fact table w/SCD2
Page 1 of 1
Montly Snapshot views with transactional Fact table w/SCD2
I am a new at working with Dimensional Database design. I am struggling with something that probley is real easy to do, but just can't find documentation that will explain it.
I want to create a monthly snapshot view of my transactional Fact table with SCD2 dim tables.
If I create snapshot fact table, then I am wasting space on 100,000 customer that products don't change, yet I re-create them for the next month.
I only want to show the change to the Fact row when a Dim changes, Fact is added, update, or removed.
But creating a monthly snapshot of this stumps me.
Here is a simple example:
(What can my Fact table look like? Can I have Tran Date, and End Date to help know when a fact has changed?)
(Can you help, or show me a better way)
Dim_Cust
Cust_Key Cust Id Cust Name Efct Date End Date
-------- ------- --------- --------- ---------
...1......1004....Bob.......1-1-1.....Null
...2......1005....John......1-1-1.....Null
...3......1006....Al........3-1-1.....Null
Dim_Product:
Prod_Key Product Id Product Desc Product Cost Efct Date End Date
-------- ---------- ------------ ------------ --------- --------
...1.......A01.......Nail..........2.00........1-1-1.....Null
...2.......N01.......Hammer........5.00........1-1-1.....Null
...3.......Q02.......Glove.........3.00........1-1-1.....2-1-1
...4.......Q02.......Mitten........3.00........2-1-1.....Null
Fact.Table: (factless)
Trans.Date Cust_Key Prod_Key End Date
---------- -------- -------- --------
...1-1-1.....1........2.......null
...1-1-1.....1........1.......2-1-1 <-- Remove in Feb
...1-1-1.....1........3.......2-1-1 <-- Rename in Feb
...1-1-1.....2........1.......null
...2-1-1.....1........4.......null <-- Renamed
...2-1-1.....2........4.......null <-- Added in Feb
...3-1-1.....3........2.......null <-- Added in Mar
** Can I have a End date to handle change in fact table?
My monthly snapshot view:
JANUARY:
Cust id Cust Name Product Id Product Name Product Cost
------- --------- ---------- ------------ ------------
.1004....Bob........N01.......Nail............2.00
.1004....Bob........A01.......Hammer..........5.00
.1004....Bob........Q02.......Glove...........3.00
.1005....John.......A01.......Hammer..........5.00
** This is the initial view of the data
FEBURARY:
Cust id Cust Name Product Id Product Name Product Cost
------- --------- ---------- ------------ ------------
.1004....Bob........N01.......Nail............2.00
.1004....Bob........Q02.......Mittens.........3.00
.1005....John.......A01.......Hammer..........5.00
.1005....John.......Q02.......Mittens.........3.00
**Cust 1004 has 1 less Product, and other product renamed
Cust 1005 adds a product which was renamed
MARCH:
Cust id Cust Name Product Id Product Name Product Cost
------- --------- ---------- ------------ ------------
.1004....Bob........N01.......Nail............2.00
.1004....Bob........Q02.......Mittens.........3.00
.1005....John.......A01.......Hammer..........5.00
.1005....John.......Q02.......Mittens.........3.00
.1006....Al.........N01.......Nail............2.00
** no changes to 1004 & 1005, but Add 1006
I want to create a monthly snapshot view of my transactional Fact table with SCD2 dim tables.
If I create snapshot fact table, then I am wasting space on 100,000 customer that products don't change, yet I re-create them for the next month.
I only want to show the change to the Fact row when a Dim changes, Fact is added, update, or removed.
But creating a monthly snapshot of this stumps me.
Here is a simple example:
(What can my Fact table look like? Can I have Tran Date, and End Date to help know when a fact has changed?)
(Can you help, or show me a better way)
Dim_Cust
Cust_Key Cust Id Cust Name Efct Date End Date
-------- ------- --------- --------- ---------
...1......1004....Bob.......1-1-1.....Null
...2......1005....John......1-1-1.....Null
...3......1006....Al........3-1-1.....Null
Dim_Product:
Prod_Key Product Id Product Desc Product Cost Efct Date End Date
-------- ---------- ------------ ------------ --------- --------
...1.......A01.......Nail..........2.00........1-1-1.....Null
...2.......N01.......Hammer........5.00........1-1-1.....Null
...3.......Q02.......Glove.........3.00........1-1-1.....2-1-1
...4.......Q02.......Mitten........3.00........2-1-1.....Null
Fact.Table: (factless)
Trans.Date Cust_Key Prod_Key End Date
---------- -------- -------- --------
...1-1-1.....1........2.......null
...1-1-1.....1........1.......2-1-1 <-- Remove in Feb
...1-1-1.....1........3.......2-1-1 <-- Rename in Feb
...1-1-1.....2........1.......null
...2-1-1.....1........4.......null <-- Renamed
...2-1-1.....2........4.......null <-- Added in Feb
...3-1-1.....3........2.......null <-- Added in Mar
** Can I have a End date to handle change in fact table?
My monthly snapshot view:
JANUARY:
Cust id Cust Name Product Id Product Name Product Cost
------- --------- ---------- ------------ ------------
.1004....Bob........N01.......Nail............2.00
.1004....Bob........A01.......Hammer..........5.00
.1004....Bob........Q02.......Glove...........3.00
.1005....John.......A01.......Hammer..........5.00
** This is the initial view of the data
FEBURARY:
Cust id Cust Name Product Id Product Name Product Cost
------- --------- ---------- ------------ ------------
.1004....Bob........N01.......Nail............2.00
.1004....Bob........Q02.......Mittens.........3.00
.1005....John.......A01.......Hammer..........5.00
.1005....John.......Q02.......Mittens.........3.00
**Cust 1004 has 1 less Product, and other product renamed
Cust 1005 adds a product which was renamed
MARCH:
Cust id Cust Name Product Id Product Name Product Cost
------- --------- ---------- ------------ ------------
.1004....Bob........N01.......Nail............2.00
.1004....Bob........Q02.......Mittens.........3.00
.1005....John.......A01.......Hammer..........5.00
.1005....John.......Q02.......Mittens.........3.00
.1006....Al.........N01.......Nail............2.00
** no changes to 1004 & 1005, but Add 1006
smizguy- Posts : 1
Join date : 2009-02-03
Similar topics
» Transactional detail fact table w/complimentary snapshot table. How do the two play together?
» transactional fact vs periodic snapshot fact
» Transactional Fact and update of records
» Fact table for transactional data
» Transactional fact table with a date ?
» transactional fact vs periodic snapshot fact
» Transactional Fact and update of records
» Fact table for transactional data
» Transactional fact table with a date ?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum