General Ledger model?

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.

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,

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