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!
