Time Dimension

Time Dimension creation script

DDL for a time dimension table.

Create table DimTime
(
	time_key smallint NOT NULL,
	time_type varchar(20) NOT NULL,
	full_time time(0) NULL,
	hour_of_day tinyint NULL,
	minute_of_hour tinyint NULL,
	hourly_segments varchar(13) NULL,
	minute_15_segment_key tinyint NULL,
	minute_15_segments varchar(13) NULL,
	minute_30_segment_key tinyint NULL,
	minute_30_segments varchar(13) NULL,
	 CONSTRAINT [PK_DimTime] PRIMARY KEY CLUSTERED 	 (time_key)
)
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Time dimension table with grain = 1 munute.',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'DimTime',
    @level2type = NULL,
    @level2name = NULL
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Minute of day (0 - 1439).  Special values (e.g. UNKNOWN) are negative. Primary Key.',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'DimTime',
    @level2type = N'COLUMN',
    @level2name = N'time_key'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Normal = minute of day; UNKNOWN, NOT SPECIFIED, INVALID)',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'DimTime',
    @level2type = N'COLUMN',
    @level2name = N'time_type'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Full time of day: 00:00 to 23:59.',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'DimTime',
    @level2type = N'COLUMN',
    @level2name = N'full_time'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Hour of day: 0 to 23.',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'DimTime',
    @level2type = N'COLUMN',
    @level2name = N'hour_of_day'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Minute of hour: 1 to 59.',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'DimTime',
    @level2type = N'COLUMN',
    @level2name = N'minute_of_hour'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Label for hourly segment. e.g. "10:00-10:59".',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'DimTime',
    @level2type = N'COLUMN',
    @level2name = N'hourly_segments'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Key (and sort order) for each 15-minute segment of day: 0 to 95.',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'DimTime',
    @level2type = N'COLUMN',
    @level2name = N'minute_15_segment_key'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Label for 15-minute segment. e.g. "09:15-09:29".',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'DimTime',
    @level2type = N'COLUMN',
    @level2name = N'minute_15_segments'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Key (and sort order) for each 30-minute segment of day: 0 - 47.',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'DimTime',
    @level2type = N'COLUMN',
    @level2name = N'minute_30_segment_key'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'Label for 30-minute segment. e.g. "11:30-11:59"',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'DimTime',
    @level2type = N'COLUMN',
    @level2name = N'minute_30_segments'

 Table Load script

Populates table with minutes for one day.  Includes special-meaning rows (-1 to -3)

-- Populate DimTime table.
-- Special Values
INSERT INTO DimTime (time_key, time_type)
	VALUES
		(-3, 'INVALID'),
		(-2, 'UNASSIGNED'),
		(-1, 'UNKNOWN')
-- minutes 0 - 1439
DECLARE @i int = 0
WHILE (@i < 1440)
BEGIN
	INSERT INTO dbo.DimTime
		VALUES
			(@i
			,'Normal'
			,DATEADD(minute, @i, '00:00')
			,DATEPART(hour, DATEADD(minute, @i, '00:00'))
			,DATEPART(minute, DATEADD(minute, @i, '00:00'))
			,CAST(CAST(DATEADD(minute, @i, '00:00') AS time) AS char(2)) + ':00-' +
				CAST(CAST(DATEADD(minute, @i, '00:00') AS time) AS char(2)) + ':59'
			,@i / 15
			,NULL
			,@i / 30
			,NULL
			)
	SET @i += 1
END

-- 15 minute buckets
UPDATE DimTime SET minute_15_segments =
	CAST(CAST(DATEADD(minute, (minute_15_segment_key * 15), '00:00') AS time) AS char(5)) + '-' +
		CAST(CAST(DATEADD(minute, 14 + (minute_15_segment_key * 15), '00:00') AS time) AS char(5)) 
-- 30 minute buckets
UPDATE DimTime SET minute_30_segments =
	CAST(CAST(DATEADD(minute, (minute_30_segment_key * 30), '00:00') AS time) AS char(5)) + '-' +
		CAST(CAST(DATEADD(minute, 29 + (minute_30_segment_key * 30), '00:00') AS time) AS char(5)) 

 

Add time_key to Existing Table

The key for DimTime, time_key, is meaningful, being the minute of the day (0 to 1439).   That makes a computed column acceptable when joining the date dimension to existing tables.

 

-- Add a computed time_key column.
ALTER TABLE  [table-name]
ADD time_key AS CAST(DATEDIFF(minute, CAST(n_DateStamp AS DATE), n_DateStamp) AS smallint)

Monitoring SSRS Reports and Subscriptions

Interpreting data in ReportServer database

Observations from SSRS 2008 R2

Subscriptions

table dbo.Subscriptions.

InactiveFlags

There’s no way to disable a subscription from SSRS UI though articles suggest setting InactiveFlags to 1 will achieve this.

  • 0 : schedule is active.
  • 8 : “One or more report parameters have an invalid parameter value.” (text displaed in subscription editor)
  • 16 : “An unkown parameter was found.” (text displayed in subscription editor).  i.e. a parameter name was set that no longer exists in the report specification.

Schedules

table dbo.Schedule

Column Description
 DaysOfWeek  Bitmap.
Sun = 1 … Sat = 64
All days = 127
 WeeksInterval  1 for Daily
 Month Bitmap for months
 MonthlyWeek  nth week of month
or Null
 Recurrence Type 1 : Once
2 : Hourly
3 : [not yet achieved]
4 : Day / Week
6 : Month

Re-triggering Scheduled Reports

This is the code that appears in the report-initiating Job.

EXEC ReportServer.dbo.AddEvent@EventType=‘TimedSubscription’,@EventData=‘<SubscriptionID>’

Values for EventType:

  • Timed Subscription : individually scheduled subscription for  a single report.
  • SharedSchedule : this job triggers a shared schedule.

Performance Monitoring using typeperf.exe

Introduction

Avoid all use of the Performance Monitor interface by using typeperf.exe (the oddest of names).

Implementation

The Baselining articles above are thorough.  These are notes on my implementation.

The aim of the articles is to log to a database and then run reports from the database.  A change to the -o switch will log the output to a file instead.

Command Line

Simplest example:

TYPEPERF  -c "\System\Processor Queue Length" -si 5 -sc 4

Emits the “System\Processor Queue Length” counter, four times with 5 second intervals (including 5 seconds before the first counter is emitted).

Which counters are available?

TYPEPERF -q > c:\BitBucket\TypePerfAvailableCounters.txt

Outputs a list of all counters available on the machine to a handy text file.

The only intelligent approach is to collect the counters you are interested in into a text file.  This is the list, aimed at SQL performance monitoring, that I am currently using.

Note on the wildcards.  …PhysicalDisk(*)..  will return counters for all disks (0 C:, 1 E, …) plus an aggregated counter named _Total.  I don’t want the _Total counter; the expression …PhysicalDisk(* *) matches all counters with a space in the identifier – which the disk names have but that the _Total name doesn’t.  Unfortunately the same hack doesn’t work for SQL Server:Locks(*) since most of the lock identifiers don’t have spaces.

\Memory\Available MBytes
\Memory\Pages/sec
\PhysicalDisk(* *)\% Disk Time
\PhysicalDisk(* *)\Current Disk Queue Length
\PhysicalDisk(* *)\Disk Transfers/sec
\PhysicalDisk(* *)\Disk Bytes/sec
\PhysicalDisk(* *)\Disk Reads/sec
\PhysicalDisk(* *)\Disk Writes/sec
\PhysicalDisk(* *)\Avg. Disk sec/Read
\PhysicalDisk(** *)\Avg. Disk sec/Write
\Processor(* *)\% Processor Time
\Processor(* *)\% Privileged Time
\System\Processor Queue Length
\System\Context Switches/sec
\Network Interface(* *)\Bytes Total/sec
\SQLServer:Access Methods\FreeSpace Scans/sec
\SQLServer:Access Methods\Full Scans/sec
\SQLServer:Buffer Manager\Buffer cache hit ratio
\SQLServer:Buffer Manager\Free pages
\SQLServer:Buffer Manager\Page life expectancy
\SQLServer:Latches\Total Latch Wait Time (ms)
\SQLServer:Locks(*)\Lock Timeouts/sec
\SQLServer:Locks(*)\Lock Wait Time (ms)
\SQLServer:Locks(*)\Number of Deadlocks/sec
\SQLServer:Memory Manager\Memory Grants Pending
\SQLServer:Memory Manager\Target Server Memory (KB)
\SQLServer:Memory Manager\Total Server Memory (KB)
\SQLServer:SQL Statistics\Batch Requests/sec
\SQLServer:SQL Statistics\SQL Re-Compilations/sec
\SQLServer:SQL Statistics\SQL Compilations/sec
\SQLServer:General Statistics\User Connections

TYPEPERF  -cf "C:\PerformanceMonitoring\CounterList.txt" -si 15 -sc 4  is a command that will use a CounterList file.

TYPEPERF  -cf "C:\PerformanceMonitoring\CounterList.txt" -si 15 -sc 4 -f SQL -o SQL:DatabaseStatisticsDS!Log1 will log to database using System DSN “DatabaseStatisticsDS” (creating the database if necessary).

You can gather counters from another server by using the -s option.  I haven’t attempted to do that yet; so far I am running typeperf against the local machine and logging the output to a distant one.

Permissions

User must be member of Performance Log Users group.  No errors raised if they aren’t but some (not all) counters will be zero.

Errors

The Baselining SQL Server article Part 1 covers TypePerformance errors (“No valid counters” !) in detail.