Fact Table with huge number of Blank (or Empty) foreign keys

View previous topic View next topic Go down

Fact Table with huge number of Blank (or Empty) foreign keys

Post  mohsinazizz on Wed Feb 22, 2012 10:55 pm

Hi,
I am in a situaion where we have a fact table with about 150 fields. these fields can be divided into three stages.

Lets say first 50 fields relate to stage one, next 70 fields belong to stage and last 30 belong to stage three.

If fact table has one million rows then all one million records enter in stage one but only 10,000 or so would go to stage two and finally only 5,000 would end up in stage three.

this means about 990,000 (out of 1,000,000) records will have blank values in fields related to stage two. and fields related to stage three would even have more blanks.

My question/worry is that aren't we wasting lots of space here? Is there any better way to do this?

Any sort of help guidance will be highly appreciated.

Thanks,
Mohsin

mohsinazizz

Posts : 5
Join date : 2012-02-08

View user profile

Back to top Go down

Re: Fact Table with huge number of Blank (or Empty) foreign keys

Post  ngalemmo on Wed Feb 22, 2012 11:07 pm

Create 3 fact tables. 1 for each stage. Or if this is for medical trials, each stage would have multiple fact tables.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Fact Table with huge number of Blank (or Empty) foreign keys

Post  mohsinazizz on Wed Feb 22, 2012 11:42 pm

Thanks for you quick response.

I actually thought about that approach. Say we have three Fact tables for three stages and these three tables will have one to one relationship between them.

Now possibly users want to see the total number of records in stage 2 and filter them by attributes that relate to stage one. For example if 'Date Of Birth' is recorded in stage1 (Not is Stage 2), users might want to create a report of total number of records in stage 2 having DOB year = 1999

or in other words how we can create relationship between Stage2 fact table and a dimension with stage1 attributes.

Thanks heaps!

Mohsin

mohsinazizz

Posts : 5
Join date : 2012-02-08

View user profile

Back to top Go down

Re: Fact Table with huge number of Blank (or Empty) foreign keys

Post  mohsinazizz on Thu Feb 23, 2012 8:23 pm

ngalemmo wrote:Create 3 fact tables. 1 for each stage. Or if this is for medical trials, each stage would have multiple fact tables.

mohsinazizz

Posts : 5
Join date : 2012-02-08

View user profile

Back to top Go down

Re: Fact Table with huge number of Blank (or Empty) foreign keys

Post  ngalemmo on Thu Feb 23, 2012 11:32 pm

You carry the appropriate dimensions forward into the other fact tables.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Fact Table with huge number of Blank (or Empty) foreign keys

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