Database Reference
In-Depth Information
When 'popular_comp' Then N'Popular Computing'
When 'psychology' Then N'Psychology'
When 'trad_cook' Then N'Traditional Cooking'
When 'UNDECIDED' Then N'Undecided'
End
, [PublisherKey]=[DWPubsSales].[dbo].[DimPublishers].[PublisherKey]
, [TitlePrice]=Cast( isNull( [price], -1 ) as decimal(18, 4) )
, [PublishedDateKey]=isNull( [DWPubsSales].[dbo].[DimDates].[DateKey], -1 )
From [Pubs].[dbo].[Titles]
Join [DWPubsSales].[dbo].[DimPublishers]
On [Pubs].[dbo].[Titles].[pub_id]=[DWPubsSales].[dbo].[DimPublishers].[PublisherId]
Left Join [DWPubsSales].[dbo].[DimDates] -- The "Left" keeps dates not found in DimDates
On [Pubs].[dbo].[Titles].[pubdate]=[DWPubsSales].[dbo].[DimDates].[Date]
Go
-- 2g) Get source data from pubs.dbo.titleauthor and
-- insert into FactTitlesAuthors
Select
[TitleKey]=DimTitles.TitleKey
--, title_id
, [AuthorKey]=DimAuthors.AuthorKey
--, au_id
, [AuthorOrder]=au_ord
From pubs.dbo.titleauthor
JOIN DWPubsSales.dbo.DimTitles
On pubs.dbo.titleauthor.Title_id=DWPubsSales.dbo.DimTitles.TitleId
JOIN DWPubsSales.dbo.DimAuthors
On pubs.dbo.titleauthor.Au_id=DWPubsSales.dbo.DimAuthors.AuthorId
-- 2h)Get source data from pubs.dbo.Sales and
-- insert into FactSales
Select
[OrderNumber]=Cast( ord_num as nVarchar(50) )
, [OrderDateKey]=DateKey
--, title_id
, [TitleKey]=DimTitles.TitleKey
--, stor_id
, [StoreKey]=DimStores.StoreKey
, [SalesQuantity]=qty
From pubs.dbo.sales
JOIN DWPubsSales.dbo.DimDates
On pubs.dbo.sales.ord_date=DWPubsSales.dbo.DimDates.date
JOIN DWPubsSales.dbo.DimTitles
On pubs.dbo.sales.Title_id=DWPubsSales.dbo.DimTitles.TitleId
JOIN DWPubsSales.dbo.DimStores
On pubs.dbo.sales.Stor_id=DWPubsSales.dbo.DimStores.StoreId
-- Step 3) Add Foreign Key s back (Will be used with SSIS Execute SQL Tasks)
Alter Table [dbo].[DimTitles] With Check
Add Constraint [FK_DimTitles_DimPublishers]
Foreign Key ( [PublisherKey] ) References [dbo].[DimPublishers] ( [PublisherKey] )
