SQL datawarehousing extend dimension with extra info

Question

I have a dimension core.DimAd which contains ad information. i.e.

PK AdKey  AdvertiserID  CampaignID 
1         Ad company 1  Campaign 1
2         Ad company 2  Campaign 2
3         Ad company 4  Campaign 3

I then want to create a new extension table like AdChannelExtension

PK ChannelExtensionKey  Adkey  Channel
   1                    1      Sales
   2                    1      Production
   3                    2      Sales

I then want to create a view

VWDimAd

which joins the two tables on ad key and use as a dimension in SSAS. When it combinees the rows however it's non unique and won't process due to duplicate keys. How do you deal with this scenario? I cannot alter the original table. Thanks!

Answer 1

Create a composite key in your view and use that in SSAS:

select cast(a.AdKey as nvarchar(20))
         + '-'
         + cast(e.ChannelExtensionKey as nvarchar(20)) as AdAdChannelCompKey
      ,a.AdvertiserID
      ,a.CampaignID
      ,e.Channel
from DimAd a
    inner join DimAdChannelExtension e
        on(a.AdKey = e.AdKey)