My GL Cube has an Accounts DIM that uses a single table
that links accountid to parentid. Acutally the columns
are

accountid, parentid, accountname, categoryid

Now this is the problem that I am seeing. I'm using this
account table as a normal parent child relationship DIM.
But then I want to throw in another table
(AccountCategory). This other table can be looked at like
the next higher step of the current account DIM. It's
basically the top level categories of a balance statement.


It seems to be that since each record Account DIM is build
a relationship with the parentID and so on, then the
category table comes along and tries to restructure the
whole thing that way it is told by id/fk. It almost seems
like my Top most level AccountCategories should reside in
the account table but not allow any child levels to link
to it?

Any ideas would be great.

-mike

Hi Michael,

Thanks for your post. According to your description, I unfortunately do not
have a clear idea of your problem. If possible, please provide a clear
sample on the FoodMart 2000 sample database to describe the problem on your
side. Also, please provide the tree structure of the dimension you require.

In additional, based on my experience, it is impossible to add a column in
the dimension table to create a higher level outside a Parent-Child
Dimension. However you can recreate the parent-child relationship by
integrating the higher level. Is that what you want?

Thanks for using MSDN newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Ok,
I will try to explain the best I can. Using the Account
DIM of FoodMart - It is parent child DIM like mine. They
have data under Total Expense - General & Administration,
Information Systems, Marketing and Lease under the Total
Expenses. I have the same accounts basically, mine drill
down a bit farther but none the less it basically works
the same. However, we have no account called expenses for
the formetioned accounts to be children to. This is my
problem.

So, What I did was create a seperate table AccountCategory
which contains the following
Assests
Liabilities
Revenue
Expenses
COGS etc etc.

Now back to the Account DIM. The top most parents in this
DIM needs to related into one of the ares in the
AccountCategory table. So in my Accont DIM my General &
Administration, Information Systems, Marketing and Lease
does not know that they are children to the Expense Member.

Hope this clarifies things.

-mike

A parent/child dim can only be sourced from a single table or view. You
could use a view to join the tables, and use it to source the dimension.

tom @ the domain below
www.tomchester.net


"Mike Morse" <mike.morse (AT) micromo (DOT) com> wrote

Hi Mike,

Thanks for your feedback. I start to understand what you mean. You want to
add a higher level on the existed levels in the dimension. If I have
misunderstood, please feel free let me know.

In the Account dimension, we can see the data source table account in the
FoodMart 2000.mdb (always under D:\Program Files\Microsoft Analysis
Services\Samples). There are two columns (account_id and account_parent).
We can update the data in this table with these columns and process the
account dimension again to change the tree structure of the account
dimension.

Please feel free to post in the group if this solves your problem or if you
would like further assistance.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.