We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now


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

wpwebster asked
Medium Priority
Last Modified: 2016-02-13
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?
Watch Question

Top Expert 2006
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts



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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.