Invalid object name ‘dbo.vTargetMail’ in Adventure Works

when practicing Azure Machine Learning, Azure PowerBI or other DB based services, it is common to have a databased named Adventure Works as prerequisite in your SQL Server/Warehouse. However, even when following the tutorial https://azure.microsoft.com/en-us/documentation/articles/sql-data-warehouse-get-started-manually-load-samples/, you may still hit problem like “Invalid object name ‘dbo.vTargetMail'”, which indicates there was no such view(table) in the sample database for your practice.

this can cause much boring efforts in data exporting/troubleshooting/etc..

the reason for this is “the tutorial above is just to guide to prepare a lightweight version of Adventure Works, which doesn’t contain a whole view(table)”.

here is a great article to fix as workaround: http://oliviaklose.com/how-to-set-up-the-adventure-works-2014-warehouse-in-azure-sql-database/, it works by building whole Adventure Works in local SQL Server, then exporting all data to cloud, that is great, but it still need much time and efforts.

then from the lightweight database from https://azure.microsoft.com/en-us/documentation/articles/sql-data-warehouse-get-started-manually-load-samples/, can we create the missing views(tables) to fix all that matters?

Yes!

quick one is to execute the query below to recreate the views you need:
/****** Object:  View [dbo].[vDMPrep]    Script Date: 3/31/2016 5:58:08 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

— vDMPrep will be used as a data source by the other data mining views.
— Uses DW data at customer, product, day, etc. granularity and
— gets region, model, year, month, etc.
CREATE VIEW [dbo].[vDMPrep]
AS
SELECT
pc.[EnglishProductCategoryName]
,Coalesce(p.[ModelName], p.[EnglishProductName]) AS [Model]
,c.[CustomerKey]
,s.[SalesTerritoryGroup] AS [Region]
,CASE
WHEN Month(GetDate()) < Month(c.[BirthDate])
THEN DateDiff(yy,c.[BirthDate],GetDate()) – 1
WHEN Month(GetDate()) = Month(c.[BirthDate])
AND Day(GetDate()) < Day(c.[BirthDate])
THEN DateDiff(yy,c.[BirthDate],GetDate()) – 1
ELSE DateDiff(yy,c.[BirthDate],GetDate())
END AS [Age]
,CASE
WHEN c.[YearlyIncome] < 40000 THEN ‘Low’
WHEN c.[YearlyIncome] > 60000 THEN ‘High’
ELSE ‘Moderate’
END AS [IncomeGroup]
,d.[CalendarYear]
,d.[FiscalYear]
,d.[MonthNumberOfYear] AS [Month]
,f.[SalesOrderNumber] AS [OrderNumber]
,f.SalesOrderLineNumber AS LineNumber
,f.OrderQuantity AS Quantity
,f.ExtendedAmount AS Amount
FROM
[dbo].[FactInternetSales] f
INNER JOIN [dbo].[DimDate] d
ON f.[OrderDateKey] = d.[DateKey]
INNER JOIN [dbo].[DimProduct] p
ON f.[ProductKey] = p.[ProductKey]
INNER JOIN [dbo].[DimProductSubcategory] psc
ON p.[ProductSubcategoryKey] = psc.[ProductSubcategoryKey]
INNER JOIN [dbo].[DimProductCategory] pc
ON psc.[ProductCategoryKey] = pc.[ProductCategoryKey]
INNER JOIN [dbo].[DimCustomer] c
ON f.[CustomerKey] = c.[CustomerKey]
INNER JOIN [dbo].[DimGeography] g
ON c.[GeographyKey] = g.[GeographyKey]
INNER JOIN [dbo].[DimSalesTerritory] s
ON g.[SalesTerritoryKey] = s.[SalesTerritoryKey]
;
GO

 

then

/****** Object:  View [dbo].[vTargetMail]    Script Date: 3/31/2016 6:01:26 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
— vTargetMail supports targeted mailing data model
— Uses vDMPrep to determine if a customer buys a bike and joins to DimCustomer
CREATE VIEW [dbo].[vTargetMail]
AS
SELECT
c.[CustomerKey],
c.[GeographyKey],
c.[CustomerAlternateKey],
c.[Title],
c.[FirstName],
c.[MiddleName],
c.[LastName],
c.[NameStyle],
c.[BirthDate],
c.[MaritalStatus],
c.[Suffix],
c.[Gender],
c.[EmailAddress],
c.[YearlyIncome],
c.[TotalChildren],
c.[NumberChildrenAtHome],
c.[EnglishEducation],
c.[SpanishEducation],
c.[FrenchEducation],
c.[EnglishOccupation],
c.[SpanishOccupation],
c.[FrenchOccupation],
c.[HouseOwnerFlag],
c.[NumberCarsOwned],
c.[AddressLine1],
c.[AddressLine2],
c.[Phone],
c.[DateFirstPurchase],
c.[CommuteDistance],
x.[Region],
x.[Age],
CASE x.[Bikes]
WHEN 0 THEN 0
ELSE 1
END AS [BikeBuyer]
FROM
[dbo].[DimCustomer] c INNER JOIN (
SELECT
[CustomerKey]
,[Region]
,[Age]
,Sum(
CASE [EnglishProductCategoryName]
WHEN ‘Bikes’ THEN 1
ELSE 0
END) AS [Bikes]
FROM
[dbo].[vDMPrep]
GROUP BY
[CustomerKey]
,[Region]
,[Age]
) AS [x]
ON c.[CustomerKey] = x.[CustomerKey]
;
GO

if you need more views, you can get detailed query from package downloaded here: https://msftdbprodsamples.codeplex.com/releases/view/125550

 

Leave a Reply

Your email address will not be published. Required fields are marked *