SQL Leap Year Time Dimension (Parallel Period)

Hi Guys,

Seen as the internet is so Unhelpful when it comes to working with leap years in SQL Server.

I’ve written a script that will allow you to do parallel period comparisons down to the day. So if today is Thursday and you want to compare your data to last year Thursday e.g. Sales. Then you will need to create a script that can’t just take you back a year, and add or remove a few days. You need to take into consideration that in every leap year there’s 53 weeks and an extra day. So just to save you all the hassle here’s the script. If you don’t know how to check for leap years, you can get the logic from the script too.

You guys might want to think about adding this to your Date Dimensions in the future if you’re creating Sales data marts.

/******************Parallel Peroid Comparison to day****************************/

/********This is if you want to compare today to last year what today is********/

-- Replace FullDate with your own Current date time in your Time Dimension; Format = GETDATE()

-- Replace DayOfYear with your own Current day of year in your Time Dimension; Format = DATENAME(DAYOFYEAR, FullDate)

SELECT *,CASE WHEN (YEAR(FullDate) % 4 <> 0) THEN 0 ELSE 1 END IsLeapYear

, CASE WHEN ((YEAR(FullDate) % 4 = 0) AND [DayOfYear] > 59) THEN DATENAME(WEEKDAY,DATEADD(dd, +2,DATEADD(yy,-1,FullDate)))

WHEN ((YEAR(FullDate) % 4 = 1) AND [DayOfYear] < 60) THEN DATENAME(WEEKDAY,DATEADD(dd, +2,DATEADD(yy,-1,FullDate)))

WHEN ((YEAR(FullDate) % 4 <> 0) AND [DayOfYear] > 59) THEN DATENAME(WEEKDAY,DATEADD(dd, 1,DATEADD(yy,-1,FullDate)))

WHEN ((YEAR(FullDate) % 4 <> 0) AND [DayOfYear] < 60) THEN DATENAME(WEEKDAY,DATEADD(dd, 1,DATEADD(yy,-1,FullDate))) ELSE DATENAME(WEEKDAY,DATEADD(dd, +1,DATEADD(yy,-1,FullDate))) END LeapYearDay

FROM DimDate

What irritates me is how simple it would be to do this in MDX.

ParallelPeriod( [ Level_Expression [ ,Index [ , Member_Expression ] ] ] )

SELECT ParallelPeriod ([Date].[Calendar].[Calendar Quarter]

   , 3

   , [Date].[Calendar].[Month].[October 2003])

   ON 0

   FROM [Adventure Works]

How does Hyper-Threading work and when to use it on Microsoft SQL Server

Hi Guys,

So the question is when to use Hyper-Threading on a SQL box, and what impact it has. To do this you first need to understand the CPU architectures, so to do this I compared the Intel Core i5 processors with the Intel i7's because the i7's have hyper-threading whereas the i5's don't. That's pretty much the only difference between the 2 if you didn't know.

Most people automatically assume that bigger is better, but welcome to the SQL world where software actually makes a difference, and can save a company millions on hardware costs if you just knew how to configure your software in the right way.

So let's start with the CPU. Fist off, download CPU-Z for yourself to get the information about your processor that you're running. http://www.cpuid.com/softwares/cpu-z.html, the link should be on the right-hand side.

So if you look at the above screen shots of my laptop, you'll see that I have 2 cores, and with hyper-threading enabled, it gives me 4 threads (Logical CPU's), if hyper-threading was disabled it would only give me 2 threads. When hyper-threading is enabled, and it increases your Threads, then it assigns some of your L3 cache to those threads, so if I have 4Mb of L3 Cache, it will distribute it among each of the threads, so every thread will get 1Mb of L3 Cache to use, but with hyper-threading disabled, it will give me 2 Threads, with 2Mb of Cache each.See my diagram below.

The reason why this is important to know is that when you compare the differences in OLTP (Online Transaction Processing) databases, and data warehouses / marts in the BI space, you want more threads to process your real-time incoming data by having Hyper-Threading enabled. BI systems require larger data volumes to be processed less frequently, so in that case, it would make sense to have less threads, but give those threads more L3 Cache to process the larger volumes, in which case it will make sense to disable Hyper-Threading, but only if you're doing less frequent loads, you will have to weigh it up and do some tests for yourself when it comes to Real-Time data warehouses.

Hope this will help to clear things up for people that are still debating about which one is better to use when you're developing or working with BI systems, or OLTP systems.

Kind Regards
Carel Greaves