Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JeffCarey
Starting Member
1 Post |
 Posted - 2012-02-27 : 16:30:02
|
| Business scenario: We have sales OPPORTUNITIES, which have zero or more PRODUCTS associated with each opportunity. These represent an opportunity to sell a customer some products, and it's possible that early in the sales process we don't yet know any specific products (hence the zero products possibility). Each opportunity has an AGE (current date minus the opportunity create date). We have a fact table which has a grain of OPPORTUNITY-PRODUCT, that is, one row per product on an opportunity (and those opportunities with zero products actually have one row linked to a NULL product dimension member). The age of each OPPORTUNITY is a value represented on each OPPORTUNITY-PRODUCT row.Requirement: We have a cube based on this fact table. In that cube I need a measure of AVERAGE OPPORTUNITY AGE. Since the grain of the fact table is OPPORTUNITY-PRODUCT though, and not OPPORTUNITY, a simple average would weight opportunities with more products higher than opportunities with fewer products. I need the average age to be weighted so that each OPPORTUNITY has the same weight, so that for example, an opportunity with two products is factored into the average age once, as is an opportunity with just one product.I also need the average to work regardless of how the cube is sliced. For example, we may slice by PRODUCT LINE, so that an opportunity with multiple products falls under multiple product lines and should factor into the average opportunity age under both product lines.I've encountered several varieties of this same scenario and feel like it must be a fairly common requirement, but can't seem to find the right approach to solving it. Any assistance would be appreciated.Thanks,Jeff Carey |
|
|
|
|
|
|
|
How do I calculate the average of a measure at a h