DataWarehouse Star Join with role play.

Question

can someone tell me how to correctly write the tsql to do the join.

I have a fact table with one roll.

declare    @DevelopmentFact    TABLE
(
      designCountrySK    int
    , deployCountrySK int
)


insert into @DevelopmentFact
select 1, 2
go

The dimension Table @CountryDim list 2 different countries.

declare @CountryDim    TABLE
(
        SK    int    identity(1,1)
    , CountryName    Varchar(10)
)


insert into @CountryDim
select 'India'
union
select 'USA'

The next query will show the CountryName for the deployCountry.

select *
from @DevelopmentFact  f
join @CountryDim            d
on f.deployCountrySK    =    d.SK

How to have both the deployCountry and designCountry returned?

---

-- IBM has jobs for USA employee to work oversea (India) and get their wages.

Answer 1

Try
insert into @CountryDim (CountryName)
select 'India'
union
select 'USA'

select *
from @DevelopmentFact  f
join @CountryDim            d
on f.deployCountrySK    =    d.SK
or f.designCountrySK    =    d.SK

---

Many Thanks & Best Regards, Hua Min

Answer 2

thanks.  how can i have USA and India both on the same row and each for the correct column, USA for deployCountry and India for designCountry?

---

-- IBM has jobs for USA employee to work oversea (India) and get their wages.

Answer 3

If you select from @CountryDim, you can see India is with 1, USA with 2, right?

---

Many Thanks & Best Regards, Hua Min

Answer 4

thanks.  how can i have USA and India both on the same row and each for the correct column, USA for deployCountry and India for designCountry?

---

-- IBM has jobs for USA employee to work oversea (India) and get their wages.

Are you looking something like below: Here I have used two instances of CountryDim one for joining to DesignCountry and other for joining to DeployCountry.

SELECT	F.deployCountrySK, CDeploy.CountryName AS DeployCountry, F.designCountrySK, CDesign.CountryName AS DesignCountry 
FROM	@DevelopmentFact F
JOIN	@CountryDim CDeploy
ON		F.deployCountrySK	= CDeploy.SK
JOIN	@CountryDim CDesign
ON		F.designCountrySK	= CDesign.SK

Thanks!

Answer 5

Yes, Deepak.  thanks.

---

-- IBM has jobs for USA employee to work oversea (India) and get their wages.