Merging fact tables

[Ejaz Khan]

I am creating a Data Mart with two fact tables Loan Applications and Documents.

Loan Applications fact has one to many relationship with Documents fact. Each Table has its own Dimensions like Reasons Dimension for Loan Applications and Time Dimension for Documents.

If I merge the two fact tables the size of tables becomes a big issue and lots of columns will have null values. i.e. each Document is for Loan Application but every Loan Application does not require the documents.

Users using this data mart can query from either fact table: 1) Show all applications for Documents X

2)Which documents are required for Application A.

Question 2 is fine (due to one to many relationship) but for Question 1 there will be lots of rows with null columns and when you drill down from this it can lead to a problem.

Can anybody suggest a better way to design the data model with minimum redundant data with no Null values in the fact table.

[slimdave]

what query tool are you using?

[mike9]

I wouldn't care about the nulls. To reduce the number of redudant data I would create a dimension with the documents. Also create a bitmap index over the document reference.

HTH
Mike

[slimdave]

i think it's very significant that the two facts have different dimensions. i would keep them as seperate fact tables.