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.US_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.ZipCode_WK,US_Region_wk, z.State_shrt
Union
--City
Select 'Parent'=z.State_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.US_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_shrt,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.Province_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_REGION t
On c.Region_WK=t.Region_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_Name
,sr.US_Region_Name
From dbo.DIM_US_REGION sr
Inner Join dbo.DIM_COUNTRY c
On sr.Country_wk=c.Country_WK
Group by c.Country_Name, sr.US_Region_Name
Union
--Region
Select sr.US_Region_Name
,c.State_Long
,c.State_Long+' ('+cast(c.US_Region_wk as varchar)+')'
From dbo.DIM_US_REGION sr
Inner Join dbo.DIM_POSTAL_CODES_US c
On sr.US_Region_WK=c.US_Region_WK
Group by sr.US_Region_Name, c.State_Long,c.US_Region_wk
Union
-- Continent
Select Null
,Region_NK
,Region_NK
From dbo.DIM_WORLD_REGION
Where Region_NK Is Not Null
