17

I'm using BIDS to create a cube in SSAS 2008 r2.

I used the wizard to create a time dimension. Part of that configuration is selecting a date range.

After creation, I realized that I needed a wider date range than I originally specified.

I did find that I could open the dimension, go to properties, and redefine the date range there. I saved and reprocessed the dimension, but the actual date range in the table is not growing to include the newly-added dates.

Is there another way to get this time dimension to grow or do I need to recreate it from scratch?

JNK
  • 17,718
  • 5
  • 56
  • 97
  • 3
    I have always created mine from scratch via a stored procedure that accepts start/stop dates and granularity (seconds, minutes, etc) as parameters. – datagod Apr 20 '12 at 14:42
  • 1
    Well I used the wizard because I didn't need anything finer than day, and the groupers were built in. I'm an SSAS noob so I was looking for the path of least resistance. – JNK Apr 20 '12 at 14:43
  • So as a workaround I just recreated this from scratch, but I'd like to know if there is another solution for this. – JNK Apr 23 '12 at 13:47
  • _but the actual date range in the table is not growing to include the newly-added dates_ How did you browse to see newly added data, after reprocessing dimension? What was original time period? – Ilya Berdichevsky Apr 23 '12 at 19:37
  • @nikadim I browsed the dimension after reprocessing. I also ran my cube process again which failed since there were dates it was trying to join that were outside the range. The original range was from like 2009-2012 and I added from 2006-- – JNK Apr 23 '12 at 19:57
  • @JNK - Which option in the Wizard for creating the time dimension did you choose? – Lamak Apr 24 '12 at 14:49
  • @Lamak Create a time dimension in the DSV – JNK Apr 24 '12 at 15:09

2 Answers2

1

Take a look at the Time Dimension properties dialog. Expand the Source node and change the CalendarStartDate or CalendarEndDate to suit your needs. Then Reprocess the Dimension.

mbbblue
  • 11
  • 2
1

Is your Time dimension connected to a source or is it a Server time dimension which has no table for it in the Data source

because usually when you generate a time dimension using BIDs it gives you these two options

and it looks like in your case you dont have a table for it in the datasource, i mean you chose a server time dimension which creates a dimension in Analysis server itself.

so you wont be able to append or add more dates, you will need to recreate it from scratch.

but if you chose the other option "Generate a time table in the data source" This option creates a time dimension table in the underlying data source and populates the same with data based on the inputs provided.

you should have access to be able to create a table in your data source

in this case you can add records to the table and reprocess it.

here is an easy script to create and fill a time dimension table and later you can generate your time dimension in the cube from this table

http://microsoft-ssis.blogspot.com/2011/01/create-and-fill-time-dimension.html

and here is 2 articles about generating a time dimension and its options

http://www.sqlservergeeks.com/blogs/AmitK/sql-server-bi/383/ssas-considerations-for-time-dimension-part1

http://www.sqlservergeeks.com/blogs/AmitK/sql-server-bi/385/sql-server-ssas-considerations-for-time-dimension-part-2

AmmarR
  • 2,756
  • 3
  • 22
  • 32
  • There was a table for it in the data source. – JNK May 17 '12 at 12:04
  • i just tried creating a time dimension, and after creating and processing it, i changed the data range as you did from the properties of the time dimension, saved it, processed it again and in the browser tab i clicked refresh and the new dates did show up......... if this doesn't work with you try adding dates manually to the data source table and reprocess the dimension – AmmarR May 17 '12 at 12:14