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

Posted on 2006-03-25
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?
Question by:wpwebster
    LVL 28

    Accepted Solution

    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.

    Author Comment


    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.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how the fundamental information of how to create a table.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now