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.

pls help - doubt regarding OLAP cubes.

what is it that decides that a particular OLAP cube is small or large? Is it the number of tuples or the number of dimensions that are present in the cube?

Thank you.

Small or large - are subjective characteristics, what is large for some people is small for others. There are also multiple dimensions by which to judge the size:

1. Number of members in the key attribute of the dimension. I would say that below 1 million is small, from 1 to 10 million is medium and above 10 million is large

2. Total number of attributes in all dimensions. Below 1000 is small, above is large

3. Number of records in the fact table at measure group granularity (i.e. number of records which will remain in the cube, not the number of rows in the fact table which will get reduced due to aggregation/deduplication). Or from another angle, number of rows loaded into the cube every day. I would say that below 10 million rows a day is small, between 10 million to 100 million rows a day is medium, and above 100 million rows a day is large.

There are, of course interesting combinations of the above.

|||thanks a lot !!!|||

Thanks Mosha!!

Could you please shed some light on the "interesting combinations of the above" if you get some time or is it out of scope of this forum?
We are working on at our company on a shared farm service model where upon multiple ssas databases are hosted on a single server.

If a certain app group fits in the large/medium criteria then they have to budget for their own hardware and storage.
Any additional information will be very much appreciated.

Rgds

Hari

Please, HELP! OLAP Cube Locked

When I try to process a cube on olap I receive this error message:

Could not lock object (user xxx on computer yyy has locked 'xxxxxxxx'

I already tried to find an option to unlock the cube and I do a restart of SQL service.
I'm worryed about my job !!!! :eek:
Please, HELP ME !!!! :(Is this only when someone else are editing the cube?

Think you can programmatically determinde if a cube is locked (using dso)

LOL...Relax..you can't loose your job cuz of a restriction ms placed.|||Thx Niconel but I' solved today (on morning Italy time) this issue.
Simply, all the OLAP DB was locked.
This is a problem related to the memory management of my server.
When I try to process a larger DB I obtain that error and the solution is: restart OLAP service from the Cluster Admin Consolle.
When I writed that I' m worried about my job I not refer to the data.
I' was worried about my professional skill level !!! :D
Thank you for your reply!
Byez,

Juppa.