Loading Fact table

Hi,

I have following tables FACT, A_DIM, B_DIM. How do I update relationship between a_key and b_key in the FACT table when new record arrives in TYPE2 a_dim dimension? Below is simple create scrip to illustrate what I mean.

create table fact (a_key integer, b_key integer);
create table a_dim (a_key integer, a_id integer, a_string varchar2(20));
create table b_dim (b_key integer, b_id integer, b_string varchar2(20));

insert into a_dim values (1, 100, 'value1');
insert into a_dim values (2, 200, 'value2');

insert into b_dim values(1, 10, 'somestring1');

insert into fact (a_key, b_key) values (1, 1);
insert into fact (a_key, b_key) values (2, null);

Now new record arrives into a_dim:
insert into a_dim values (3, 100, 'value1_1');

insert into fact (a_key, b_key) values (3,1);

Do I want to make sure that fact has only one record that joins a_dim and b_dim or it is normal to have old key's maintaining ref- integrity and if not what is the best way to archive this?

Thank you in advance

Not sure what you are trying to achieve here.
By the way, is the fact table a Factless fact?
I could follow up to "Now new record arrives into a_dim:" and then inserting into the dimension table, which looks good per Type 2 SCD. Is there a reason a record was inserted to the Fact table after that?

Edwin
who has yet to implement a Type 3 SCD.

Forgive my ignorance I am new to this. So you saying that when change occurred for record with natural key 100 and new record inserted into dimension table with new surrogate key since it is TYPE2 the fact table only need to be updated with new surrogate key, correct?

Thank you.

Your second fact table insert is a new transaction. It has no relationship to previously inserted fact rows.

bakunian wrote:So you saying that when change occurred for record with natural key 100 and new record inserted into dimension table with new surrogate key since it is TYPE2 the fact table only need to be updated with new surrogate key, correct?

When a change occurs to the dimension table, and a new surrogate key is created (Type 2), future fact inserts will use the new surrogate key.

Edwin
who is curious about the new CDC functionality in SQL Server 2008

Thank you Edwin,

So what happens to the old user_key in the transactional fact table that points to old record in Type 2 user_dim dimension should it be deleted or just leave it alone?

Leave the old fact record as it is. Do not update it. That is the benefit of Slowly Changing Dimension. You can go back in History and view the record as of that time.

That's great, thank you. One final detail below.

Fact:
USER_KEY | SUBSCRIPTION_KEY
---------------------------------------
100 | 123
---------------------------------------
200 | ? <- There is no change in subscription it's still active, so should SUBSCRIPTION_KEY be NULL or 123?
---------------------------------------

User_dim:
USER_KEY | USER_SSN | USER_ADDRESS
-----------------------------------------------
100 | 123-45-6789 | 1st Street
-----------------------------------------------
200 | 123-45-6789 | B Avenue <-- new address
-----------------------------------------------

Assuming a new Fact transaction has come in with User key 200 and Subscription Key 123, that record will be
User Key 200, Subscription Key 123.
Per your question, since subcription 123 is the most active, use that key.

Another best practice...use default value (e.g. -1) if the dimension is not found. Avoid using NULL foreign keys in the Fact table.

Good Luck
Edwin

I understand that a new record is created for the new key.

However, what happens in the loading of the fact table that lets the process know that it has to load a fact again?

Does this happen since the combination of keys has changed and it is recognized as a new row?

Should a fact table load check for existing rows, if so, how?

-- RPC