Pls help on data design

I am tring to build a large relational database as source for OLAP and it is running ok.

It is a financial transaction database accumulated with data of (1) Actuals in Jan 2005, Feb 05... to Mar 06

(2) Budgeted numbers in Jan 2005, Feb 05... to Mar 06

An example is like this:-

Table A - actuals

Date/Product/Sales value

Table B - Budget

Date/Product/Sales value

However, one last trouble is regarding the time dimension,.

What is the best way to assign the time value to each transaction so that I can:-

(1) Compare aggregated Jan 06 with Jan 05 easily ?

(2) Compare aggregated Feb 06 with Jan 06 easily ?

(3) Compare actual vs budget easily ?

For example, if date is 23 Jan 2006, should I make 2 more columns, month & year, ie assign values 1 and 2006 ?

Also, should I create 2 columns of value fields, "Actual" and "budget", or should I create one single data value, but added one more domension, "status" for example and asiign "Actual" or "budget" to each transaction ?

Help...

Assuming that you're using AS 2005, you can get some ideas by looking at the Adventure Works cube - Financial Reporting measure group (just 1 measure: Amount) and Scenario dimension (3 members: Actual, Budget and Forecast). Defining Time Intelligence permits various time-based comparisons, such as the ones you mentioned:

http://msdn2.microsoft.com/en-us/library/ms175440(SQL.90).aspx

>>

Defining Time Intelligence (SSAS)

The time intelligence enhancement is a cube enhancement that adds time calculations (or time views) to a selected hierarchy. This enhancement supports the following categories of calculations:

Period to date.
Period over period growth.
Moving averages.
Parallel period comparisons.