MDX equivalent to SQL subqueries with aggregation

Question

I'm new to MDX and trying to solve the following problem. Investigated calculated members, subselects, scope statements, etc but can't quite get it to do what I want.

Let's say I'm trying to come up with the MDX equivalent to the following SQL query:

SELECT SUM(netMarketValue) net, 
  SUM(CASE WHEN netMarketValue > 0 THEN netMarketValue ELSE 0 END) assets,
  SUM(CASE WHEN netMarketValue < 0 THEN netMarketValue ELSE 0 END) liabilities,
  SUM(ABS(netMarketValue)) gross
  someEntity1
FROM (
SELECT SUM(marketValue) netMarketValue, someEntity1, someEntity2
FROM <some set of tables>
GROUP BY someEntity1, someEntity2) t
GROUP BY someEntity1

In other words, I have an account ledger where I hide internal offsetting transactions (within someEntity2), then calculate assets & liabilities after aggregating them by someEntity2. Then I want to see the grand total of those assets & liabilities aggregated by the bigger entity, someEntity1.

In my MDX schema I'd presumably have a cube with dimensions for someEntity1 & someEntity2, and marketValue would be my fact table/measure. I suppose i could create another DSV that did what my subquery does (calculating net), and simply create a cube with that as my measure dimension, but I wonder if there is a better way. I'd rather not have 2 cubes (one for these net calculations and another to go to a lower level of granularity for other use cases), since it will be a lot of duplicate info in my database. These will be very large cubes.

Answer 1

I think you should leave the aggregation logic to the cube--that's what it does best.

In your case, I would create an account dimension, and then I would add Account Intelligence. However, this only works for the Enterprise edition of SQL Server (2005 and above).

If you happen to have the standard edition, the canonical way to do this is to use unary operators.

That's the way we used to do it with Sql Server 2000, and here you have a great example.

Answer 2

Thanks, however maybe i oversimplified my question. I have about 30 dimensions total. someEntity2 is the dimension by which i want to calculate net values. someEntity1 is one of the other dimensions totally unrelated to someEntity2, as such they can't be hierarchy levels within the same dimension.

This application isn't even really an account ledger--just conceptually similar, in a sense. The account intelligence wouldn't really work because I need a way to determine which members in my hypothetical someEntity2 correspond to assets & which correspond to liabilities. However, mine are subject to change at any time--I only determine if its an "asset" if the total marketValue (from the measure dimension) is positive.

Answer 3

I think what you want is not two cubes, but one cube with two fact tables (sometimes called a constellation schema). The question was written months ago so I won't elaborate more here unless someone asks for more info.