Parent Child Dimension AS 2000-2005

Hi,

I have a parent child dimension in AS 2000. Some of the accounts belongs to more then one parent and I have solved that by using formula expression that points to the original account member. The source table for the dimension looks like this:

AccountID AccountName ParentID Formula

1, 'A1', 10, ''

2, 'A2', 10,''

3, 'A1', 20, '[Account].&[1]'

10, 'P1', 10, ''

20, 'P2', 20, ''

I have tried to do the same in AS 2005 but I can not get it to work, should this type of dimension be implemented in a different way in AS 2005?

Thanks, Christer

You can use many-to-many dimension relationships to model this is AS 2005.

I have an article on my blog on pretty much this exact scenario at http://geekswithblogs.net/darrengosbell/articles/57811.aspx

Marco at www.sqlbi.eu has a very comprehensive white paper on some of the ways in which many-to-many relationships can be used in AS 2005.

|||

Thanks, I think Marco's multiple groups scenario could work for me. But does that mean that I have to deal with two dimensions? In AS 2000 i could use one dimension with two levels Item and ItemGroup, in this scenario it looks like it will be two separate dimensions.. correct?

/Christer

|||I beleive the very same approach you used in AS2000 should work in AS2005 as well (although alternative approach with M-to-M is also worth considering). You need to specify CustomRollupColumn to point to the "Formula" column - and it should work fine. Please provide more details what exactly did you do and what happened.|||

Mosha is probably right - I think your situation may be slightly different to the one that I was trying to solve with m-to-m relationships. As he said, can you tell us a bit more about what you did and what did not work?

If you do go down the m-to-m track you do not need a second dimension, what you need is a measure group that contains the relationship between which records in your fact table map to which account records in your dimension table. Below is a very simplistic fact table and m-to-m fact table. You should be able to prototype this sort of thing with views or named queries before making any structural changes.

eg,

Fact Table
==========

AccountID Amount
1 $20
2 $25

M-to-M Fact Table
==================

AccountID MappedAccountID
1 1
1 3
2 2

|||

ok - here is more details:

factAccount table: Id, AccountID, Hours

1, 1, 100

dimAccount table: AccountID, AccountName, ParentID, Formula

1, A1, 10
2, A1, 20, ([Account].[Accounts].&[1], [Measures].[Hours])
10, P1, 10
20, P2, 20

The Account dimension based on the dim table above is configured as parent-child and have following settings on its attributes:

PARENTID

Usage = Parent

Name = Accounts

ACCOUNTID

Usage = Key

NameColumn = AccountName

CustomRollupColumn = Formula

FORMULA

Usage = Regular

When browsing the cube in a pivotable I get no values for member A1 under P2 ([Account].[Accounts&[2]):

Account, Hours

P1 - A1, 100

P2 - A1, (empty)

Any idea?

Thanks, Christer

|||

OK - I think I know what the problem is. You need to define CustomRollupColumn = Formula on the Parent attrbute, not on the Key attribute. Let me know if this solved your problem.

HTH,

Mosha (http://www.mosha.com/msolap)

|||

Yes, it solved my problem!

Thanks!