Facts summed up twice because of a many to many relationship - SOLVED

[Bark]

Hi gurus,

I have a problem that I think I need to fix in FWM as it is for a DMR going to be used in Query Studio.

I've got 2 tables, 1 for customers and 1 for accounts. 1 customer may have more than 1 account and 1 account might be owned by more than 1 customer. The accounts table contains the balance of the account.

I've then created a DMR with 2 dimensions (there are more, but let's stick to 2 for the sake of the explaination): Customers and Accounts.

When I use the model in QS, I drag the account, then the balance and the results are correct. Then I drag the customer and some of them get duplicated (which makes sense). I figured, if I create a calculated measure sum([Balance]) / count([customer_id]), it will give me the amount per account even if I drag the customer id. This works fine for the accounts but then the summary total, it does the same so it basically "averages" the balance of my whole book.

I have also tried to do "sum([balance])/count([customer_id] for [account_no]) but this messes up the detail as it always divides based on the custmers per account (which also makes sense).

Can any of you think of a way of getting the correct results? Even if the answer is to reshape the model, it does not matter as long as the numbers are right.

Attached the results I am getting with the first expression (sum/count).


Many thanks.

Regards,

Bark

[Bark]

Hi again,

I thought I solved it and I was going to post a happy reply with the solution but instead I am posting more unsuccessful attempts.

I tried creating a new measure "average([balance] for [account_no])" and then the aggregation to sum. It works fine as long as the [account_no] field is present. If I try to do a table with, let's say, [product] and [balance] it does the average "for [product]"!! I don't understand why...

I tried silly things like adding (arithmetically) the [account_no] * 0 to the expression to force Cognos to bring the field in the query but I get the same result, "average for product".

Any ideas?

Thanks and regards,

Bark

[RobsWalker68]

Hi Bark,

I wasn't sure from your post if the accounts table was the fact or whether it is the dimension.  In a scenario where i am facing a many-many relationship then I would consider the use of a bridging table with a weighting factor to produce the correct results.

Below is a link to an interesting discussion on the kimball site that might help.

http://kimballgroup.forumotion.net/t1356-weighting-factor-in-bridge-table

Kind Regards


Rob

[Bark]

Hi Rob,

Many thanks for the reply.

I cannot use a weighting factor because a customer might have several accounts so if the analysis is by customer, the account balance will be multiplied by the factor for joint accounts.

I'll keep posting my attempts to see if it brings more insight.

Thanks and regards,

Bark

[Bark]

Hi guys,

I finally solved it. I did the calculated measure with the "average for account_no" but this time before the DMR, in the Business Layer. I still don't know why when I did it in the DMR itself it was changing the agrument of the for to other columns but when I did it before the DMR, as a calculated dataItem for the Accounts table, it works.

Thanks and regards,

Bark

[Lynn]

Hi Bark,

I wasn't sure from your post if the accounts table was the fact or whether it is the dimension.  In a scenario where i am facing a many-many relationship then I would consider the use of a bridging table with a weighting factor to produce the correct results.

Below is a link to an interesting discussion on the kimball site that might help.

http://kimballgroup.forumotion.net/t1356-weighting-factor-in-bridge-table

Kind Regards


Rob

Rob,
As an interesting side note, did you know that Ralph Kimball is a huge fan of a new variety of cherries? I think they are called 'glacier cherries' ?? Have you ever tried these?
;D
Lynn

[MFGF]

Rob,
As an interesting side note, did you know that Ralph Kimball is a huge fan of a new variety of cherries? I think they are called 'glacier cherries' ?? Have you ever tried these?
;D
Lynn

Ah! I heard that too! A particular favourite of his after going "rain skating" - apparently he buys brogues with smooth leather soles, waits until it rains hard, then goes out "skating" on the wet pavements (sidewalks) :) He is an interesting man on many counts! ;D