This is possibly a simple problem which I have yet to overcome.
Consider a cube based on clickstream data.
First, I have a fact table based on page views. That is One Row per page view on a site. Measures includes [Views], [Visits], [Bounce Rate] etc.
Secondly I have a measure group based on Leads. Measures include [Leads], [Revenue], [Margi]n etc.
One page can create multiple leads, therefore a one to many relationship exists. OFF of this Leads Fact I also have a Leads Dimension which describe the lead. An example attribute may be [Quality] = Good / Bad.
Now when browsing the cube I might want to see the number of [Views] or [visits] against the Lead Attribute, [Quality]. The problem in a one [View] to many [Leads] is that the [Views] are incorrectly multiplied for each lead each created.
e.g. One [View] created 3 [Leads]. [views] by [quality] = 3
I want Views by Quality to be DIVIDED and = 0.33 or, ideally, still 1. "1 View Created 2 bad and 1 Good Lead"
Would anyone have any ideas on how to solve this ?
THANK YOU !!!