I'd like to create a hierarchy in a dimension in Analysis Services 2005 which is subtractive rather than additive, can this be done?

I would like to create an account dimension in SSAS in which I have a simple hierarchy where some of the members are subtracted rather than added. For example Operating Income = Gross Margin - ExpenseGroup1 - Expense Group2....
Where Gross Margin is in turn an aggregate e.g. Gross Margin = Revenue - Cost of Sales.

I've looked at adding business intelligence to my dimension and defining "Account Intelligence".  It seems though that this is just a way of adding some pre-defined attributes such as Assets, Liabilities, Income, Expense... to the dimension.  It looks helpful in the way these defined attributes will aggregate over the time dimension e.g. Assets not adding over time but rather 2005 Assets would be the same as Dec 2005 assets.  It doesn't seem though that it has any effect on how the members are aggregated in the Account dimension.

Because of the number of the accounts and the subtractions (around 6000 members) it makes using the creating a calculated member by writing the MDX statement too cumbersome.  Isn't there a way to put this into a table?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Create a view in your database but populate -ve value for the cost of sales in the table. and create a cube using that view. I hope this will help.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wpwebsterAuthor Commented:

Thanks for the suggestion.  I'd thought about that but the constraint is the number of accounts that I would have to do that with is too great.  

After knocking my head against the wall all weekend I think I may have found that I can do this by creating a Parent/Child hierarchy rather than a regular hierarchy and then use the add intelligence function to "create custom member formula".  It looks like this should work.  I'm going to give it a try.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.