Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 27th, 2004, 05:29 AM
Registered User
 
Join Date: Jun 2003
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Date Dimension

Hi,
I am developing a datawarehouse for my company but am a little stuck on the best practice when it comes to the Date Dimension. Should the date Dimension have all the days of the year hand typed into it and then the ETL tool check the date and make the correct link? or should the date dimension get a new entry every time a new customer is uploaded to the warehouse, therefore there may be 1000 entries all with 8th March 2004? or am I completely missing somthing else? any ideas?

Cheers
Magnus

 
Old March 1st, 2004, 11:19 AM
Authorized User
 
Join Date: Jun 2003
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I beleive the best method is to have all the days of the year in your table. You would of course do it "programatically" not by hand. Also; on a DW project I worked on - our ETL tool would create today's date for the table instead of creating all future days of the year - but either way - you should have an entry for each day.

_________________________
Joe Horton
Database Developer / Software Engineer
WISHA/Legal Services Software Development
Department of Labor and Industries
Voice (360) 902-5928 fax (360) 902-6200
 
Old August 10th, 2004, 06:45 AM
Registered User
 
Join Date: Aug 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You can use tools like DimensionModeler to generate your time dimension data and load it into your warehouse tables.

Check DimensionModelers web-site for functionality at http://www.dssdev.com/dm/dmoverview.html

Hope, this helps.

 
Old August 24th, 2004, 05:14 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I believe that SQl Server Analysis Manager will create a date dimmension for you complete with your values. It is some kind of wizard. Right click on Shared Dimmensions and select New Dimmension / Wizard.



Sal
 
Old August 31st, 2006, 09:32 AM
Registered User
 
Join Date: Aug 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

If you're using SQL Server 2005 you have 2 options:
-Create the table in the DW
-User the time server dimension built-in dimension.

The second option seems simple once that you don't have to create the dimension table, but I advice a little test to figure out if it meets your requirements. If you go this way remember to create a date-time column in your facts. I went this way, and am now seeking help because it doesn't meet my requirements.

The first option means that you'll have to create and populate the table. Chose a start and end date. There is a base script for creating and filling up the table:
"
CREATE TABLE [dbo].[DimTime] (
            [TimeSurKey] [int] IDENTITY (0, 1) NOT NULL ,
            [TimeFull] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [TimeHour] [tinyint] NOT NULL ,
            [TimeMinute] [tinyint] NOT NULL ,
            [TimeSecond] [tinyint] NOT NULL
) ON [PRIMARY]

GO


-----------------------------------------------------



insert into DimTime
select '00:00:00', 0, 0, 0

declare @ctr int
set @ctr =1
declare @actTime datetime
set @actTime = '2005-01-01 00:00:00'
while @ctr < 86400
begin
      set @actTime = @actTime + '00:00:01'
      insert into DimTime
      select convert(varchar,@actTime,8), datepart(hh,@actTime), datepart(n, @actTime), datepart(s, @actTime)
set @ctr = @ctr + 1
end

"

After having made the table you have two choices:
-to use the built-in time dimension in SQL Server (do not confuse with TIME-SERVER);
-to create a regular dimension.

The balance is always between flexibility and ease of use.





 
Old August 31st, 2006, 09:33 AM
Registered User
 
Join Date: Aug 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

If you're using SQL Server 2005 you have 2 options:
-Create the table in the DW
-User the time server dimension built-in dimension.

The second option seems simple once that you don't have to create the dimension table, but I advice a little test to figure out if it meets your requirements. If you go this way remember to create a date-time column in your facts. I went this way, and am now seeking help because it doesn't meet my requirements.

The first option means that you'll have to create and populate the table. Chose a start and end date. There is a base script for creating and filling up the table:
"
CREATE TABLE [dbo].[DimTime] (
            [TimeSurKey] [int] IDENTITY (0, 1) NOT NULL ,
            [TimeFull] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
            [TimeHour] [tinyint] NOT NULL ,
            [TimeMinute] [tinyint] NOT NULL ,
            [TimeSecond] [tinyint] NOT NULL
) ON [PRIMARY]

GO


-----------------------------------------------------



insert into DimTime
select '00:00:00', 0, 0, 0

declare @ctr int
set @ctr =1
declare @actTime datetime
set @actTime = '2005-01-01 00:00:00'
while @ctr < 86400
begin
      set @actTime = @actTime + '00:00:01'
      insert into DimTime
      select convert(varchar,@actTime,8), datepart(hh,@actTime), datepart(n, @actTime), datepart(s, @actTime)
set @ctr = @ctr + 1
end

"

After having made the table you have two choices:
-to use the built-in time dimension in SQL Server (do not confuse with TIME-SERVER);
-to create a regular dimension.

The balance is always between flexibility and ease of use.






Similar Threads
Thread Thread Starter Forum Replies Last Post
Dimension Hierarchies Not Working orangepower SQL Server 2005 1 December 14th, 2006 03:41 PM
Determine an Image dimension - ASP cancer10 Classic ASP Databases 3 November 21st, 2006 10:48 AM
One dimension array. Wenggo C# 5 August 18th, 2006 02:39 AM
SQL OLAP Dimension Writeback ripahoratiu SQL Server 2000 0 October 24th, 2003 02:43 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.