Time (Not Date) Dimension Table SQL code

I am seeing more call for Time Dimensions. By that I mean Time-of-Day Dimensions. So now I have to retrain myself to call Date Dimensions "Date" and Time Dimensions "Time".


As a follow up to this post, here's some DDL and a quick routine to generate a time of day table. The resolution is to the second, which so far has proved sufficient for my clients' purposes.


Enjoy


SET nocount ON
/*
CREATE TABLE [dbo].[dim_Time](
  [TimeId] [int] NOT NULL,
  [Time] [time](7) NULL,
  [Hours] [tinyint] NULL,
  [Minutes] [tinyint] NULL,
  [Seconds] [tinyint] NULL,
 CONSTRAINT [PK_dim_Time] PRIMARY KEY CLUSTERED
(
  [TimeId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

TRUNCATE TABLE [dbo].[dim_Time]
GO
SELECT * FROM dbo.dim_time
*/

DECLARE @second1 INT = 0
DECLARE @currtime TIME
DECLARE @msg VARCHAR(30) = ''


WHILE @second1 < 86400
  BEGIN
      SELECT @second1 = @second1 + 1
      SELECT @currtime = Dateadd(ss, @second1, '00:00.00')
      --  SELECT   
      --DatePart(hh, @currtime) * 10000 + 
      --DatePart(mi, @currtime) * 100 +
      --DatePart(ss, @currtime) AS TimeID
      --, @currtime AS [Time]
      --, DatePart(hh, @currtime) AS [Hours]
      --, DatePart(mi, @currtime) AS [Minutes]
      --, DatePart(ss, @currtime) AS [Seconds]
      INSERT dbo.dim_time
             ([TimeID],
              [Time],
              [Hours],
              [Minutes],
              [Seconds])
      VALUES (
              Datepart(hh, @currtime) * 10000 + 
              Datepart(mi, @currtime) * 100 + 
              Datepart(ss, @currtime),
               @currtime,
               Datepart(hh, @currtime),
               Datepart(mi, @currtime),
               Datepart(ss, @currtime) )
      IF @second1 % 1000 = 0
        BEGIN
            SELECT @msg = 'Now Processing ' + CAST(@second1 AS VARCHAR(12))
            PRINT @msg
        END
  END 

Adventures with Virtualization

Those of us needing to run BI software in virtual environments are well familiar with the challenges of getting things running smoothly, especially if you are using a laptop as your lab or demo environment. We have a new set of challenges with Windows 7, and with SharePoint 2010.

Challenge #1 - SharePoint 2010 requires 64 bit OS, and the only MS virtualization software that allows for 64-bit is Hyper-V.

Only problem with running Hyper-V is that you cannot sleep a machine with Hyper-V, so if you’re using you laptop as a lab machine, you need to retain an OS that will run productivity apps and sleep. I set up my old laptop using the technique described here and it works well. I haven’t repeated the process for my new laptop yet, but I plan to.

Challenge #2 - Windows 7 Virtual PC requires Hardware Assisted Virtualization

Apparently this is no longer true, but I've wrestled with this one for months, in fact in early March I returned a machine that didn’t support hardware assisted virtualization because I needed to run Windows 7 VPC. . MS just released a version of Virtual PC that does not require hardware assisted virtualization. 

Other tips that have worked well for me:

• Use a fast disk - I was using a 7200 RPM eSATA external drive - faster than the 5400 disk commonly found on laptops. Now I am using an SSD drive- even better.
• Store the VHD in a compressed folder. This one struck me as counter-intuitive but anecdotally seems to work.