February 27, 2012 at 2:23 pm
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
March 6, 2012 at 3:29 pm
not sure this is the greatest way to go about it, but we implement [FactTableName]Averages as a separate object "spinoff". It populates in the same fact table ETL pkg after the fact table successfully completes since it uses that data and allows us to change the granularity. This also keeps fact table + avgs tables synched - they either both succeed or they both fail.
You could do this in the cube only/instead by adding a query to the dsv. We materialize them to feed the reporting solutions as well as the cubes.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply