MS SQL Data Warehousing - SQL to denormalize data

Author

SQL to denormalize data

Dave

2005-11-29, 8:23 pm

Guys I am trying to demoralize the source column in one of my tables so

I can identify the combination of sources that the email was recruited
from.

I know I can do this with a while loop (or cursor) but I would really
prefer taking a
set based approach.

Also, please feel free to offer any ideas on a better way to model
this.

I would like to know if we recruited an email on web site A, web site
B, or web site A and B.

My data table looks something like #data_table and I want my result set

to look something like #hybrid_table.

Can anyone offer some advice on how to approach this problem?

Thanks for you suggestions!!!

--drop table #data_table
create table #data_table (email_id int,src_id int)
--raw data
insert into #data_table select 1,5
insert into #data_table select 1,6
insert into #data_table select 1,7

insert into #data_table select 2,5
insert into #data_table select 2,6
insert into #data_table select 2,7

insert into #data_table select 3,5
insert into #data_table select 3,6
insert into #data_table select 3,7

insert into #data_table select 4,5
insert into #data_table select 4,6

insert into #data_table select 5,5
insert into #data_table select 5,9
insert into #data_table select 5,4
insert into #data_table select 5,20

insert into #data_table select 6,20
insert into #data_table select 6,5
insert into #data_table select 6,9
insert into #data_table select 6,4

--DROP TABLE #hybrid_table
create table #hybrid_table (hybrid_id int identity(1,1),hybrid
_name
varchar(50))
insert into #hybrid_table (hybrid_name) SELECT '5,6,7'
insert into #hybrid_table (hybrid_name) SELECT '5,6'
insert into #hybrid_table (hybrid_name) SELECT '4,5,9,20'

select * from #data_table order by email_id,src_id
select * from #hybrid_table

Adam Machanic

2005-11-29, 8:23 pm

"Dave" <daveg.01@gmail.com> wrote in message
news:1133303678.372420.276920@z14g2000cwz.googlegroups.com...
> Guys I am trying to demoralize the source column in one of my tables so
> I can identify the combination of sources that the email was recruited
> from.

Can you explain why denormalizing would help you make that
determination?

--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--

daveg.01@gmail.com

2005-11-30, 1:23 pm

I guess that is what I am asking too.

I have one email fact. I need to know what site I recruited this email
from. It was recruited from 2 sites. I want to give both credit since
both are good sources for recruiting emails.

How can you model this?

I think it is best to create a hybrid source dimension.

Source Dimension
Category Site Cost Per Email
Partner A Site 1 $1
Partner A Site 2 $10000

daveg.01@gmail.com

2005-12-06, 9:23 am

Just wanted to bump this.

Does anyone have any suggestions on how to model this scenerio?

How about someing like below?
Source Dimension
Category Site Cost Per Email
Partner A Site 1 $1
Partner A Site 2 $10000
Hybrid Site 1,2 $10001

Sponsored Links