Multiple granular levels for fact table

View previous topic View next topic Go down

Multiple granular levels for fact table

Post  hesh on Tue Aug 16, 2011 9:36 am

My fact table has to incorporate both at Transaction level and Accumulative , my basic design for Transaction level is as follows

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

View user profile

Back to top Go down

Re: Multiple granular levels for fact table

Post  Jeff Smith on Tue Aug 16, 2011 11:44 am

Create an aggregate table from the fact table.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Multiple granular levels for fact table

Post  jgranden on Tue Aug 16, 2011 12:25 pm

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.

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

View user profile

Back to top Go down

Re: Multiple granular levels for fact table

Post  hesh on Tue Aug 16, 2011 1:06 pm

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

View user profile

Back to top Go down

Re: Multiple granular levels for fact table

Post  jgranden on Tue Aug 16, 2011 10:15 pm

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)

jgranden

Posts : 6
Join date : 2010-07-09

View user profile

Back to top Go down

Re: Multiple granular levels for fact table

Post  ngalemmo on Wed Aug 17, 2011 1:47 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Multiple granular levels for fact table

Post  hesh on Wed Aug 17, 2011 2:18 am

thanks for your valuable advice ngalemmo

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
but it looks like a replica of an OLTP system rather then OLAP?

Thanks,
Hesh.





hesh

Posts : 12
Join date : 2011-08-16

View user profile

Back to top Go down

Re: Multiple granular levels for fact table

Post  ngalemmo on Wed Aug 17, 2011 1:08 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Multiple granular levels for fact table

Post  BoxesAndLines on Thu Aug 18, 2011 9:36 am

ngalemmo wrote:Never mix grains in a fact table. It's not a loaf of bread.

Nice! Very nice!
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Multiple granular levels for fact table

Post  hesh on Wed Oct 19, 2011 2:30 am

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.



hesh

Posts : 12
Join date : 2011-08-16

View user profile

Back to top Go down

Re: Multiple granular levels for fact table

Post  ngalemmo on Wed Oct 19, 2011 10:40 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Multiple granular levels for fact table

Post  hesh on Wed Oct 19, 2011 11:18 am

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
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

View user profile

Back to top Go down

Re: Multiple granular levels for fact table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum