I am running into a little problem creating a GL cube
using the chart of accounts.

I have what looks like a typical parent child DIM table
with account id and parent id. However my fact table has
transactions which in some cases have an account id of a
parent account. In other words the sales fact , instead of
a leaf level account. Is this possible. When I process
the cube it blows up on me. ANy ideas?

-mike

It's possible to have non-leaf data in the fact table. You must edit the
Account dimension -- change the "Members With Data" property to something
other than "leaf members only".

tom @ the domain below
www.tomchester.net


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

Consider the following example
What you shuld see for the cube to work:

P-C AccountDim

A1 - P1
P1 - P1
A2 - P2
P2 - P1

This will result in

ALL
P1
-A1
--P2
-----A2

The anomaly you are facing is what if P1 comes in as a
member key(Account and not as a parent alone) in such a
case, you need to scrub the dimension data to accomplish
this. i.e.

Instead of the above sample design it as such
A1 - P11
P1 - P11
P11 - P11
A2 - P2
P2 - P1

As such your cube will recognize your Account with the
Parent code if it comes in as an Account Code.

The other way out is to actually fix your data, which in
more warehousing cases will not be possible.

Let me know if this helps.

Regards,
Geeta

Hi Michael,

Thanks for your post. Please check and see if the Employees dimension in
the HR cube in the FoodMart 2000 sample database meets your requirements.
If so, Analysis Services are able to provide this kind of functionality.

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.