Post reply

SCD Wizard question

  • July 19, 2007 at 6:09 am

    #119785

    I'm having a problem with the SCD wizard.  Primarily, that it wants me to choose a Business Key when one technically doesn't exist.

    As Kim Possible likes to say: "Here's the sitch".

    A certificate is an insurance policy.  I have in my transaction DB a table with certificate information.  Each individual certificate belongs to an indivdual customer so the customer name and address information is recorded in this table.  There will never be a certificate without a customer and while a customer can hold more than one certificate, it is never for the same thing.

    In my datawarehouse, I'm separating out the certificates from the customer information.  Yes, I know this should have been done in the DB, but the DB is old and there is no way to change it at this late date.  So, I'm doing it in my DW.  The problem is, the only unique identifier associated with the customer information is the Primary Key ID that is also associated with the certificate.

    Should I use that Primary Key as my Business Key in the Slowly Changing Dimension Wizard?  What effect will it have on my recordset? 

    I don't want to store that Primary Key in my Customers table.  I'm already storing it in the Certificates table.  And I have a central table that will be connecting the Certificates key to the Customers new key.  But the question is, can I get away with doing this if I want to implement Slowly Changing Dimensions?

    Thanks in advance for any advice!

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • July 26, 2007 at 8:12 am

    #722584

    Brandie,

    my experience with the SCD is that you should see the business keys as columns which, when they change , require a new record. Also the business key should be something which has a business meaning, like product name or customer name. In my opinion the Unique Primairy ID satisfies the first condition , but certainly not the second. It is more or less a degenerated key, a meaningless number, which in fact can be discarded in the datawarehouse.

    What I assume (correct me if I am wrong, because I am not familiar with the insurance business) , is that a certificate (or policy)  is a combination of a specific insurance product (like life insurance, fire insurance )  and a customer. 

    So looking in that way to it, you could store the certificates as a fact table containings all policies. This fact table then contains for instance start- and enddate of the policy, payments , the person who closed the deal, etc , and is connected to a customer dimension and a insurance dimension. It also contains, if you wish, the unique ID. So then your problem is transferred to the insurance product dimension. But certainly a insurance product has a specific name, which can be used as busines key of the SCD ?

    When you have a fact table you can make all kind of reports on all existing policies or certificates. This might not be possible if you store the policies in a dimension. When you define the policies in a fact table, then the SCD in not in scope , because I think you should not use the SCD on a fact table ( maybe for small, but certainly not bigger than 100K records.), because of possible performance problems.

     

    Iit s also rather important what kind of reports you need to make !  Are there any business requirements ?

    Hope this helps !

    Servaas Winkelman.

     

     

     

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply