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
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?
Question by:wpwebster
LVL 28

Accepted Solution

imran_fast earned 1000 total points
ID: 16292253
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

ID: 16300911

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

850 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