Advice on modeling actual versus worst / standard / best

View previous topic View next topic Go down

Advice on modeling actual versus worst / standard / best

Post  majerle9 on Thu Mar 29, 2012 7:15 am

I have some transactional data that I am want to model in a star schema and need to assign dollar values based on the attributes of the transaction. The dollar values are contained in another table and have low and high thresholds to check against transactional measures, e.g. Join transactions on some key attributes (Product, Site) and then whether the transactional measure fits within a low and high threshold. So for one combo of product and site, I have 3 rows of thresholds I can potentially match a measure to (0-10, 11-20, 21-9999). The 3 rows indicate worst, standard, and best performance respectively, and the associated dollar values reflect this. I need to do this for multiple transactional measures to assign cost for performance against these areas. The dollar values are multipliers for measures already in the transaction data.

In my fact table, I'll end up with performance_measure_1_value, performance_measure_2_value, performance_measure_3_value and so on, however, I am also wanting to allow the users to compare actual to the other potential values (worst, standard, best). Should I include measures for all the possible values in my fact (could end up being a very wide table), or only include the actuals and then have multiple role-playing FKs to a dim showing Product, Site, Performance Check Name, Worst_Performance_Value, Standard_Performance_Value, Best_Performance_Value? Or something else?

I've already created a lookup table in the DW to capture the performance multipliers to use to calculate actual value in m fact table and it is a SCD as my source is overwritten and I need to keep track of effective dates for multipliers and thresholds. I could potentially create a dim over this lookup tablethat pivots the values as columns as above?

Any thoughts/feedback on this greatly appreciated.

majerle9

Posts : 2
Join date : 2012-03-29

View user profile

Back to top Go down

Re: Advice on modeling actual versus worst / standard / best

Post  BoxesAndLines on Thu Mar 29, 2012 2:24 pm

Sounds like a banding dimension with multiple relationships to the fact table (roles). This works as long as the same bands apply to the same measurements. In other words, a value of 8 for metric 1 is falls into the same band as a value of 8 for metric 2. If the value bands are different for each metric then you'll need separate dimensions for the different metrics.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Advice on modeling actual versus worst / standard / best

Post  majerle9 on Thu Mar 29, 2012 4:37 pm

Thanks for that. I guess my main concern is how to allow users to look at actual versus possible values.

So Fact is something like:
DK1
DK2
DK3
Measure1
Measure2
Measure3
Performance1ActualMeasure (Measure1 * Performance Multiplier found by looking up other table)
Performance2ActualMeasure (Measure2 * Performance Multiplier)
Performance3ActualMeasure (Measure3 * Performance Multiplier)

I am wondering if I should then add
Performance1BestPossibleMeasure
Performance1WorstPossibleMeasure
etc. or structure this in another way.

FYI, the lookup table is in the structure of:
Attr1
Attr2
PerformanceName
PerformanceLevel
LowThreshold
HighThreshold
performance$Value


majerle9

Posts : 2
Join date : 2012-03-29

View user profile

Back to top Go down

Re: Advice on modeling actual versus worst / standard / best

Post  Sponsored content


Sponsored content


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