February 2, 2007 at 7:09 am
I am somewhat new to analysis services and thought I could start away from MDX and just use Reporting Services tools but I ran into a problem.
I have to be able to calculate (valid clicks/count)/(valid impressions/count) = click through rate
I have a web site where we log impressions (results from searches with partial descriptions of web content) and clicks.
I built a fact table with cost of click which is 0 for impressions
FactId int,
WebId int,
ActTypeId int,
I have dimensions
Web Page (WebId int, URL varchar(255))
Activity Type (ActTypeId int, Name varchar(100))
Simple star schema right. I originally put 2 members ('Impression' and 'click') in Activity Type but then realized in my processing that I had to account for double clicks or refresh so I added two more activity types 'invalid impression' and 'invalid click'
Problem I am running into now is how would I calculate the click through rate impressions/clicks for a web page?
I thought I could just create
CREATE CALCULATED MEMBER [Members].[Valid Impressions] AS
'[Measures].[Fact Activity Count],[Dim Activity Type].[Activity Type].&[Valid Impression]'
and create valid clicks calculated member in the same way and divide them.
I am wondering If I should break the cube up and make one for impressions and clicks or add a column vaild click, valid impression and used linked cubes.
Any suggestions would be appreciated.
February 2, 2007 at 12:20 pm
Hey Derek,
Two things I thought you could try.
i) pivot your source data (using the activity) to give yourself four measures -> Click, Impression, Invalid Click and Invalid Impression. (it's implicit that the counts for Click and Impression are 'good' ones only). THen in your cube, create a calculated measure that is [Measures].[Click] / [Measures].[Impression] . You could also do the same for the invalid counts and/or any other combination (ie use invalid / (invalid + valid) to get an invalid click percent etc).
ii) leave the data as is and create a calculated member within the dimension that is [Activity].[Valid Click] / [Activity].[Impression] . I haven't run thsi thru a sample cube yet to test the validity but from memory when trying to create an artificial 'total' field, all we did was create a new calc member in the dimension thta was [member1] + [member 2] + [member n], although not looking for a total you are trying to perform simple math functions over members in the dimension.
Personally, i prefer option 1 but you might want to test both to see usability implications for your end user(s).
Cheers,
Steve.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply