Count Distinct on Dimensional attribute in context
Hi community,
I am wondering if it is possible to do a count distinct on a dimensional attribute and have that count be in context.
For example, take the following simplified model:
Two dimensions, Class of Business and Policy, where Policy is slowly changing (SCD2), so we could have multiple SKs for the same Policy Ref, and one fact table Premium.
Class of Business dimension
| COB_SK | CLASS_OF_BUSINESS |
| 1 | COB1 |
| 2 | COB2 |
| 3 | COB3 |
Policy dimension (note the 2 different POLICY_SK for POL1 due to a change in POLICY_ATT1 that we need to keep)
| POLICY_SK | POLICY_REF | POLICY_ATT1 |
| 1 | POL1 | A |
| 2 | POL1 | A' |
| 3 | POL2 | B |
| 4 | POL3 | C |
| 5 | POL4 | D |
Premium fact
| COB_SK | POLICY_SK | PREMIUM_AMT |
| 1 | 1 | 100 |
| 1 | 2 | 50 |
| 2 | 3 | 1000 |
| 2 | 4 | 1250 |
| 3 | 5 | 600 |
The generated query would look like this:
SELECT cob.class_of_business, SUM(fact.premium_amt) total_premium, count(distinct pol.policy_ref) policy_count
FROM premium_fact fact
JOIN class_of_business cob on fact.cob_sk = cob.cob_sk
JOIN policy pol on fact.policy_sk = pol.policy_sk
GROUP BY cob.class_of_business
Note the count distinct is on the dimension attribute POLICY_REF as I can't use the policy_sk in the fact table due to SCD2.
| CLASS_OF_BUSINESS | PREMIUM_AMT | POLICY_COUNT |
| COB1 | 150 | 1 |
| COB2 | 2250 | 2 |
| COB3 | 600 | 1 |
In this particular example I know I can redesign the model to have a policy dimension that doesn't have the SCD elements in it, i.e. 1 record per policy, and link the fact table to both dimensions, the SCD2 version and the non-SCD version, but I was just wondering if there is a way round this, or is my count distinct on the dimensional attribute just not the way it is done!
Many thanks for your help.
Malcolm