merv
Feb 7, 2018

Azure DW - Rolling Dates From A Static Calendar?

One of the requirements we most often come across when working on solutions involving Azure Analysis Services is for rolling date time values so that the end user can filter their data based on calendar-based sequences (e.g. rolling day, week, month or year sequences).

I have found that one of the simplest methods to achieve this is to combine a static calendar table with date dimensional values with SQL views to generate the rolling set of sequences. Users can then incorporate this information into case statements, pick lists, filtering or slicing of data, such as Day Sequence between -7 and -1 for a rolling 7 days, or Year Sequence = 0 for the current year.


I have created a couple of samples in SQL for creating a simple calendar table in Azure SQL Data Warehouse which is configured with a distribution type of replicate, so please feel free to use.


View SQL is


/*

Name - V D Date

Description - calendar view with functions for use in models


Use any additional SQL Date/Time functions as required in the view

The sample ones I have added are in Bold

*/


DROP VIEW [dbo].[V_D_Date]


SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO


CREATE VIEW [V_D_Date] AS

(

SELECT


[DateKey]

,[Date]

,[Day]

,[DaySuffix] as Day_Suffix

,[Weekday]

,[WeekDayName] as Weekday_Name

,[DayOfYear] as Day_Of_Year

,[WeekOfMonth] as Week_Of_Month

,[WeekOfYear] as Week_Of_Year

,[ISOWeekOfYear] as ISO_Week_Of_Year

,[Month]

,[MonthName] as Month_Name

,[Quarter]

,[QuarterName] as Quarter_Name

,[Year]

,[MMYYYY]

,[YYYYMM]

,[MonthYear] as Month_Year

,[FirstDayOfMonth]

,[LastDayOfMonth]

,[FirstDayOfQuarter]

,[LastDayOfQuarter]

,[FirstDayOfYear]

,[LastDayOfYear]

,[FirstDayOfNextMonth]

,[FirstDayOfNextYear]

,DATEDIFF (DAY,GETDATE(),[Date]) AS Day_Sequence

,DATEDIFF (WEEK,GETDATE(),[Date]) AS Week_Sequence

,DATEDIFF (MONTH,GETDATE(),[Date]) AS Month_Sequence

,DATEDIFF (YEAR,GETDATE(),[Date]) AS Year_Sequence

,DATEDIFF (q,GETDATE(),[Date]) AS Qtr_Sequence


FROM [dbo].[Calendar]

)


Calendar Creation SQL


/*


Creates a table called DBO.Calendar For numbers of years fro a stated start date.


Change the table name for 'CREATE TABLE' statement.


Change date range on First Line for declare statement.


*/


DECLARE @StartDate DATE = '20160101', @NumberOfYears INT = 5;

/*this is a holding table for intermediate calculations:*/


CREATE TABLE #dimdate

(

[date] DATE,

[day] tinyint,

[month] tinyint,

FirstOfMonth date,

[MonthName] varchar(12),

[week] tinyint,

[ISOweek] tinyint,

[DayOfWeek] tinyint,

[quarter] tinyint,

[year] smallint,

FirstOfYear date,

Style112 char(8),

Style101 char(10)

);


-- prevent set or regional settings from interfering with table

SET DATEFIRST 7;

SET DATEFORMAT mdy;

SET LANGUAGE US_ENGLISH;


DECLARE @CutoffDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate);


/*use catalogue views to generate as many rows as required for dates*/


INSERT #dimdate([date])

SELECT d

FROM

(

SELECT d = DATEADD(DAY, rn - 1, @StartDate)

FROM

(

SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate))

rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])

FROM sys.all_objects AS s1

CROSS JOIN sys.all_objects AS s2

ORDER BY s1.[object_id]

) AS x

) AS y;


UPDATE #DimDate


set

[day] = DATEPART(DAY, [date]),

[month] = DATEPART(MONTH, [date]),

FirstOfMonth = CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 0)),

[MonthName] = DATENAME(MONTH, [date]),

[week] = DATEPART(WEEK, [date]),

[ISOweek] = DATEPART(ISO_WEEK, [date]),

[DayOfWeek] = DATEPART(WEEKDAY, [date]),

[quarter] = DATEPART(QUARTER, [date]),

[year] = DATEPART(YEAR, [date]),

FirstOfYear = CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, [date]), 0)),

Style112 = CONVERT(CHAR(8), [date], 112),

Style101 = CONVERT(CHAR(10), [date], 101)

;


/* CHANGE OF TABLE NAME CAN BE MADE BELOW IF REQUIRED */


--DROP TABLE dbo.Calendar


CREATE TABLE dbo.Calendar


WITH

(

DISTRIBUTION = REPLICATE

)


AS


SELECT


DateKey = CONVERT(INT, Style112),

[Date] = [date],

[Day] = CONVERT(TINYINT, [day]),

DaySuffix = CONVERT(CHAR(2), CASE WHEN [day] / 10 = 1 THEN 'th' ELSE

CASE RIGHT([day], 1) WHEN '1' THEN 'st' WHEN '2' THEN 'nd'

WHEN '3' THEN 'rd' ELSE 'th' END END),

[Weekday] = CONVERT(TINYINT, [DayOfWeek]),

[WeekDayName] = CONVERT(VARCHAR(10), DATENAME(WEEKDAY, [date])),

[DayOfYear] = CONVERT(SMALLINT, DATEPART(DAYOFYEAR, [date])),

WeekOfMonth = CONVERT(TINYINT, DENSE_RANK() OVER (PARTITION BY [year], [month] ORDER BY [week])),

WeekOfYear = CONVERT(TINYINT, [week]),

ISOWeekOfYear = CONVERT(TINYINT, ISOWeek),

[Month] = CONVERT(TINYINT, [month]),

[MonthName] = CONVERT(VARCHAR(10), [MonthName]),

[Quarter] = CONVERT(TINYINT, [quarter]),

QuarterName = CONVERT(VARCHAR(6), CASE [quarter] WHEN 1 THEN 'First'

WHEN 2 THEN 'Second' WHEN 3 THEN 'Third' WHEN 4 THEN 'Fourth' END),

[Year] = [year],

MMYYYY = CONVERT(CHAR(6), LEFT(Style101, 2) + LEFT(Style112, 4)),

YYYYMM = CONVERT(CHAR(6), LEFT(Style112, 4) + LEFT(Style101, 2)),

MonthYear = CONVERT(CHAR(8), LEFT([MonthName], 3) + ' ' + LEFT(Style112, 4)),

FirstDayOfMonth = FirstOfMonth,

LastDayOfMonth = MAX([date]) OVER (PARTITION BY [year], [month]),

FirstDayOfQuarter = MIN([date]) OVER (PARTITION BY [year], [quarter]),

LastDayOfQuarter = MAX([date]) OVER (PARTITION BY [year], [quarter]),

FirstDayOfYear = FirstOfYear,

LastDayOfYear = MAX([date]) OVER (PARTITION BY [year]),

FirstDayOfNextMonth = DATEADD(MONTH, 1, FirstOfMonth),

FirstDayOfNextYear = DATEADD(YEAR, 1, FirstOfYear)

FROM #dimdate

;

DROP Table #dimdate;

0 comments0 replies
New Posts
  • Darren Fuller
    Jan 24, 2019

    Filtering DataFrames with other DataFrames in Spark

    Discussion 
    Something that comes up quite frequently when people start using Spark is "How can I filter my DataFrame using the contents of another DataFrame?". People with SQL experience will immediately look to trying to replicate the following. SELECT * FROM table_a a WHERE EXISTS (SELECT * FROM table_b b WHERE b.Id = a.Id) So how do you do this in Spark? Well, some people will try to use the Column.isin method which uses varargs, this is okay for a small set of values but if you have a couple of large DataFrames then it's less than optimal as each row needs to be evaluated against the list. So what's the other choice? We can use joins to do the same thing. There are 2 we can use, a SEMI JOIN which is equivalent to our above example of running EXISTS; the other is ANTI JOIN which is equivalent to a NOT EXISTS. Using the above example and keeping the table names as DataFrame names we could re-write this in Scala as: table_a.join(table_b, Seq("Id"), "left_semi") These 2 joins are unique in that they only return the output of the left DataFrame, without any content from the right DataFrame. So what does this look like in practice. Well using Azure Databricks we can quickly create some sample data to try them out. First lets create a couple of DataFrames. First lets runs a simple query to find heroes which have an arch-enemy. This uses the SEMI JOIN to keep records in the left DataFrame where there is a matching record in the right DataFrame. Now, lets have look for heroes who've been a little more active and have removed their arch-enemies (for now). This time we've used an ANTI JOIN to keep only those records in the left DataFrame where there are no matching records in the right DataFrame. You'll notice that in the examples the join condition uses the slightly longer form, that's because in this example the columns we're joining on have different names, and also because there is a column in both DataFrames which have the same name.
  • Darren Fuller
    Jan 7, 2019

    ARM and ARM Functions

    Discussion 
    Recently I needed to deploy an Azure Data Lake Store - Gen 2 instance and thought I'd take the opportunity to use some custom ARM template functions . These aren't something you often see in the example templates but can be really useful if there's a complex expression which you find yourself writing repeatedly within a template. If, for instance, you routinely create resource names based on a prefix, unique name and a suffix then this could save you a few keystrokes. In essence you are simply parameterizing the expression as follows: In this way you can use this simpler expression where you would have previously used the more complex version. [namespace.function(parameter1, parameter2)] If you want to see what this looks like in a full template then checkout this simple ARM template I put together for creating a Data Lake Store - Gen 2 instance over on GitHub.
  • Darren Fuller
    Dec 6, 2018

    Read the documentation for better performance

    Discussion 
    Documentation is not something people often spend time reading, or if they do then its to quickly find the one thing their after and then get out as quickly as possible, very similar to how I do my Christmas shopping. Sometimes it's worth spending time reading the documentation though as there can be some useful bits of information hidden in summary descriptions, links etc... One such item is the Azure Data Lake Store client. If you find yourself reading or writing a lot of files and your doing it in multiple tasks (or threads, but you should be using Tasks if possible), then reading the docs can really help you out. For instance this snippet taken from the description at the top of the documentation page . If an application wants to perform multi-threaded operations using this SDK it is highly recomended to set ServicePointManager.DefaultConnectionLimit to the number of threads application wants the sdk to use before creating any instance of AdlsClient. By default ServicePointManager.DefaultConnectionLimit is set to 2. Okay, so how bad can things be if you don't read this? Well, to answer that I created an ADLS instance and uploaded a number of small parquet files. Then wrote an application to read each file (using the excellent Parquet .NET ) and return the number of records in the file, each file is processed in it's own Task and each uses the same AdlsClient instance. The simple process being followed here is to get a list of files, call " ProcessPath " on each and then when all the files have been process output the results. The output of this initial version is as follows: It's not too bad, but with multiple tasks I would have expected it to be better. Looking at the documentation snippet above it suggests we need to change the ServicePointManager.DefaultConnectionLimit value, but what to? Well doing some digging around came across a suggestion from Microsoft Support which, for ASP.NET, is to limit the number of requests that can execute at the same time to 12 per CPU (or 12 per core). So let's give that a go and see what happens. The code change for this is pretty simple and we can use System.Environment to get the number of processors available. So does it make much of a difference? Well, yes, quite a lot of difference actually. I ran the code in both variations a few more times to check it wasn't intermittent networking issues, other processes on my laptop interfering etc... but no, it really does make that much of a difference. So next time you're working with multiple tasks sharing resources, maybe spend a bit of time reading the documentation to see if there's anything which can make a difference to your application.