General ledger and budgets

Hi,

As adviced I have a date dimension which has a day as a key i.e. '20151016'. The point is I have different types of financial fact tables. The budget table has a granularity of a month. As this is no the primary key of the date dimension, how should it be attached?

The incurred cost in the ledger has a primary keys based on a key base on the month it is to be booked in the subledger and a document number. So its key is the subledger and the document number. So the same question for this table. How to connect it to the date dimension?

Regards,

Nico Verschuren

Are you taking about a month (Gregorian) or a fiscal period?

Irrespective of that, it is generally better if you have a dimension for it. However it can also be done by using a specific date (first day of month, last day or month, 15th of the month) that represents the month and always use that date to locate the date dimension key. The problem with it is the model lacks clarity. You can resolve some ambiguity by using a different name for the FK in facts at a monthly grain (such as month_key (or fiscal_period_key) instead of date_key).

Thanks for your reply. I am working with a Georgian Calendar.

Maybe it is an idea to model the month as a shrunken dimension?

Regards,

Nico

Yes. Having a month dimension improves clarity in the model.

You can create flags in your date dimension to represent "Month" for specific group if their definition of "month different from each other. For example if accounting month starts on 5th of every month then create a column in your date dimension accounting_month_date_ind and set its value as 'Y' and rest of the dates have value 'N'.