Multiple granular levels for fact table
5 posters
Page 1 of 1
Multiple granular levels for fact table
My fact table has to incorporate both at Transaction level and Accumulative , my basic design for Transaction level is as follows
If I aggregate the values then query will take time to execute . How can I provide cumulative information like total_due at store level or customer level from this fact table? shall i go for one more fact table for Accumulative information ?
Please suggest.
Thanks,
Hesh.
- Code:
CUSTOMER_KEY, LOAN_KEY, TIME_KEY, LOAN_AMT, TOTAL_DUE, LOAN_STATUS, TRANSACTION,STORE
9000,1000,1,200,200,Open, Advance ,100
9000,1000,2,200,0,Close, Payment,100
If I aggregate the values then query will take time to execute . How can I provide cumulative information like total_due at store level or customer level from this fact table? shall i go for one more fact table for Accumulative information ?
Please suggest.
Thanks,
Hesh.
hesh- Posts : 12
Join date : 2011-08-16
Re: Multiple granular levels for fact table
Create an aggregate table from the fact table.
Jeff Smith- Posts : 471
Join date : 2009-02-03
Re: Multiple granular levels for fact table
Add 3 columns, row_eff_start_date, row_eff_end_date, LastTransactionOfDay Flag.
LastFlag keeps from doublecounting balances when multiple transactions happen on a specific day.
You do need to revisit the previous row to set the end effective date when you insert a new row.
what was the balance at a point in time '8/1/2011' (works for today as well)?
LastFlag keeps from doublecounting balances when multiple transactions happen on a specific day.
- Code:
Cust, TrxDate, Amount, Balance, row_start, row_end, Lastflag
42, 8/1, 100, 100, 7/1, 7/4, Y
42, 8/5, 50, 150, 7/5, 7/20, Y
42, 8/21, -20, 130, 7/21, 7/21, N
42, 8/21, -20, 110, 7/21, 7/21, N
42, 8/21, -20, 90, 7/21, 7/21, N
42, 8/21, -20, 70, 7/21, 12/31/9999, Y
You do need to revisit the previous row to set the end effective date when you insert a new row.
what was the balance at a point in time '8/1/2011' (works for today as well)?
- Code:
select Balance, ... from FactTransaction
where '8/15/2011' between row_start and row_end
and LastFlag = 'Y'
and customerkey = 42
jgranden- Posts : 6
Join date : 2010-07-09
Re: Multiple granular levels for fact table
Jeff Smith wrote:Create an aggregate table from the fact table.
is this a regular practice in DW? I am new to this (experienced in OLTP), what I know is we should only refer facts table , if we do this what this aggregate table will be called? is there any technical name for this table?
Thanks,
Hesh.
hesh- Posts : 12
Join date : 2011-08-16
Re: Multiple granular levels for fact table
two kinds of aggregate tables: "periodic snapshot"
1 row per loan per time_period (month granularity is probably the most common). Each month you create 1 row per loan regardless of activity.
"accumulating snapshot"
1 row per loan. columns would be things like start_date, loan_orig_amount, last_payment_date, last_payment_amount, etc
(my prior example is transactional grain: 1 row per transaction)
1 row per loan per time_period (month granularity is probably the most common). Each month you create 1 row per loan regardless of activity.
"accumulating snapshot"
1 row per loan. columns would be things like start_date, loan_orig_amount, last_payment_date, last_payment_amount, etc
(my prior example is transactional grain: 1 row per transaction)
jgranden- Posts : 6
Join date : 2010-07-09
Re: Multiple granular levels for fact table
Never mix grains in a fact table. It's not a loaf of bread.
The typical design pattern for something like this is a transactional fact (no balance) and a periodic snapshot fact holding balance information.
The typical design pattern for something like this is a transactional fact (no balance) and a periodic snapshot fact holding balance information.
Re: Multiple granular levels for fact table
thanks for your valuable advice ngalemmo
what i understand is we need to create a trasactional fact table like
and one TRANSACTION DIMENSION like
Thanks,
Hesh.
what i understand is we need to create a trasactional fact table like
- Code:
CUSTOMER_KEY,LOAN_KEY,TIME_KEY,TRANSACTION_KEY
9000,1000,1,80001
9000,1000,2,80002
and one TRANSACTION DIMENSION like
- Code:
TRANSACTION_KEY,TRAN_ID,PRINCIPLE,FEE
8001,ADV,200,30, Advance ,100
8002,PAY,-200,-30, Payment,100
Thanks,
Hesh.
hesh- Posts : 12
Join date : 2011-08-16
Re: Multiple granular levels for fact table
You don't model it that way.
Facts represent events. In the case of a transactional fact for loans, those events would be disbursing or receiving funds against the loan. Events have measures. Dimensions are context for the event. A typical loan fact table would have dimensions such as date, customer, loan, transaction type. The measure on the fact would be the amount of the transaction (and maybe fees or other amount breakdowns as appropriate). You do not put measures in dimensions. In fact, you would not have a 'transaction' dimension.
The loan balance snapshot would have similar dimensions; date, customer, loan; and carry measures such as balance as of the given date.
Facts represent events. In the case of a transactional fact for loans, those events would be disbursing or receiving funds against the loan. Events have measures. Dimensions are context for the event. A typical loan fact table would have dimensions such as date, customer, loan, transaction type. The measure on the fact would be the amount of the transaction (and maybe fees or other amount breakdowns as appropriate). You do not put measures in dimensions. In fact, you would not have a 'transaction' dimension.
The loan balance snapshot would have similar dimensions; date, customer, loan; and carry measures such as balance as of the given date.
Re: Multiple granular levels for fact table
ngalemmo wrote:Never mix grains in a fact table. It's not a loaf of bread.
Nice! Very nice!
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Multiple granular levels for fact table
Hi Ngalemmo,
I have changed my design accordingly but one thing do we need to store loan balance snap shot periodically? or I just need to refresh daily? What would be the best practice/general practice ?
Thanks for your all valuable responses and make my thought process towards dimensional thinking,
-Hesh.
I have changed my design accordingly but one thing do we need to store loan balance snap shot periodically? or I just need to refresh daily? What would be the best practice/general practice ?
Thanks for your all valuable responses and make my thought process towards dimensional thinking,
-Hesh.
hesh- Posts : 12
Join date : 2011-08-16
Re: Multiple granular levels for fact table
A periodic snapshot may be more useful than updating a balance table on a daily basis. Discuss this with the business. They would help decide what the appropriate period should be. Typical choices are either month end, billing date, or due date.
Re: Multiple granular levels for fact table
May be I need to explain in more details...
This is Loan domain and I am trying to create an accumulative snap shot table at loan level with following columns
on 01-jan-2011 this snap shot would be like
on 03-Feb-2011 this snap shot would be like
Do I need to store only the current day snapshot of this information or I need to store this information for each business day(i.e. daily?)
What is the best practice for this?
Thanks,
Hesh.
This is Loan domain and I am trying to create an accumulative snap shot table at loan level with following columns
on 01-jan-2011 this snap shot would be like
- Code:
LOAN_KEY,LOAN_DATE,DEPOSIT_DATE,RETURN_DATE,CLEAR_DATE,WRITEOFF_DATE,LOAN_PAIDOFF_DATE,
LOAN_STATUS,CHECK_STAUS,
DUE_AMT,RTN_FEE,LATE_FEE
sample data
10001,01-jan-2011,null,null,null,null,null,
Open,Held,
2000,0,0
on 03-Feb-2011 this snap shot would be like
- Code:
LOAN_KEY,LOAN_DATE,DEPOSIT_DATE,RETURN_DATE,CLEAR_DATE,WRITEOFF_DATE,LOAN_PAIDOFF_DATE,
LOAN_STATUS,CHECK_STAUS,
DUE_AMT,RTN_FEE,LATE_FEE
sample data
10001,01-jan-2011,30-jan-2011,null,03-feb-2011,null,30-jan-2011,
Close,Clear,
0,0,0
Do I need to store only the current day snapshot of this information or I need to store this information for each business day(i.e. daily?)
What is the best practice for this?
Thanks,
Hesh.
hesh- Posts : 12
Join date : 2011-08-16
Similar topics
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» How to handle multiple aggregations for multiple KPIs in fact table
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» Multiple Fact Tables vs. Consolidated Fact Table
» Multiple fact types in one fact table
» How to handle multiple aggregations for multiple KPIs in fact table
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» Multiple Fact Tables vs. Consolidated Fact Table
» Multiple fact types in one fact table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum