SSAS + Time Dimension and Hours

Hot Topic

To add one report or many
Report Builder Help
can't modify stored proc which is in a publication
External documentation
Service Broker connection
Store procedures & Functions
how do i backup my users + passwords?
SQL Server Import Copy or Query
Change "Report is being generated"
How robust is Reporting Services 2005

SQL Server

SELECT in a table takes FOREVER
Reporting Services 2000 broke by installing .NET 2.0 redistributable runtime files
'Incorrect Syntax near' Error in Create Type in 2005
Posting XMLA to AS from java without msmdpump.dll
One dropdownlist depend from other.
Transaction log shipping over WAN with compression
Inserting rows concurrently (paralellism)
Update column with 8 digit random number
SQL 2005 License Options
Publication

SSAS + Time Dimension and Hours

Hi All,

I'm very new to the whole Analysis Services scene and I'm stuck. I need to create a time table with the following attributes:

TimeKey, Hour, Day, Month, Quarter, Year

Trouble is, I can create a dimension, but without the Hour part...

I've read through the tutorials and online help, but I cant find anything to help me. I've found a couple of articles that suggest having a Day and TimeOfDay dimension, but not how to create them.

Could anyone help

Thanks

 

Answer this question

SSAS + Time Dimension and Hours

  • pmela

    I absolutely agree with having a Date dimension and a separate TimeOfDay dimension. Creating such a TimeOfDay dimension table is simple TSQL. Just create a table with 3 fields (Hour, Min, Sec) and insert records in it for each second in the day. You end up with 86400 records (if your grain is down to the second -- obviously it would be different if your grain was every 5 min or every hundredth of a second).

    Then in your fact table, take the timestamp and create two calculated fields from it that expose the Date and the TimeOfDay using the date_part function.

    Hope that helps.

    _-_-_ Dave



  • SSAS + Time Dimension and Hours
Answer this question