Hi
As I'm a novice in the field of olap, I was wondering if anyone out
there could help me.
I have a fact table containing fx.

Account_Id, Date, Amount, Account_Type, Sum_From, Sum_To
1000, 01-10-05, 1,000.00, OPERATION,,
1100, 31-07-05, 1,200.50, OPERATION,,
1150, 30-06-05,1,890.50, OPERATION,,
1200,,SUM,1000,1199

In the case that the account_type is fx. SUM, there will be no amount
etc., but there will be information of which accounts the sum consist
of. In the above ex. I wan't a summation of the amount on accounts 1000
to 1199 for account_id = 1200. Of course other dimensions as well must
be handled (time dimension, department dimension etc.)

Hopefully some one out there might have a suggestion.

Thanks/BR
Jan

You are mixing a dimension and facts in the same table.

You should set up an account dimension table with at least the following
columns.

AccountLevel1, AccountLevel2, AccountType
1200, 1000, OPERATION
1200, 1100, OPERATION
1200, 1150, OPERATION

You would then set up a Date dimension table

Date, Month, Year
01-10-05, October, 2005

And the fact table just has the account, date and amount fields.

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1128543503.497598.299390 (AT) g14g2000cwa (DOT) googlegroups.com>,
jan.noergaard (AT) mail (DOT) dk says...

Hi Thanks for your reply.

If I have this setup, then how will I be able to make the summation, on
the Accounts that don't have any amounts. Do you have a sugestion. Fe.
on account 1200 I wan't to sum up the facttable from account 1000 to
1199. I can handle that with accountlevel_1, but f.e. account say 130
also might consist of summation of accounts 1000..1299. That would be a
typical setup in a ERP system (Axapta).

BR/ Jan


*** Sent via Developersdex http://www.developersdex.com ***

If I understand you correctly, you simply insert another level above
level 1.

Most GL's I have worked with have this sort of levelled structure where
detailed accounts roll up to higher levels of abstraction. These sort of
structures fit very well with OLAP.

Level_0, Level_1, Level_2
130, 1200, 1000
130, 1200, 1001
....
130, 1200, 1199
130, 1300, 1201
....
130, 1300, 1299


--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Hi, thanks for your answer
In this way, would I then be able to see in the follwing way in the
cube?

Dimension:
Date
Department
etc.

Account_ID Amount
1000 10,000.50
1100 1,000.00
1150 1,000.00
....
1200 12,000.50 (summation from 1000
to 1199)
1220 1,000.00
....
1300 13,000.50 (summation from 1000
to1299)
BR/Jan

Yes, it would work exactly like this.

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1129149108.493900.290110 (AT) g47g2000cwa (DOT) googlegroups.com>,
jannoergaard (AT) hotmail (DOT) com says...