Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

Need to create a time dimension down to the millisecond?

Ok, I know that creating a time dimension down to the millisecond is extremely rare.  After all, we are talking about  86,400,000 records.  In my case, it is needed for a customer with a 50TB database that contains stock trades that are recorded down to the millisecond (we are using a Parallel Data Warehouse).  You can imagine how long it would take to build this table using a loop with inserts.  But my friend Martin Lee came up with a very fast solution using a cross join (cartesian product).  Check out the code:


/* CREATE TIME PART TABLES TO DO A CROSS JOIN CARTESIAN PRODUCT TO CTAS INTO DIMTIME */
--CREATE A SINGLE VALUE TABLE TO USE AS DUMMY FROM TABLE.
IF NOT EXISTS (SELECT 1 FROM DirectEdgeDW.sys.tables WHERE name = 'Singleton' AND type = 'U')
BEGIN
	CREATE TABLE DirectEdgeDW.dbo.Singleton
	WITH (DISTRIBUTION = REPLICATE)
	AS
	SELECT DISTINCT 1 AS VALUE FROM SYS.DATABASES
END

--GENERATE MILLISECOND TABLE

IF EXISTS (SELECT 1 FROM DirectEdgeDW.sys.tables WHERE name = 'MCL_TimePart_MS' AND type = 'U')
BEGIN
	DROP TABLE DirectEdgeDW.dbo.MCL_TimePart_MS
END
CREATE TABLE DirectEdgeDW.dbo.MCL_TimePart_MS
WITH (DISTRIBUTION = REPLICATE)
AS
SELECT TOP 999 ROW_NUMBER() OVER (ORDER BY REQUEST_ID) AS Millisecond
FROM sys.dm_pdw_exec_requests
INSERT INTO MCL_TimePart_MS
VALUES (0)

--SELECT * FROM MCL_TimePart_MS
IF EXISTS (SELECT 1 FROM DirectEdgeDW.sys.tables WHERE name = 'MCL_TimePart_SS' AND type = 'U')
BEGIN
	DROP TABLE DirectEdgeDW.dbo.MCL_TimePart_SS
END
CREATE TABLE DirectEdgeDW.dbo.MCL_TimePart_SS
WITH (DISTRIBUTION = REPLICATE)
AS
SELECT Millisecond AS Second FROM DirectEdgeDW.dbo.MCL_TimePart_MS WHERE Millisecond < 60
--select * from MCL_TimePart_SS

IF EXISTS (SELECT 1 FROM DirectEdgeDW.sys.tables WHERE name = 'MCL_TimePart_MM' AND type = 'U')
BEGIN
	DROP TABLE DirectEdgeDW.dbo.MCL_TimePart_MM
END
CREATE TABLE DirectEdgeDW.dbo.MCL_TimePart_MM
WITH (DISTRIBUTION = REPLICATE)
AS
SELECT second AS Minute FROM DirectEdgeDW.dbo.MCL_TimePart_SS
--select * from MCL_TimePart_MM

IF EXISTS (SELECT 1 FROM DirectEdgeDW.sys.tables WHERE name = 'MCL_TimePart_HH' AND type = 'U')
BEGIN
	DROP TABLE DirectEdgeDW.dbo.MCL_TimePart_HH
END
CREATE TABLE DirectEdgeDW.dbo.MCL_TimePart_HH
WITH (DISTRIBUTION = REPLICATE)
AS
SELECT second AS Hour FROM DirectEdgeDW.dbo.MCL_TimePart_SS WHERE Second < 24
--select * from MCL_TimePart_HH

--CROSS JOIN
IF EXISTS (SELECT 1 FROM DirectEdgeDW.sys.tables WHERE name = 'DimTime' AND type = 'U')
BEGIN
	DROP TABLE DirectEdgeDW.dbo.DimTime
END
CREATE TABLE DirectEdgeDW.dbo.DimTime
WITH (CLUSTERED INDEX(TimeKey),DISTRIBUTION = REPLICATE)
AS
SELECT CAST(CAST(hh.Hour AS VARCHAR(2)) + RIGHT('0' + CAST(mm.Minute AS VARCHAR(2)),2) + RIGHT('0' + CAST(ss.Second AS VARCHAR(2)),2) + RIGHT('00' + CAST(ms.Millisecond AS VARCHAR(3)),3) AS INT) AS TimeKey
	, hh.Hour, mm.Minute, ss.Second, ms.Millisecond
FROM
	DirectEdgeDW.dbo.MCL_TimePart_MS ms
	CROSS JOIN DirectEdgeDW.dbo.MCL_TimePart_SS ss
	CROSS JOIN DirectEdgeDW.dbo.MCL_TimePart_MM mm
	CROSS JOIN DirectEdgeDW.dbo.MCL_TimePart_HH hh

INSERT INTO dbo.DimDate (DateKey,FullDateAltKey,DayNumberOfWeek,DayNameOfWeek,DayNumberOfMonth,DayNumberOfYear,WeekNumberOfYear,WeekNameOfYear,[MonthName],MonthNumberOfYear,MonthFlag,QuarterNumber,QuarterName,QuarterFlag,SemesterNumber,SemesterName,SemesterFlag,[Year])
SELECT -1, CAST('1/1/2100' AS DATE), 0, 'Unknown', 0, 0, 0, 'Unknown', 'Unknown', 0, 0, 0, 'Unknown', 0, 0, 'Unknown', 0, 0
FROM [DirectEdgeDW].[dbo].[UnknownMembers]

DROP TABLE DirectEdgeDW.dbo.MCL_TimePart_MS
DROP TABLE DirectEdgeDW.dbo.MCL_TimePart_SS
DROP TABLE DirectEdgeDW.dbo.MCL_TimePart_MM
DROP TABLE DirectEdgeDW.dbo.MCL_TimePart_HH

Comments

Leave a comment on the original post [www.jamesserra.com, opens in a new window]

Loading comments...