How to Create Time Dimension??

Question

Post reply

How to Create Time Dimension??

rhunt

SSCertifiable

Points: 5868
More actions
February 16, 2006 at 12:52 pm

#111689

BI newbie here. I have created and populated my dimension and fact tables for my first cube. However, I'm not sure how to configure/create a time dimension.
I have one simple fact table...the columns are:
StoreFront, AccountNumber, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec.
I would like to have a time dimesion of :
Year-->Quarter-->Month
How should I design my time dimension table and how to perform the aggregations?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Chris Teter SSC-Addicted Points: 493 More actions · Answer 1

Are you saying that you have each Month name as a column? Because I don't understand why you would do this.

Also, do you have any facts in this fact table?

I'm not quite sure I understand what you are trying to do. Please provide a little more info/background, and I'll try to help you out.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 2

I would normally pivot the data also (like Chris is alluding to) and have the 'time' identifier as a single field and the 'amount' as a single field (you're currently using Time as individual fields and implicitly including the amount under each one). This would also let you add other measure fields with little to no impact on the existing system (eg adding a 'Cost' field to your table would require the addition of either 12 new fields, one for each month, or adding a single field that then identifies the row as being a cost row versus a revenue row - not overly nice. Adding cost to the suggested structure would be adding a single cost field).

Taking this approach would also let you build a time dimension that will work as you outlined and in a very similar way to how your product/store/employee ... dimensions work now. A single foreign key in the fact will relate to the primary key in the dimension. You can then store whatever hierarchies you want in that dim (eg the time dim could have both fiscal and calendar years - this would be harder to do with your current setup).

Steve.

Duncan Lawrence SSCrazy Points: 2011 More actions · Answer 3

Hi, I regularly have to create time dimensions and have scripted a stored procedure to do just that. The procedure creates a table called ext_calendar and takes two arguments; the first date you want and the last date. It does assume a time dimension hierarchy of Year, Quarter, Month, however it would be a simple matter to add a further level of day.

The table created has a single record for each date between the two dates with the following columns.

Column 1 - CalDate - the day's date.

Column 2 - SOMDate - the first date in the month of the CalDate.

Column 3 - EOMDate - the last date in the month of the CalDate.

Column 4 - FinYear - the financial year to which the CalDate belongs - this does assume a financial year commencing on the 1st April each year.

Column 5 - FinQtr - the financial quarter to which the CalDate belongs - this does assume a financial year commencing on the 1st April each year.

Column 6 - FinQtrName - the name of the financial quarter.

Column 7 - FinMonth - the financial month to which the CalDate belongs - this does assume a financial year commencing on the 1st April each year.

Column 8 - FinMonthName - the full name of the date.

Here is the full script as well as some additional code for running and testing.

/*this stored procedure creates a calendar table called ext_calendar between two given dates.

there are two arguments to input, the first and last dates required in american format*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_create_calendar]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[usp_create_calendar]

GO

create procedure usp_create_calendar(@StartDate smalldatetime, @EndDate smalldatetime)

as

if exists (select * from dbo.sysobjects where id = object_id(N'[ext_calendar]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [ext_calendar]

CREATE TABLE [ext_calendar](

CalDate smalldatetime,

SOMDate smalldatetime,

EOMDate smalldatetime,

FinYear int,

FinQtr tinyint,

FinQtrName varchar (20),

FinMonth tinyint,

CalMonthName varchar (20)

&nbsp

INSERT INTO ext_calendar

SELECT @StartDate,

NULL,

NULL

declare @MaxCalDate smalldatetime

set @MaxCalDate = @EndDate

while (@MaxCalDate <= @EndDate)

begin

insert into ext_calendar

select dateadd(dd,1,max(CalDate)),

NULL,

NULL

from ext_calendar

if (select max(CalDate) from ext_calendar)>= @EndDate

break

else

continue

end

update ext_calendar

set FinYear = (

select case

when datepart(mm,CalDate) between 4 and 12

then datepart(yyyy,CalDate)+1

else datepart(yyyy,CalDate)

end

&nbsp,

FinQtr = (

select case

when datepart(mm,CalDate) between 4 and 6

then 1

when datepart(mm,CalDate) between 7 and 9

then 2

when datepart(mm,CalDate) between 10 and 12

then 3

else 4

end

&nbsp,

FinQtrName = (

select case

when datepart(mm,CalDate) between 4 and 6

then 'Qtr. 1 '+cast((datepart(yyyy,CalDate)+1)as char)

when datepart(mm,CalDate) between 7 and 9

then 'Qtr. 2 '+cast((datepart(yyyy,CalDate)+1)as char)

when datepart(mm,CalDate) between 10 and 12

then 'Qtr. 3 '+cast((datepart(yyyy,CalDate)+1)as char)

else 'Qtr. 1 '+cast(datepart(yyyy,CalDate)as char)

end

&nbsp,

FinMonth = (

select case

when datepart(mm,CalDate) between 4 and 12

then datepart(mm,CalDate)-3

else datepart(mm,CalDate)+9

end

&nbsp,

CalMonthName = datename(dd,CalDate)+' '+datename(mm,CalDate)+' '+datename(yyyy,CalDate)

select min(CalDate)SOMDate,

max(CalDate)EOMDate

into #tmp_cal_months

from ext_calendar

group by datepart(yyyy,CalDate),

datepart(mm,CalDate)

order by datepart(yyyy,CalDate),

datepart(mm,CalDate)

update c

set c.SOMDate = t1.SOMDate,

c.EOMDate = t1.EOMDate

from #tmp_cal_months t1,

ext_calendar c

where datepart(yyyy,t1.SOMDate) = datepart(yyyy,c.CalDate)

and

datepart(mm,t1.SOMDate) = datepart(mm,c.CalDate)

drop table #tmp_cal_months

go

exec usp_create_calendar '04/01/2004', '03/31/2006'

select * from ext_calendar

If you like it and can use it, please help yourself.

Duncan

All the best,

Duncan

JeffB Hall of Fame Points: 3541 More actions · Answer 4

Since Analysis Services allows you to specifiy that a dimension is a time dimension when creating it, the following is the practice that I use:

Create Table dimDate (DateID int Identity(1,1), Date Datetime)

--Insert all the relative dates for you data into this table using something like a numbers table or a while loop

Have the fact table defined as:

Create Table fctItem(DateID int, DimID1 int, DimID2 int, ..., Measure1 int, Measure2 int, Measure3 money, ...)

Finally, when creating the dimension using the wizard, specifiy 'Time' instead of 'Standard' and you can choose any hierarchy, naming, format etc that you like.

rhunt SSCertifiable Points: 5868 More actions · Answer 5

Yes - each month is a column name. This is a common format we use for tables that hold balances, not line item entries such as a ledger. Our ERP uses it...many of our financials use it (easy summing down the column) and because we so commonly output the information in this format, you have to write a lot less SQL most of the time. Additionaly, since there is a century and year column as well in production tables, one year is exactly one line (unless we sneak in a 13th month some year). Additionaly, month to month calculations (variances, etc) are simplified and easily calculated in just one row. Anyway - these are just some examples of why we use this format.

Now, I completely agree that the time dimension would be simplified if each month was a row item (rather than a column) and I could store multiple time facts as attributes of the month.

We use Hyperion (which I am trying to get away from) and that system currently has the structure listed above. However, hyperion lets you easily match up arbitrary fact columns with different levels of a time dimension (i.e. columns 3, 4, 5 = Q1 of time dimension, etc.)

So, basically inexperience with cubes, fact tables, and dimensions led me in this direction. I will try breaking the columns into rows and storing time attributes (facts...err...uhh, measures?) as part of the row.

I'm still trying to get me terminology correct...

Thanks for the responses. RH

JeffB Hall of Fame Points: 3541 More actions · Answer 6

Since the souce data is in the format you have, you could base the cube on a view that transposes the data into the format of:

storefront, account, dateid, balance

Sample SQL code is follows. You'll have to add the additional months and make the select into a view.

declare @date table (dateid int, date datetime)

insert @date values (1, '01 Jan 2006')

insert @date values (2, '01 Feb 2006')

insert @date values (3, '01 Mar 2006')

declare @Balance table (Storefront varchar(20), Account varchar(20), Jan int, Feb int, Mar int)

insert @Balance values ('NY', 'A1', 1, 1, 2)

insert @Balance values ('NY', 'B2', 3, 4, 9)

select Storefront,

account,

d.dateID,

case when datepart(mm, d.date) = 1 then b.Jan

when datepart(mm, d.date) = 2 then b.Feb

when datepart(mm, d.date) = 3 then b.Mar

end

from @Balance b

cross join @Date d

hr_sn Ten Centuries Points: 1062 More actions · Answer 7

If it is Analysis Services 2005 then you can create time dimension using Buisness Intelligence studio 2005. Just right click on dimensions and add new dimension. then follow the prompts, it will give option to populate the table lying under this dimension as well.