Loading Fact table

View previous topic View next topic Go down

Loading Fact table

Post  bakunian on Tue Feb 17, 2009 4:56 pm

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

bakunian

Posts : 4
Join date : 2009-02-11

View user profile

Back to top Go down

Re: Loading Fact table

Post  Edwin Kurian on Tue Feb 17, 2009 5:59 pm

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.

Edwin Kurian

Posts : 13
Join date : 2009-02-03
Location : Milwaukee

View user profile http://www.valordevelopment.com

Back to top Go down

Fact load

Post  bakunian on Tue Feb 17, 2009 6:25 pm

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.

bakunian

Posts : 4
Join date : 2009-02-11

View user profile

Back to top Go down

Re: Loading Fact table

Post  BoxesAndLines on Wed Feb 18, 2009 12:04 am

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Loading Fact table

Post  Edwin Kurian on Wed Feb 18, 2009 11:17 am

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

Edwin Kurian

Posts : 13
Join date : 2009-02-03
Location : Milwaukee

View user profile http://www.valordevelopment.com

Back to top Go down

Fact Load

Post  bakunian on Wed Feb 18, 2009 12:58 pm

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?

bakunian

Posts : 4
Join date : 2009-02-11

View user profile

Back to top Go down

Re: Loading Fact table

Post  Edwin Kurian on Wed Feb 18, 2009 1:39 pm

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.

Edwin Kurian

Posts : 13
Join date : 2009-02-03
Location : Milwaukee

View user profile http://www.valordevelopment.com

Back to top Go down

Fact Load

Post  bakunian on Wed Feb 18, 2009 2:20 pm

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
-----------------------------------------------

bakunian

Posts : 4
Join date : 2009-02-11

View user profile

Back to top Go down

Re: Loading Fact table

Post  Edwin Kurian on Wed Feb 18, 2009 6:10 pm

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

Edwin Kurian

Posts : 13
Join date : 2009-02-03
Location : Milwaukee

View user profile http://www.valordevelopment.com

Back to top Go down

Clarifiction

Post  rpcasey001 on Fri May 29, 2009 4:46 pm

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

rpcasey001

Posts : 7
Join date : 2009-05-29

View user profile http://www.R-P-C-Group.com

Back to top Go down

Re: Loading Fact table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum