SQL Date Dimension table data generator in PowerShell

It's common to have a date dimension table in databases that are used by applications that need to store information related to the financial/fiscal/holiday year of a business. Every business will likely have a different start date of the year and want to query data by week number, which would be different to the standard calendar week number. Additionally they might want to query against quarters & periods which differ from year to year depending on how they choose to break the year down in weeks.

Generating this data within T-SQL is challenging to say the least, it is getting easier in SQL Server 2012 with the new T-SQL functions. A much more flexible approach is to use PowerShell to do this, which is what I have done in the script below.

You'll need to set the $weeks variable to match the required breakdown of weeks in the year, this should add up to 52.

Set the $startDate to the 1st day of the year.

Set the $connectionString to your database and the destinationTableName to the table that contains the following columns:

• ID
• GIVEN_DATE(smalldatetime)
• WEEK_NO(int)
• CAL_WEEK_NO(int)
• DAY_ID (int)
• NON_WORK (bit)
• YEAR_NO(int)
• HOLIDAY_WEEK_No(int)
• PERIOD (int)
• QUARTER (int)

You can of course change the columns/data that you generate and store.

[void] [Reflection.Assembly]::LoadWithPartialName("Microsoft.VisualBasic")

$weeks = @(4,4,5,4,4,5,4,4,5,4,4,5)
$startDate = Get-Date "30/01/2011"
$yearNo = "1"
$connectionString = "Data Source=server;Initial Catalog=database;user=sa;password=password"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = "date_dimension"

$dataTable = DateDimension | Out-DataTable
$bulkCopy.WriteToServer($dataTable)

Function out-DataTable {
  $dt = new-object Data.datatable  
  $First = $true  

  foreach ($item in $input){  
    $DR = $DT.NewRow()  
    $Item.PsObject.get_properties() | foreach {  
      if ($first) {  
        $Col =  new-object Data.DataColumn  
        $Col.ColumnName = $_.Name.ToString()  
        $DT.Columns.Add($Col)       }  
      if ($_.value -eq $null) {  
        $DR.Item($_.Name) = "[empty]"  
      }  
      elseif ($_.IsArray) {  
        $DR.Item($_.Name) =[string]::Join($_.value ,";")  
      }  
      else {  
        $DR.Item($_.Name) = $_.value  
      }  
    }  
    $DT.Rows.Add($DR)  
    $First = $false  
  } 
  return @(,($dt))
}

function Get-WeekdayFlag($target) {
 if($target.DayOfWeek -eq 'saturday' -or $target.DayOfWeek -eq 'sunday') {$true}
 else {$false}
}

function Get-TimeKey ($target) { $target.ToString("yyyy-MM-dd hh:mm:ss") }

function DatePart($interval, $target) {
 [Microsoft.VisualBasic.DateAndTime]::DatePart(
  $interval,
  $target,
  [Microsoft.VisualBasic.FirstDayOfWeek]"System",
  [Microsoft.VisualBasic.FirstWeekOfYear]"System")
}

function DateDimension
{
    $weekNo = 1
    $dayNo = 0
    $count = 1
    $quarter = 1
    foreach($elem in $weeks)    
    {
        $max = $elem * 7
        $dayNo = 1        
        while($dayNo -le $max)
        {            
            New-Object PSObject |
                Add-Member -pass NoteProperty ID("1") |
                Add-Member -pass NoteProperty GIVEN_DATE($startDate) |
                Add-Member -pass NoteProperty WEEK_NO($weekNo) |
                Add-Member -pass NoteProperty CAL_WEEK_NO(DatePart "ww" $startDate) |
                Add-Member -pass NoteProperty DAY_ID([int]$startDate.DayOfWeek) |
                Add-Member -pass NoteProperty NON_WORK(Get-WeekdayFlag $startDate) |
                Add-Member -pass NoteProperty YEAR_NO($yearNo) |
                Add-Member -pass NoteProperty HOLIDAY_WEEK_No(0) |
                Add-Member -pass NoteProperty PERIOD($count) |    
                Add-Member -pass NoteProperty QUARTER ($quarter) 
            if($dayNo % 7 -eq 0) {$weekNo++}    
            $startDate = $startDate.AddDays(1)
            $dayNo++
        }    
        if($count % 3 -eq 0){$quarter++}
        $count++
    }
}