General Ledger model?

View previous topic View next topic Go down

General Ledger model?

Post  inventivethinking on Tue Sep 23, 2014 4:30 pm

I am doing a model of GL accounts and stripped down schema looks like this

DIM_GL
-------
GL_Key
GL_AccountNo
GL_CatA_Percentage
GL_CatB_Percentage
GL_CatC_Percentage
GL_CatD_Percentage

DIM_FACT
---
GL_KEY
Date_Key
Debit_Credit_Indicator_Key
Journal_Entry_Number
Journal_Entry_Amount

The question is each GL account contributes a percentage to top level four categories A, B, C, D --- For example a $5000 entry to journal contribute 50% 0% 0% 50% to these categories. What is the best way to implement these percentages?

Should I create 4 columns in my fact table so it looks like

Fact
----
GL_KEY
Date_Key
Debit_Credit_Indicator_Key
Journal_Entry_Number
Journal_Entry_Amount
Journal_Entry_Cat_A_Amount
Journal_Entry_Cat_B_Amount
Journal_Entry_Cat_C_Amount
Journal_Entry_Cat_D_Amount

and populate a calculated value in that during ETL? or does this need to be part of some analysis services that populates some aggregates?

the business is looking for answers on summary level and not really on transaction level.

any help is appreciated.

inventivethinking

Posts : 3
Join date : 2014-08-19

View user profile

Back to top Go down

Re: General Ledger model?

Post  nick_white on Wed Sep 24, 2014 6:18 am

Hi - your design (4 amount fields on your fact table) is fine as long as the Categories are not going to change - you won't add more categories or change the use of one or more of the category amount fields.

If the categories can change then I would introduce Category as a Dimension (possible as a Degenerate Dimension) and have a single amount field on the fact.

Regards,

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Jakie ogumienie? proszę o radę

Post  watson45 on Tue Sep 30, 2014 11:25 pm

I am doing a model of GL accounts and stripped down schema looks like this

watson45

Posts : 1
Join date : 2014-09-30
Age : 37

View user profile

Back to top Go down

Re: General Ledger model?

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