Periodic snapshot or Transaction type of FI

Dear Experts,
I want to design a dimension model about General Ledger of FI, all of my requirements are based on "monthly" analysis, but we need to update data by daily, because the customer maybe change historical data. How do I call the Model? period snapshot or transaction type?
if I call it Transaction type, the question is my requirements, I don't need to see more detail by daily.
if I call it periodic snapshot, the question is I need to update data by daily & maybe change data of last month.

best regards,
Evans.

Hi,

if your all the requirements are based on monthly they you can have it as periodic snapshot.
You are saying "I need to update data by daily & maybe change data of last month",what is it you will be updating

• will it be fact information

• will it be a dimension related ( in case if you re using hierarchy based dimension).

• Do you need to store the history of updates ?

thanks

Himahshu

At the base level you would maintain a fact table at the journal transaction level with appropriate dimensions. These rows are usually aggregations (date, journal, account, transaction) of sub-ledger detail. You may then also build aggregates, such as snapshots, from this detailed data.

Sub ledger detail data is maintained in other facts (subject areas).

Dear Himahshu,
will it be fact information =>Yes

will it be a dimension related ( in case if you re using hierarchy based dimension).=>Yes, just like account hierarchy, cost center, profit center

Do you need to store the history of updates ? => yes, before closing, the user maybe update data of last month.

Dear Ngalemmo,
seems your idea is only solution, one question, if the source system already prepare monthly data for you, do you want to use it? of you will follow Kimball's idea use transaction and then aggregate it?

best regards,
Evans.

If you are bringing in the detail, then I would usually create the aggregate from the detailed fact table. It is usually the simpler thing to do. Loading another monthly table involves another ETL process, scheduling and more opportunity for something to go wrong.