April 27, 2011 at 2:38 pm
I am looking to introduce into my data warehouse stats from google analytics and merge that into my main fact table that currently has data from our crm.
My sql skills are getting better for a self taught newbie, howe er im struggling with how to conceptually join data in this instance.
The current structure for my measures in my olap cube is based on distinct counts of interaction reference numbers in my fact table generated by the crm product. The google data are page views, so the aggregation would be sums not distinct counts. I am guessing that to join them I would need to somehow have an row per page view based on a concatenation of perhaps date,time,page and something else maybe ip address to form something to distinct count.
That's the problem thougg- how best to join 2 tables where one has values to distinct count and one has values to sum so that my cube has one measure for 'contacts'. Any ideas perhaps from people who have had a similar problem?
April 28, 2011 at 1:20 am
To update : The issue is I don't want to lose the unique reference numbers from the table as I also want to be able to drill down directly to the reference number through reporting. So it has to a way of somehow moving a model that uses a standard sum aggregation to a model that uses a distinct count.
In my head I think that there must be a way of processing the data so for example it might convert a row that looks like this...
Date, PageID, Pageviews
01/02/03, ArticleID=332, 3
To look something like
Date, PageID, PageviewID
01/02/03, ArticleID=332, 010203_332_1
01/02/03, ArticleID=332, 010203_332_2
01/02/03, ArticleID=332, 010203_332_3
So that PageviewID becomes the distinct count measure. Not entirely sure how I would achieve that, or if that's the right way of approaching it.
Your advice, as always, is appreciated.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply