General Ledger Fact Table: Dimensional Model

View previous topic View next topic Go down

General Ledger Fact Table: Dimensional Model

Post  jldosil on Tue Jul 10, 2012 8:42 am

Hello:

I need some help on dimensional modeling.

We have a transactional fact table that contains the general ledger account movements of a big enterprise. The size of the table is 350.000.000 rows.

ASSET;MOVEMENT;ACCOUNT;ACCOUNT_DESC;DEBIT/CREDIT;AMOUNT
1;1;6000;SALES;D;1.000 €
1;2;4720;VAT;D; 180 €
1;3;4000;PROVIDER;H;-1.180 €

The users ask us to have reports like this:

1. Filter the movements of an account, for example the account 6000:
1;1;6000;SALES;D;1.000 €

2. Determine the amount filtered in base of the counterpart movements of the asset, i.e, the rest of the movements of the asset, in the example:

ASSET;MOVEMENT;ACCOUNT;ACCOUNT_DESC;DEBIT/CREDIT;AMOUNT;COUNTERPART_ACCOUNT;COUNTERPART_ACCOUNT_DESC;COUNTERPART_DEBIT/CREDIT;COUNTERPART_AMOUNT
1;1;6000;SALES;D; 1.000 € ;4720;VAT;D; 180 €
1;1;6000;SALES;D; 1.000 € ;4000;PROVIDER;H;-1.180 €

¿How do you do that with a good perfomance?

The ways we think are:

1. Use a view, but the number of rows is very high.
2. Use the fact table like itself, and like a role of itself, but the perfomance is not good.

Thanks a lot.

Jose Dosil



jldosil

Posts : 3
Join date : 2011-09-19

View user profile

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