hi everybody,

i'm having a problem with a slowly cahnging dimension i developed on
SQL Server 2005. the dimension is employee and it's scd type 2. the
fact table has a sk_employee in it and a bussiness key of id_number.
when i set the organizational index and then put an employee next to
it, i see the employee mentioned 3 times in it for a certain year, as
that employee has changed 3 times during that year, and each
sk_employee is considered a different entity for the cube. i'd like to
see the employee's id_number just once. what would you suggest?
should i add atrribute types of scdEndDate, scdStartDate etc. - will
that solve it?
should i add a view which just takes the last version for each entity
in the employee table?
i would appreciate your help.

thanks,
ella

You should create an attribute that has the application key (invariant
for any customer) as key column, and whatever you want (the customer
name?) as name column.
Of course, there will be still a key attribute with the surrogate key
as a key column, and it is necessary to join the fact table.
The SCD type 2 is supposed to be useful exactly for this: tracking all
the variation of a dimension members over time. In the UDM, you have to
decide what you want to show to the user through attributes
granularity.

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

meshiach wrote:

hi marc,

thank you so much for the advice!