Problem with parent-child dimension

I have to migrate OLAP database from AS2000 to AS2005. I have a problem with one of my dimension. This is how the situation looks like.

In AS2000 I have a parent child 'Users' dimension. Here is the structure of this dimension

Users

UniqueIdUser - Key

UniqueIdUserPrev - Parent

IDUser

UserName

The parent-chld relation is based on colums : UniqueIdUser and UniqueIdUserPrev. In AS2000 connection between this dim and the facts table is based on dim.IDUser - fact.IDUser. I know that this kind of connection gives me cartesian since dim.IDUser is not unique, but this is what I expected.

The question is : How can I do a parent child dimension in AS2005, where a parent child relation will be based on UniqueIdUser and UniqueIdUserPrev colums but connection to the fact table will be based on IDUser?

How about using a named query, which includes UniqueIdUser, for the fact table, like:

select fact.*, dim.UniqueIdUser

from fact

join dim on fact.IDUser = dim.IDUser

Corrsepondingly, the dimension table could now be a named query, like:

select distinct UniqueIdUser, UniqueIdUserPrev, UserName

from dim

|||

Hi

Thx for advice. Yes It will work, cube can be based on view like this. But my fact tables are quite big - something about 5-8 millions as well as dimUsers (about 20K). Inner join between this two tables will not work efficiently. There must be another solution. I have migrated cubes from AS2000 to AS2005 and AS2005 managed to convert this cube. But I can not do it manualy. AS2005 has created wired relation in 'Dimension Usage' between UniqueIDUser from 'Dimension Colums' and UniqueIDUser from 'Measure Group Colums' although there is no column UniqueIDUser in 'Measure Group Colums' (You can not choose it from drop down list)!!? If wizard could do this, I should mange to do this manualy also.

|||

Maybe you can inspect the migrated .cube XML file, to find out how the Granularity attribute is configured (there should be an element like: <Type>Granularity</Type>).

My understanding is that not all of the XML elements generated by the Migration Wizard can be manually recreated via the BIDS GUI. Of course, you could edit the .cube XML file directly, but then you'd have to understand it very well.