Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

Update Adventure Works with views

Posted by thomasivarssonmalmo on March 16, 2015

Here are two scripts to create two views in the Adventure Works DW database so that you can play around with current dates instead of having to go back several years.

First the date view:

Create View dbo.DatesPlus5Y

AS

(

Select

DATEADD(YEAR,5,FullDateAlternateKeY) as FulldateAlternateKey5Y,

DATEDIFF(MONTH,DATEADD(YEAR,5,FullDateAlternateKeY),GetDate()) as MonthSequence,

DateName(m,DATEADD(YEAR,5,FullDateAlternateKeY)) As MontName,

DatePart(m,DATEADD(YEAR,5,FullDateAlternateKeY)) As MontNbr,

Year(DATEADD(YEAR,5,FullDateAlternateKeY)) as CalendarYear,

Year(DATEADD(YEAR,5,FullDateAlternateKeY))*100 + Month(DATEADD(YEAR,5,FullDateAlternateKeY)) As YearMonth,

Year(DATEADD(YEAR,5,FullDateAlternateKeY))*100 + Datepart(qq,DATEADD(YEAR,5,FullDateAlternateKeY)) As YearQuarter,

Case When DATEADD(YEAR,5,FullDateAlternateKeY) = Convert(varchar(8),GetDate(),112) Then ‘Y’ Else ‘N’ End As CurrentDate,

Case When DATEDIFF(MONTH,DATEADD(YEAR,5,FullDateAlternateKeY),GetDate()) = 0 Then ‘Y’ Else ‘N’ End as CurrentMonth,

Case When DATEDIFF(MONTH,DATEADD(YEAR,5,FullDateAlternateKeY) ,GetDate())between 0 and 2 Then ‘Y’ Else ‘N’ End as CurrentThreeMonths,

Case When DATEDIFF(MONTH,DATEADD(YEAR,5,FullDateAlternateKeY) ,GetDate())between 0 and 5 Then ‘Y’ Else ‘N’ End as CurrentSixMonths,

Case When DATEDIFF(MONTH,DATEADD(YEAR,5,FullDateAlternateKeY) ,GetDate())between 0 and 11 Then ‘Y’ Else ‘N’ End as Current12Months,

Case When DATEDIFF(MONTH,DATEADD(YEAR,5,FullDateAlternateKeY) ,GetDate())between 0 and 23 Then ‘Y’ Else ‘N’ End as Current24Months,

Case When DATEDIFF(MONTH,DATEADD(YEAR,5,FullDateAlternateKeY) ,GetDate())between 11 and 23 Then ‘Y’ Else ‘N’ End as Current12MonthsLY

From [dbo].[DimDate])

 

And then a fact table view. Feel free to add the measures you need. I have added flags for current date, month , year and so on, to replace named sets that do not work in n Tabular and Power View DAXMD.

Create View dbo.FactInternetSalesUpdate

AS

(

Select

CustomerKey,

ProductKey,

DATEADD(YEAR,7,OrderDate) As OrderDate,

DATEADD(YEAR,7,ShipDate) As ShipDate,

DATEADD(YEAR,7,DueDate) As DueDate,

OrderQuantity

From [dbo].[FactInternetSales])

The MD cube or the Tabular model need a Daily process so that the current flags will be accurate. Thanks to MVP Peter Larsson.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: