How to handle semi-additive measures which are not time-dependent. Please look at below sample data
POLICY,Premium,PLAN,ISSDATE,ChangeI
P1,100,ENDOW,01-01-2004,C
P1,100,ENDOW,01-02-2004,C
P1,100,ENDOW,01-03-2004,C
P2,140,TERM,02-02-2004,C
P2,140,TERM,04-04-2004,C
P3,160,ENDOW,01-01-2004,C
P3,160,ENDOW,02-02-2004,C
P3,160,ENDOW,03-03-2004,C
P3,160,ENDOW,04-04-2004,C
P4,200,TERM,01-01-2004,C
P4,200,TERM,01-02-2004,C
P5,300,ENDOW,02-02-2004,C

The dimensions are Policy[Column 1], Plan[Column 3], Issue Date[Column 4], Change ID[Column 5]

The Measure is Current Premium [Column 2] which is at the policy level. For cubes analysing Policy Changes [These are financial and non-financial changes and do not always have an incremental premium associated to them]

When we are analysing by Policy, Plan or Issue Date dimension, we want the premium for each policy to be considered only once [For example, Premium for ENDOW should be 560]. How can we achieve this across all dimensions? Currently it is adding up all the premiums

Based on the sample data, each policy is associated with a single
premium, but with multiple changes. If just the policy/plan and
associated premium can be broken out as a separate SQL table or view,
then one solution is to combine 2 cubes in a virtual cube. The first
cube would only have the Policy and Plan dimensions, with the Premium
measure.

The second cube would have the change events, with the additional Issue
Date and Change ID dimensions. Presumably, this would only have a Count
measure to analyze changes.


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!