Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.

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...