Value Banding linked to a calculation

View previous topic View next topic Go down

Value Banding linked to a calculation

Post  robhale on Wed Oct 07, 2009 10:51 pm

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?
avatar
robhale

Posts : 10
Join date : 2009-02-03
Location : NSW, Australia

View user profile http://blog.une.edu.au/robbi

Back to top Go down

Re: Value Banding linked to a calculation

Post  ngalemmo on Thu Oct 08, 2009 11:27 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum