Adding new dimension members

[BKhanna]

I have a 'Location' dimension. New locations (airport codes) are frequently added and I was wondering how I can update this dimension so that the new members are added to a catch-all bucket in the dimension table when the data is loaded. In Essbase you can update a dimension using the data file as a source, and choose to add any new members under a parent called 'New'. I am not sure how to accomplish this in Analysis Services.

 

[johnherman]

You would probably want to do an incremental update on the dimension, but I am pretty sure there is no equivalent to the default group you mentioned that Essbase has.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[justins57]

BK,

You can flag the new location with a member property of "New". There would need to be some business rule (effective date, removal date) for this member property.

Justin

 

[BKhanna]

The problem is, how would I know that there is a new member in the data? Say, the Location dimension already has locations ATL, BOS and DFW. If a new location JFK appears in the fact table, I wouldn't know it's there, and this row of data would not get loaded into the cube because there is no JFK in the dimension table.

 

[Flybridge]

A bit off the wall this, but may be worth it.

In your data source, can you get the code for the dimension (JFK) from the fact table, then get the descriptive details from the table(s) currently feeding the dimension table?

That way, by using a default of 'NEW' as suggested above, when a new code is created you can make sure you trap it.

Flybridge