Analysis Services Time Dimension

Thanks to everyone who attended the Analysis Services session last week. The event was full (175 had registered) but we managed to get extra chairs for those of you who came in late.

I wanted to follow up on a question asked regarding time dimensions. You will almost always include a time dimension in your cubes. It’s normal for comparisons to be made over time. Same store sales year over year is a good example for the retail world. You would usually use your own time dimension to relate to the fact table. That said, SQL Server will create a time dimension for you if you so choose.

To do this, simply create a new server time dimension. Right mouse click on the dimensions folder to begin the process. You will be prompted with a date range and calendars to create like fiscal, manufacturing, and so on. You will need to generate a schema however so the cube has some data to bind to. Make sure you select the populate data checkbox otherwise your new table won’t be of much value. You will also need a column to bind to in the fact table which will likely be a date value. The schema name of the newly created table is the data source name – something to be aware of when dealing with security.

I hope this address the question about the server time table. I’ll probably use my own generated tables since they will be imported from the host system using SSIS. But you know that this option is always available to you.