I am having a problem with Analysis Services. I have a cube dealing
with financial data loaded from a General Ledger system. Therefore, I
have two dimensions--Account and Income Statement--that are each a
parent-child dimension with custom rollups and one measure--"amount".
The Account and Income Statement dimensions are both loaded into the
cube from one lookup table in the database. The lookup table contains
the folowing columns:

ID
Description
AccountID
ParentAccountID
AccountRollup
IncStmtID
ParentIncStmtID
IncStmtRollup

The parent-child relationships are ParentAccountID-AccountID and
ParentIncStmtID-IncStmtID. Description is used as the label for both
dimensions. Both of the dimensions have the same members at their
lowest levels, but as the rollups go higher the hierarchies change.

This lookup table joins to the fact table on the lookup table's ID
column. This ID is the text ID of the lowest-level accounts in the GL
system. (The other IDs are system-generated numbers.) So, I load
data into the cube at the lowest level, then customize how it rolls up
so that I can create, for instance, an income statement from the
Income Statement dimension. The Income Statement dimension breaks
down like the following (descrption and unary operator stored in the
database):

Net Income (null unary operator)
Operating Profit (+)
Gross Profit (+)
Revenue (+)
Revenue Type 1 (+)
Various Lowest Level Revenue Accounts(+)
Revenue Type 2 (+)
Various Lowest Level Revenue Accounts(+)
Cost of Revenue (-)
Cost of Revenue Type 1 (+)
Various Lowest Level Cost of Revenue Accounts(+)
Cost of Revenue Type 2 (+)
Various Lowest Level Cost of Revenue Accounts(+)
Operating Expenses (-)
Taxes (-)


The only amounts that are loaded into the cube are "Various Lowest
Level Revenue Accounts" and "Various Lowest Level Cost of Revenue
Accounts". All of the amounts are stored as positive numbers in the
database. However, when Analysis Services loads the cube, it loads
all of the amounts that roll up to a higher level that eventually has
(-) unary operator as a negative number! So it loads all "Various
Lowest Level Cost of Revenue Accounts" amounts as negative numbers,
then sums all of these expenses up to Cost of Revenue, and the result
is negative. But when it rolls Revenue and Cost of Revenue up to
Gross Profit, it applies the unary operator, so that it is now
subtracting the negative Cost of Revenue number--which means it is
actually ADDING Cost of Revenue to Revenue!

For example:
Gross Revenue = 5000
Cost of Revenue = -1000
-----------------------
Gross Profit = 5000 - (-1000) = 5000 + 1000 = 6000 == INCORRECT!!!

Should be:
Gross Revenue = 5000
Cost of Revenue = 1000
-----------------------
Gross Profit = 5000 - 1000 = 4000 == CORRECT!!!


How do I get Analysis Services to load the numbers correctly???

When I remove one of the parent-child dimensions, the numbers load
correctly (they are loaded as positive).

I tried to edit the Custom Rollup formulae as I saw recommended on
some other posts (though I am unsure whether that solution applies to
this), but this box is grayed out and I cannot access it.

I also tried breaking the lookup table into two separate tables, but
this did not fix the problem.

I have also tried changing the data type of the measure from double to
Big Integer and Unsigned Big Integer, and this does not do anything.

Final detail: we are using SP3a.

Please help--I have been searching for a solution to this for days now
and am at my wit's end...

Brad - unfortunately I don't know the solution to this as I am in fact experiencing the same situation
Interestingly I have a cube for a single business which is correct. It is in another cube which contains data for multiple business where I see the problem. So any solutions you come across pls send my way!

khodges_nosp (AT) m_csr (DOT) com.a

TIA.