Update dim ID in fact rows or create new fact row?

View previous topic View next topic Go down

Update dim ID in fact rows or create new fact row?

Post  Scott on Mon Mar 14, 2016 8:19 am

I am designing a fact table to track Student Applications which has a grain of application and status, with a new row when the status changes. Some applications have a free text notes field. I have put these a separate dimension (DimNotes). When no notes exists the fact record points to a generic 'No Note' row in the notes dimension. If a note then gets added at any point, should I update all fact rows with this ID, or should I create a new fact row that includes the new ID, or only create a new fact row with the new ID when the status of the application changes? Thank you

Scott

Posts : 17
Join date : 2016-03-07

View user profile

Back to top Go down

Re: Update dim ID in fact rows or create new fact row?

Post  ngalemmo on Mon Mar 14, 2016 3:28 pm

Adding a new fact row is a bit overkill, but it really depends on what you are trying to accomplish.

As far as notes go, frankly, a free form text field is the last thing anyone needs on a fact table. What is anyone actually going to do with it?  Enough ranting...

Depending on your dbms you can either just have a field in the fact table (some dbms' will store large text fields (CLOBs) in a separate data structure, so it does not affect query performance against the fact unless you actually use the column. That is not the case with varchar columns.), or store in a dimension table using a surrogate fact key to handle the 1:1 relationship.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Update dim ID in fact rows or create new fact row?

Post  Vishwas on Mon Mar 14, 2016 6:02 pm

Scott wrote:I am designing a fact table to track Student Applications which has a grain of application and status, with a new row when the status changes. Some applications have a free text notes field. I have put these a separate dimension (DimNotes). When no notes exists the fact record points to a generic 'No Note' row in the notes dimension. If a note then gets added at any point, should I update all fact rows with this ID, or should I create a new fact row that includes the new ID, or only create a new fact row with the new ID when the status of the application changes? Thank you

Do you need those old notes or you always want to report on new/latest notes?

Vishwas

Posts : 10
Join date : 2016-03-08

View user profile

Back to top Go down

Re: Update dim ID in fact rows or create new fact row?

Post  Scott on Tue Mar 15, 2016 4:47 am

Vishwas wrote: Do you need those old notes or you always want to report on new/latest notes?

There is only one note per application. This could get updated over time, but I will just overwrite that in the dimension, so will always see the latest note

ngalemmo wrote: Depending on your dbms you can either just have a field in the fact table (some dbms' will store large text fields (CLOBs) in a separate data structure, so it does not affect query performance against the fact unless you actually use the column. That is not the case with varchar columns.), or store in a dimension table using a surrogate fact key to handle the 1:1 relationship.

Dbms is SQL Server, I would still prefer to keep this in a separate dimension as a varchar I think, as generally it won't be queried and not all applications have notes.

Sorry for asking a stupid question but just for clarity in my head, when you say using a surrogate fact key, does that mean in the fact table store the ID of the Note dimension as a foreign key or is this a different concept? My Note dimension just has 3 columns - ID, DurableKey, Note

Scott

Posts : 17
Join date : 2016-03-07

View user profile

Back to top Go down

Re: Update dim ID in fact rows or create new fact row?

Post  ngalemmo on Tue Mar 15, 2016 7:59 am

The 'surrogate fact key' approach is you assign a unique surrogate key to each fact row and use that as the PK of the notes dimension as well. This avoids having to update the fact table.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Update dim ID in fact rows or create new fact row?

Post  Scott on Tue Mar 15, 2016 8:59 am

ngalemmo wrote:The 'surrogate fact key' approach is you assign a unique surrogate key to each fact row and use that as the PK of the notes dimension as well.  This avoids having to update the fact table.

I see, I haven't come across that technique as yet, but that's very useful. I don't think that will quite fit the grain of my fact table, as I create a new fact row each time the application changes status so it's not actually 1:1 relationship.

Scott

Posts : 17
Join date : 2016-03-07

View user profile

Back to top Go down

Re: Update dim ID in fact rows or create new fact row?

Post  Vishwas on Tue Mar 15, 2016 10:55 am

Scott wrote:
Vishwas wrote: Do you need those old notes or you always want to report on new/latest notes?

There is only one note per application. This could get updated over time, but I will just overwrite that in the dimension, so will always see the latest note

ngalemmo wrote: Depending on your dbms you can either just have a field in the fact table (some dbms' will store large text fields (CLOBs) in a separate data structure, so it does not affect query performance against the fact unless you actually use the column. That is not the case with varchar columns.), or store in a dimension table using a surrogate fact key to handle the 1:1 relationship.

Dbms is SQL Server, I would still prefer to keep this in a separate dimension as a varchar I think, as generally it won't be queried and not all applications have notes.

Sorry for asking a stupid question but just for clarity in my head, when you say using a surrogate fact key, does that mean in the fact table store the ID of the Note dimension as a foreign key or is this a different concept? My Note dimension just has 3 columns - ID, DurableKey, Note

Scott,

You make this notes dimension as type 1 dimension where you will overwrite the old notes when new notes come in and then you can use the surrogate key of notes dimension in the fact.
That way old months rows will also point to the latest notes only and that is what you need

Vishwas

Posts : 10
Join date : 2016-03-08

View user profile

Back to top Go down

Re: Update dim ID in fact rows or create new fact row?

Post  Scott on Tue Mar 15, 2016 11:05 am

Vishwas wrote:
Scott,

You make this notes dimension as type 1 dimension where you will overwrite the old notes when new notes come in and then you can use the surrogate key of notes dimension in the fact.
That way old months rows will also point to the latest notes only and that is what you need

My issue is when the application goes from not having any notes in which it points to a generic 'no value' row in the dimension, to then having a note. Based on what you've said, should I create a row in the dimension for every application regardless of whether they have a note or not?

Scott

Posts : 17
Join date : 2016-03-07

View user profile

Back to top Go down

Re: Update dim ID in fact rows or create new fact row?

Post  Vishwas on Tue Mar 15, 2016 12:21 pm

Scott wrote:
Vishwas wrote:
Scott,

You make this notes dimension as type 1 dimension where you will overwrite the old notes when new notes come in and then you can use the surrogate key of notes dimension in the fact.
That way old months rows will also point to the latest notes only and that is what you need

My issue is when the application goes from not having any notes in which it points to a generic 'no value' row in the dimension, to then having a note. Based on what you've said, should I create a row in the dimension for every application regardless of whether they have a note or not?

Yes one row in notes dimension which will say 'no value' if that application is never going to have any notes associated and its surrogate key will be in fact.
But if a application may come to having notes then yes one row for each one would make more sense

Vishwas

Posts : 10
Join date : 2016-03-08

View user profile

Back to top Go down

Re: Update dim ID in fact rows or create new fact row?

Post  ngalemmo on Tue Mar 15, 2016 12:28 pm

It boils down to what you want to update. Do you want to change the FK on the fact or add/update a row in the dimension?

If the former, the natural key for the note dimension should be the note itself. You only store each unique note once. In the latter, you have rows in the dimension when there is a note and the PK for that dimension should be the same PK as the fact. You would not have rows for 'no note'. Use an outer join to access the note dimension and provide a default when null.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Update dim ID in fact rows or create new fact row?

Post  Vishwas on Tue Mar 15, 2016 2:49 pm

ngalemmo wrote:It boils down to what you want to update.  Do you want to change the FK on the fact or add/update a row in the dimension?

If the former, the natural key for the note dimension should be the note itself.  You only store each unique note once.  In the latter, you have rows in the dimension when there is a note and the PK for that dimension should be the same PK as the fact.  You would not have rows for 'no note'.  Use an outer join to access the note dimension and provide a default when null.


ngalemmo if there are no rows for 'no note', this case would make us insert NULL in surrogate key column in fact, right ?

Vishwas

Posts : 10
Join date : 2016-03-08

View user profile

Back to top Go down

Re: Update dim ID in fact rows or create new fact row?

Post  ngalemmo on Tue Mar 15, 2016 3:40 pm

Yes, and do an outer join to the dimension.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Update dim ID in fact rows or create new fact row?

Post  Vishwas on Wed Mar 16, 2016 1:12 pm

ngalemmo wrote:Yes, and do an outer join to the dimension.


ngalemmo, do you think it is right approach to have a surrogate key column in fact table as not null ? We do make combination of surrogate key work as primary key for fact table.

Vishwas

Posts : 10
Join date : 2016-03-08

View user profile

Back to top Go down

Re: Update dim ID in fact rows or create new fact row?

Post  ngalemmo on Wed Mar 16, 2016 3:11 pm

All foreign keys do not define the primary key of a fact table. A note key, particularly if the note could change, does not help identify a row and cannot be part of a primary key, simply because it can change. The true primary key is the minimum number of columns that actually identify a row.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Update dim ID in fact rows or create new fact row?

Post  zoom on Thu Mar 17, 2016 9:35 am

You can also create one dummy row in the Note dim that would represent a "Null" note and use that row ID whenever and every time you have a null note. This way you do not store null key in the fact table and you don not have to do a outer join.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Re: Update dim ID in fact rows or create new fact row?

Post  Vishwas on Thu Mar 17, 2016 11:53 am

ngalemmo wrote:All foreign keys do not define the primary key of a fact table.  A note key, particularly if the note could change, does not help identify a row and cannot be part of a primary key, simply because it can change.  The true primary key is the minimum number of columns that actually identify a row.

Thank You ngalemmo

Vishwas

Posts : 10
Join date : 2016-03-08

View user profile

Back to top Go down

Re: Update dim ID in fact rows or create new fact row?

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