I work in the medical insurance industry. We typically look at trend costs on a per member per month (PMPM) basis as opposed to looking at it in total dollars, since membership levels are constantly changing. We want to know specific costs for the "average" member, so that a large company going out of business won't skew total dollars in a particular region. So we need to get total dollars divided by total members for a particular location/time.
The problem I'm running into in building an OLAP cube is that we classify claims by more variables than membership. For example, claims might be classified with the dimensions TIME, REGION, HOSPITAL, and PROCEDURE. But membership is only classified with dimensions TIME and REGION. So when I build a cube and drill down on TIME/REGION/HOSPITAL/PROCEDURE, I want to only include membership for TIME/REGION. Similarly, if we did a national review based on TIME/PROCEDURE, I'd want the membership (in the denominator of the PMPM metric) to be only based on TIME.
Ultimately I'd like my OLAP cube to have measurements such as PMPM, that are calculated using different membership totals in the denominator based on whether TIME and/or LOCATION is used, even though I'll drill down on more claims-related dimensions. (Right now my membership table is separate from the claims table, even though the names of the common dimension levels are the same.)
Is this possible? Any advice or references would be appreciated.
Thanks.
