"Eugene Frolov" <john@alice.ru> wrote in message
news:OjW3H%23XvEHA.3272@TK2MSFTNGP12.phx.gbl...
> Hi, All!
>
> I need to help on this problem:
> there is a dimension table with this structure in the Staging DB -
>
> Customer_Code nvarchar(50)
> Customer_Name nvarchar(50)
> SalesPerson_Code nvarchar(50)
>
> Lets imagine I'm having customer record like this:
>
> 123 'Horns and hoofs Inc.' 'Mr. Gorbachiov'
>
> After Muster_Update.dts package executing it will be copied in the
> corresponding table in Subject Matter DB.
>
> 100 123 'Horns and hoofs Inc.' 'Mr. Gorbachiov'
> '01/01/2000' '06/06/2079' ACTIVE
>
> It's OK.
>
> Some days later source record was changed like this (Customer_Name
changed)
>
> 123 'Horns and hoofs LTD.' 'Mr. Gorbachiov'
>
> Due to this change I want simply UPDATE EXISTING record in SM DB.
>
> 100 123 'Horns and hoofs LTD.' 'Mr. Gorbachiov'
> '01/01/2000' '06/06/2079' ACTIVE
>
> But another days later Mr. Gorbachiov has left our company and Mr. Eltsin
> became responsible person for working with 'Horns and hoofs LTD.'
customer.
> This looks like this in Staging DB:
>
> 123 'Horns and hoofs LTD.' 'Mr. Eltsin'
>
> And I want to reflect this change in SM DB like this:
>
> 100 123 'Horns and hoofs LTD.' 'Mr. Gorbachiov'
> '01/01/2000' '01/01/2001' INACTIVE
> 200 123 'Horns and hoofs LTD.' 'Mr. Eltsin'
> '01/01/2001' '06/06/2079' ACTIVE
>
> I. e I want to TRACK history of Sales persons changes associated with
every
> customer and RESTATE history of Customer_name changes.
>
> Is this possible to configure SSABI generated dts packages or do something
> else to support this behaviour?
>
> Thank for advance!
>
>
>

> Sounds like you want a type-1 SCD for the member name; and a type-2 SCD on
> member properties.

> If so, that is beyond the scope of basic BI Accelerator generated
> applications. Sorry, we couldn't do everything :-)

> You will have to implement it by-hand after the application is generated.
>
> I would suggest that you generate it as a type-2 SCD (track history) and
> then modify the packages so it does a type-1 on a member name change. I
> think it would be easier to implement an update in-place if the "track
> history" is already there; rather than the other way around.
>