Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Value Banding linked to a calculation

2 posters

Go down

Value Banding linked to a calculation Empty Value Banding linked to a calculation

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

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

http://blog.une.edu.au/robbi

Back to top Go down

Value Banding linked to a calculation Empty Re: Value Banding linked to a calculation

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

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

http://aginity.com

Back to top Go down

Back to top

- Similar topics

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