create time dimension based on the columns of the fact table

Question

 

i have a fact table, which contains columns 'year' and 'month'.

While i created a time dimension with the type of 'time dimension' (not 'server time dimension') and relate the 'time table columns' and 'time property name' together, i.e.

 

Time prperty name      Time table columns

year                 year

month               month

 

afterwards, the attribute ID was automatically created for this dimension, and be set to the 'granuarity attribute' by default.

However,when i use the dimension to slice data, it is not correctly displayed.

I am pretty new for SSAS, and don't know if this is the right way to create time dimension ...

Be greate appreciate for your words..

Thank you!

 

 

Answer 1

 

Hi,

 

I think you should have marked the Month field as MonthOfYear, not Month. What's the difference? Well, Month means 200812 for example, while MonthOfYear is what you, I'm almost certain, have in that field - 12.

 

The rest I didn't get, but you should have 3 attributes in your dimension: Year, Month of Year, Month. You have the first two already. The last one should be the key. Its KeyColumns in Properties should be Year + Month (2 fields, you can add more fields for key, check it out).

 

If it is easier to you, you can generate that 3rd field in DSV by an action New named column. You can build the field the same way I presented you above - using this format: yyyyMM.Then just collect all 3 fields and make a time dimension from it.

 

Usually you create time dimension as a separate table, using a script, no wizards or similar, for less headache in future. But I think it will be ok this way too, since it's the simplest way, you're a beginner, and this is a special case you need (no dates). Unless you have a large fact and no index on these fields. Then make it as a separate table with select distinct.

 

Regards,

 

 

Tomislav Piasevoli

Business Intelligence Specialist

http://www.softpro.hr

Answer 2

 

I think you should have marked the Month field as MonthOfYear, not Month. What's the difference? Well, Month means 200812 for example, while MonthOfYear is what you, I'm almost certain, have in that field - 12.

 

I did need the MonthOfYear because in my fact table, the column is , for instance, 12. (instead of 200812)

 

The rest I didn't get, but you should have 3 attributes in your dimension: Year, Month of Year, Month. You have the first two already. The last one should be the key. Its KeyColumns in Properties should be Year + Month (2 fields, you can add more fields for key, check it out).

 

When i relate the attritubes to the time dimension attributes, if the Month is related to the Month column in my fact table, there is no possibility to relate to the MonthOfYear again. Also the key is automatically assigned to another sorrogate key.(ID).

 

 

If it is easier to you, you can generate that 3rd field in DSV by an action New named column. You can build the field the same way I presented you above - using this format: yyyyMM.Then just collect all 3 fields and make a time dimension from it.

 

 

Usually you create time dimension as a separate table, using a script, no wizards or similar, for less headache in future. But I think it will be ok this way too, since it's the simplest way, you're a beginner, and this is a special case you need (no dates). Unless you have a large fact and no index on these fields. Then make it as a separate table with select distinct.

I didn't get the point...could u pls indicate the formal way for creating the time dimension?

Answer 3

 

Hi,

 

ok, here's a step-by-step guide.

 

Create a store procedure in your DW database (datawarehouse).

 

Code Snippet

CREATE PROCEDURE [dbo].[usp_Create_dim_Date_Table]

(

@StartDate smalldatetime = '20080101',

@EndDate smalldatetime = '20081231'

)

AS

BEGIN

SET NOCOUNT ON;

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dim_Date]') AND type in (N'U'))

DROP TABLE [dbo].[dim_Date]

CREATE TABLE dbo.dim_Date(

[MonthKey] [int] NOT NULL,

[MonthName] [nvarchar](15) NOT NULL,

[YearKey] [smallint] NOT NULL,

[YearName] [nvarchar](4) NOT NULL,

[MonthOfYearKey] [tinyint] NOT NULL,

[MonthOfYearName] [nvarchar](15) NOT NULL,

CONSTRAINT [PK_dim_Date] PRIMARY KEY CLUSTERED

(

[MonthKey] ASC

)

)

set language English -- or any other

set datefirst 1 -- 7 for Sunday, 1 for Monday

set dateformat dmy -- set differently if necessary

declare @CurrentDate smalldatetime

declare @TimeID as int

set @CurrentDate = @StartDate

set @TimeID = 1

while ( @CurrentDate <= @EndDate )

begin

insert into [dbo].[dim_Date]

(

[MonthKey] ,

[MonthName] ,

[YearKey] ,

[YearName] ,

[MonthOfYearKey] ,

[MonthOfYearName]

)

values

(

-- month

datepart(year, @CurrentDate ) * 100 + datepart(month, @CurrentDate ),

convert(varchar(4), datepart(year, @CurrentDate )) + ' ' +

'M ' + convert(char(2), datepart(month, @CurrentDate )),

-- year

datepart(year, @CurrentDate ),

convert(varchar(4), datepart(year, @CurrentDate )),

-- month of year

datepart(month, @CurrentDate ),

datename(month, @CurrentDate )

)

set @TimeID = @TimeID + 1

set @CurrentDate = dateadd( month, 1, @CurrentDate )

end

END

 

 

Tweak some options (see comments).

 

Execute it with your parameters (span of dates that your data lies between or greater. Or, put two dates for span directly into stored procedure as parameters' defaults in case you prefer that way (I made defaults for year 2008).

 

That should generate table dim_Date, with N rows. Preview of results executed with default params is here (just to get a better picture of what's going on):

 

MonthKey MonthName YearKey YearName MonthOfYearKey MonthOfYearName
200801 2008 M 1  2008 2008 1 January
200802 2008 M 2  2008 2008 2 February
200803 2008 M 3  2008 2008 3 March
200804 2008 M 4  2008 2008 4 April
200805 2008 M 5  2008 2008 5 May
200806 2008 M 6  2008 2008 6 June
200807 2008 M 7  2008 2008 7 July
200808 2008 M 8  2008 2008 8 August
200809 2008 M 9  2008 2008 9 September
200810 2008 M 10 2008 2008 10 October
200811 2008 M 11 2008 2008 11 November
200812 2008 M 12 2008 2008 12 December

 

Of course, you can customize the names in stored procedure if you don't like it this way.

 

Now, make a time dimension from it. There are 6 fields, 3 pairs of key-name. Make 3 attributes (not 6) in your time dimension, using Key fields for keys and Name field for names for the same attribute. Use the first field (MonthKey) as Key attribute of your dimension.

 

Then go to DSV (datasourceview) in SSAS database, select your fact table and make a New named column. Enter a title for it - MonthKey. Use this definition:

 

[Year] * 100 + [Month]

 

where [Year] and [Month] are the names of columns in your fact table that specify that terms - year and month. If not, adapt them to match your fields. Test browsing a fact table.

 

Now, link your fact and dimension table dim_Date in DSV on fields MonthKey (drag&drop from fact towards dimension table). Save.

 

In Dimension Usage tab of cube, link your Date dimension to fact on the same field - MonthKey. Process and browse the data.

 

That should give you good results.

 

Hope it helps.

 

 

Tomislav Piasevoli

Business Intelligence Specialist

http://www.softpro.hr

 

Answer 4

that's eactly what i did for the solution, although forget where i got the same help.
Thank you!:)