How to Add 'Missing' Records in Script

[benvatvandata]

Hi,

Is there a way to achieve the below through scripting (without doing Inline loads)? I need all Names (in Dim1 table) to have records for all PIs (in Dim2 table) in my Fact table. The extra records I want added (bolded text in screenshot) to the new Fact table can just have null values for Value field.

Please let me know the best method of doing this.

Thanks,

Ben

[chrismarlow]

Hi,

Maybe something like this;

Dim1:
load * inline [
NameFL
abc
def
ghi
];

Dim2:
load * inline [
Pt
1
2
];

Fact:
load * inline [
NameFL, Pt, Value
abc, 1, 5
ghi
];

Join (Dim1)
Load
	*
Resident Dim2;

Outer Join (Dim1)
Load
	*
Resident Fact;

FinalFact:
NoConcatenate
Load * 
Resident Dim1
Where Not IsNull(Pt) and Len(Pt)>0;

DROP TABLES Dim1, Dim2, Fact;

Cheers,

Chris.

[benvatvandata]

Hi Chris,

This does help. Unfortunately, my actual data model is much larger though so it'll be a bit more challenging to implement this. If there's another (or more) Dim tables that need this same process done, can I just replicate this same script with the other Dim tables? For example, let's say Dim3 is a list of TeamNames, and they all need Dim2 values (similar to what we did for Dim1)... exact same process? or will there be complications?

[chrismarlow]

Hi,

Yes, same process should apply you might need to look at the where clause.

This is doing a cartesian join & then your join back will be a on multiple fields, so I can see the data getting quite big quite quickly .. but think that is unavoidable.

Cheers,

Chris.