used in Analysis Services to create a dimension in a datastore. This
query tends to deadlock after about 10 days of running smoothly. Only
way to fix it is to reboot the box, I can recycle the services for a
quick fix but that usually only works for the next 1-2 times I call the
view.
This view is used to create a breakdown of the bill-to locations from
Continent-Global Region-Country-Sub Region-State/Province- City-Zip
Code
Yes, I know that sounds crazy, but it was a requirement.
So why would I get a deadlock on a SELECT Query? Is there a way to set
the Isolation level to Repeatable Read for a view?
Here is the view code:
CREATE View dbo.vwBillTo
as
-- US ZipCode
Select 'Parent'=z.City +' ('+ ISNULL(RTRIM(z. State_shrt), '0') +
cast(IsNull(z.U S_Region_wk,0) as varchar) + ')'
,z.Zipcode_WK as 'Child'
,z.ZipCode_WK as 'Child_ID'
From dbo.DIM_POSTAL_ CODES_US z
inner join dbo.FACT_SALES f
on z.ZipCode_WK=f. Bill_To
Where z.US_Region_wk IS NOT NULL
Group by z.City,z.ZipCod e_WK,US_Region_ wk, z.State_shrt
Union
--City
Select 'Parent'=z.Stat e_Long+' ('+cast(IsNull( z.US_Region_wk, 0) as
varchar)+')'
,z.City as 'Child'
,z.City + ' ('+ ISNULL(RTRIM(z. State_shrt), '0') +
cast(IsNull(z.U S_Region_wk,0) as varchar) + ')' as 'Child_ID'
From dbo.DIM_POSTAL_ CODES_US z
Where z.US_Region_wk IS NOT NULL
Group by z.State_Long,z. City,z.State_sh rt,z.US_Region_ wk
Union
-- Canada ZipCode
Select 'Parent'=z.City + ' ('+ ISNULL(RTRIM(z. province_shrt), '0') +
')'
,z.Zipcode_WK as 'Child'
,z.Zipcode_WK as 'Child_ID'
From dbo.DIM_POSTAL_ CODES_CAN z
inner join dbo.FACT_SALES f
on z.ZipCode_WK=f. Bill_To
Group by z.Province_Long ,z.ZipCode_WK, z.City, z.province_shrt
Union
--City
Select 'Parent'=z.Prov ince_Long
,z.City as 'Child'
,z.City+ ' ('+ ISNULL(RTRIM(z. province_shrt), '0') + ')' as
'Child_ID'
From dbo.DIM_POSTAL_ CODES_CAN z
inner join dbo.FACT_SALES f
on z.ZipCode_WK=f. Bill_To
Group by z.Province_Long ,z.ZipCode_WK, z.City, z.province_shrt
Union
-- Canada Province
Select 'CANADA'
,Province_Long
,Province_Long
From dbo.DIM_POSTAL_ CODES_CAN
Group by Province_Long
Union
-- Country
Select t.Region_NK
,c.Country_Name
,c.Country_Name
From dbo.DIM_COUNTRY c
Inner Join dbo.DIM_WORLD_R EGION t
On c.Region_WK=t.R egion_WK
Where c.Country_Name Is Not Null
Group by t.Region_NK, c.Country_Name
Union
-- SubRegion
Select c.Country_Name
,sr.US_Region_N ame
,sr.US_Region_N ame
From dbo.DIM_US_REGI ON sr
Inner Join dbo.DIM_COUNTRY c
On sr.Country_wk=c .Country_WK
Group by c.Country_Name, sr.US_Region_Na me
Union
--Region
Select sr.US_Region_Na me
,c.State_Long
,c.State_Long+' ('+cast(c.US_Re gion_wk as varchar)+')'
From dbo.DIM_US_REGI ON sr
Inner Join dbo.DIM_POSTAL_ CODES_US c
On sr.US_Region_WK =c.US_Region_WK
Group by sr.US_Region_Na me, c.State_Long,c. US_Region_wk
Union
-- Continent
Select Null
,Region_NK
,Region_NK
From dbo.DIM_WORLD_R EGION
Where Region_NK Is Not Null
