I have a cube that presents accounts receivable data. It contains 3 measures:

Measure 1: The sum of accounts that owe us money
Measure 2: The sum of accounts we owe money to
Measure 3: The Net (measure1 - measure2)

The underlying fact table contains detailed transactions with an "amount"
field which is positive when we charge an account and positive when the
account pays. I cannot rollup with simple sums. I need to first sum by
account and then slot the resulting sum as either Measure 1 or Measure 2 and
then sum for Measure 1 and sum for Measure 2.

Any ideas on how to do the two stage sum (first by account and then across
accounts)?

Thanks!


--
Bob Hodgman

Correction:

I should have said: "positive when we charge an account and *negative* when
the account pays."

rather than: "positive when we charge an account and positive when the
account pays."

No one has any ideas? Is there an MDX function that would do the job? Should
I change the fact table and do some additional calculations in the T-SQL?

Thanks!
--
Bob Hodgman


"Bob H." wrote:

Here's one possible solution I've come up with:

1. In fact table, create new columns called

pre_aggregated_Measure1
pre_aggregated_Measure2

2. Generate pre-aggregated rows in the fact table filling new columns with
the right amounts for the two pre_agg columns and zeroes for all other
numeric measures(big job).

3. Create custom rollup for the fields in question. Something like:

iif (not is leaf(this.currentmember), pre_aggregated_Measure1, Measure1)

This pushes all the heavy lifting into the load-fact-table OLTP proc. Is
this the best place for it?

Bob Hodgman


"Bob H." wrote:

Hi Bob,

It's not clear why a "two-stage sum" is required - if you could
illustrate with some sample data, that would be helpful.

In terms of Measure1 and Measure2, if you're using AS 2005 you could
create 2 Named Calculations for the fact table like:

[AmountToGet]: case when amount > 0 then amount else 0 end

[AmountToPay]: case when amount < 0 then -amount else 0 end

Then create Measure1 from [AmountToGet] and Measure2 from [AmountToPay],
both measures with "Sum" aggregation.

With AS 2000, the above expressions can be configured as the "Source
Column" property of the respective measure.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Deepak, thanks for the reply.

Here's what I was thinking on the summing (first sum by account and then sum
accounts):

Given this set of transactions:

Acct#1 01/02/2006 charge 2000.00
Acct#1 01/15/2006 charge 1500.00
Acct#2 01/05/2006 charge 100.00
Acct#2 01/29/2006 payment (300.00)
Acct#1 01/31/2006 payment (3000.00)

Acct#1 02/01/2006 charge 500.00
Acct#2 02/01/2006 charge 300.00
Acct#3 02/02/2006 charge 100.00
Acct#3 02/28/2006 payment (200.00)
Acct#1 02/28/2006 charge 500.00

Aggregations should produce:

Date they owe us we owe them
========= =========== ==========
1/2006 500.00 (200.00)
2/2006 1600.00 (100.00)


Basic question being asked here is: Of all the accounts that owe us money,
how much do they owe us? And of all the accounts where we owe them money, how
much do we owe them?

Note that the transactions are categorized in multiple ways (not reflected
in above sample data). For most aggregations, simple sum works correctly.



--
Bob Hodgman


"Deepak Puri" wrote:

Hi Bob,

I didn't get your arithmetic for the aggregation numbers, but did the
suggestions I made for Measure1 and Measure2 help? If the results from
these measures still aren't what you want, maybe you can provide a few
examples fo what they return, versus what you expect.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hi, Deepak!

If I understand correctly what you are suggesting for Measure 1 and Measure
2, it would result in (for 1/2006, for example):

Measure 1: 3600.00 = 2000.00 + 1500.00 + 100.00
Measure 2: (3300.00) = (3000.00) + (300.00)

These sums are not what is needed. What the application needs for these
figures is to first find the net for each account and then sum Measure 1 and
Measure 2 with these net figures.

So, for 1/2006:

Step #1 (get net for each account):

Acct #1 net for 1/2006: 500.00 = 2000.00 + 1500.00 + (3000.00)
Acct #2 net for 1/2006: (200.00) = 100.00 + (300.00)

Step #2 (sum positive numbers in Measure 1, sum negative numbers in Measure 2)

Measure 1: 500.00
Measure 2: (200.00)

The basic questions being asked here are:

Of all the accounts that owe us money, how much total do they owe us?
Of all the accounts where we owe them money, how much total do we owe them?

Thanks for your help!


--
Bob Hodgman


"Deepak Puri" wrote:

This is still an issue for me. Any ideas people have would be appreciated.

Thanks!
--
Bob Hodgman


"Bob H." wrote:

Hi, Deepak. This is still an issue for me. Let me know if any ideas for
possible solutions occur to you.

Thanks for your help!

--
Bob Hodgman


"Deepak Puri" wrote: