group by indirect related dimension?

Hi,

I have 1 fact and 2 dimensions.

Customer Dimenstion
Account Dimenstion( - account related only 1 customer)
Customer montly summary fact ( grain - customer, month)

i want to get avg(score) of customer managed by branch without redundance.

Acccount
------------
acct_A cust#1 branch 'A'
acct_B cust#1 branch 'A'
acct_C cust#2 branch 'A'
------------

Fact
-------------
cust#1 2011/05 score50
cust#2 2011/05 score20
-------------

my report must be like this

branch avg(score)
---------------------
'A' 35 <= (50+20)/2
---------------------

if i join the account_dim and cust_montly_fact, the avg is not what i want

select branch_cd, sum(b.score)/count(b.cust) avg_score
from account_dim a, cust_month_fact b
where a.cust_no=b.cust_no
group by b.branch_cd

=> 50+50+20/3 ... i don't want this.

how could i get correct result?

Since your fact table doesn't contain the account dimension (going by your post), why are you using it in your query? It isn't related to the grain of the fact.

you mean I have to add account key in customer_monthly_summary_fact table?
but customer has many accounts. it will affect to the grain of this fact table.

If you need to analyse customer scores by attributes on your account dimension, then the fact table needs to include a key to the account dimension. One way, that wouldn't affect the grain, is to identify the main account per customer and put that surrogate key into the fact table.

Or, changing the grain of the table to include all accounts, you could allocate the customer score across all of his accounts, i.e. cust#1 would have two rows in the fact with scores on each row of 25 (or 20/30 or...). This solution wouldn't necessarily work if one customer can have multiple accounts spread across multiple branches and you would want to use the whole score in each branch's average.

The point is, at the moment, the grain of your fact table does not allow you to use the account dimension.

As others have said ... the grain of the fact determines the dimensions required.

When you declared the grain of the fact (Customer balances by month), this declaration established the need for a customer dimension.

Customer Dimension
-------------------
Cust#1 Branch 'A'
Cust#2 Branch 'A'

When this is joined with the fact you will get the desired result.

Hi,

Thank you for the reply. but still i couldn't find solutions.

custormer has many accounts from various banks.
we collect account information from many banks.
so branch code is an attribute of account, not customer.

and i need to get avg_score by any attributes of account, not only branch.
i cound't find biz rule to fact allocation for all of this account attributes.
i have to get that avg_score by product type, replayment type, deliquency period,.... and combination of these group condition

Do i have to get the right score with nested query? Is it impossible to meet this request with data modeling?

You need to rethink this following the dimensional modelling process.

The grain of your fact table is customer. Therefore you must have a customer dimension, containing those attributes that relate to the customer. At best this is a subset of customer attributes, but as you pointed out, it cannot be attributes with many values for a single customer.

If you wish to provide analysis by Account attributes then you need to :

1. Create a fact table at the account level grain
OR
2. Create a bridge table to with a weighting factor to distribute the score to the multiple accounts for that customer. Then you could calculate a weighted average.... how accurate or meaningful that is you will need to discuss with your business

I think you have problems with your model.
Nevertheless, here is the code to get the result you want:

;WITH CTE AS (
SELECT f.CustName
, Branch
, f.Score
, ROW_NUMBER() OVER (PARTITION BY a.CustName ORDER BY a.AccountName) AS rn
FROM #Fact f
JOIN #Account a
ON f.CustName = a.CustName
)
SELECT Branch
, SUM(Score)/COUNT(Score) AS Avg_Score
FROM CTE
WHERE rn = 1
GROUP BY Branch

You need the Common Table Expres​sion(you could use a sub-query), to get the first row for each customer.