Value Banding linked to a calculation
2 posters
Page 1 of 1
Value Banding linked to a calculation
I have an interesting problem - how to hook up value bands to fact rows that require a computation.
I have a fact table where the measure I'm interested in is a percentage. In order to cope with roll-ups I am storing the actual and the divisor as separate additive facts.
For example:
ROW1: M_ACTUAL = 10, M_DIVISOR = 40
ROW2: M_ACTUAL = 10, M_DIVISOR = 20
I can aggregate the above two rows and calculate a grouped percentage at report time (20/60 = 33%), or display a calculation at the grain of the fact table (25% and 50%).
I would now like to add a little value banding dimension to categorise the calculated percentage as Low, Medium, High. My problem is, I can't do this using a standard dimension because the associated value band will only be valid at the grain in which it is applied.
So, do I:
1) just 'know' only to use that value band dimension when reporting at a specific level?
2) join with an external value band group table where fact.actual/fact.divisor between vb_group.upper and vb_group.lower?
My problem with 2 is that I'm not sure I can model that cardinality in my metadata tool and also it doesn't seem to fit into a star schema design.
Any suggestions?
I have a fact table where the measure I'm interested in is a percentage. In order to cope with roll-ups I am storing the actual and the divisor as separate additive facts.
For example:
ROW1: M_ACTUAL = 10, M_DIVISOR = 40
ROW2: M_ACTUAL = 10, M_DIVISOR = 20
I can aggregate the above two rows and calculate a grouped percentage at report time (20/60 = 33%), or display a calculation at the grain of the fact table (25% and 50%).
I would now like to add a little value banding dimension to categorise the calculated percentage as Low, Medium, High. My problem is, I can't do this using a standard dimension because the associated value band will only be valid at the grain in which it is applied.
So, do I:
1) just 'know' only to use that value band dimension when reporting at a specific level?
2) join with an external value band group table where fact.actual/fact.divisor between vb_group.upper and vb_group.lower?
My problem with 2 is that I'm not sure I can model that cardinality in my metadata tool and also it doesn't seem to fit into a star schema design.
Any suggestions?
Re: Value Banding linked to a calculation
Typically its done using option 2. Value bands sit in its own table and applied in reporting. In many applications (such as age banding in health care) there are multiple sets of bands with a particular set applied depending on the analysis being done. So the band table would also contain an ID of some sort so the user can select which band to use.
Similar topics
» Value Banding
» Measures as Dimensions: Banding Approach Help
» inventory snapshot - GMROI calculation
» Inventory Snapshot - GMROI Calculation
» Measures as Dimensions: Banding Approach Help
» inventory snapshot - GMROI calculation
» Inventory Snapshot - GMROI Calculation
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum