Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Multiple granular levels for fact table

5 posters

Go down

 Multiple granular levels for fact table   Empty Multiple granular levels for fact table

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

Back to top Go down

 Multiple granular levels for fact table   Empty Re: Multiple granular levels for fact table

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

Create an aggregate table from the fact table.

Jeff Smith

Posts : 471
Join date : 2009-02-03

Back to top Go down

 Multiple granular levels for fact table   Empty Re: Multiple granular levels for fact table

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

Back to top Go down

 Multiple granular levels for fact table   Empty Re: Multiple granular levels for fact table

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

Back to top Go down

 Multiple granular levels for fact table   Empty Re: Multiple granular levels for fact table

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

Back to top Go down

 Multiple granular levels for fact table   Empty Re: Multiple granular levels for fact table

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

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

http://aginity.com

Back to top Go down

 Multiple granular levels for fact table   Empty Re: Multiple granular levels for fact table

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

Back to top Go down

 Multiple granular levels for fact table   Empty Re: Multiple granular levels for fact table

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

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

http://aginity.com

Back to top Go down

 Multiple granular levels for fact table   Empty Re: Multiple granular levels for fact table

Post  BoxesAndLines 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!
BoxesAndLines
BoxesAndLines

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

Back to top Go down

 Multiple granular levels for fact table   Empty Re: Multiple granular levels for fact table

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

Back to top Go down

 Multiple granular levels for fact table   Empty Re: Multiple granular levels for fact table

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

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

http://aginity.com

Back to top Go down

 Multiple granular levels for fact table   Empty Re: Multiple granular levels for fact table

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

Back to top Go down

 Multiple granular levels for fact table   Empty Re: Multiple granular levels for fact table

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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