Catpuring exceptions while loding into fact table

[#1]

Hi,

While loading the data into the fact table if there any dimension keys are missing/'not matching' how to capture those in an exception table at one go ??

Let's say I have 7 dimension tables to be looked at before loading into fact table , during the course of lookup in case of any  non matching keys how do I capture it in an exception table ??  Using NOT EXISTS we can capture but exists() accepts only 2 parameters ??

Thanks

Sathya

[Gysbert_Wassenaar · Accepted solution]

You can specify more than one condition in the where clause:

Load * from somewhere

where not exists(DimA) and not exists(DimB) and not ...etc

[#3]

Thanks Gysbert..

[#4]

I was bit hasty...  Here  is the requirement. From the below, the custid 15 which is in Excep table is not there in Customer table and Productid 16 is there in Excep table which is not there in Products table.

These 2 records to be captured and loaded .  Any suggestions pll.. If I use AND it'/s not w orking

Customer:

Load * Inline [

CustID,Custname,

10,'Chris'

11,'Tom',

];

Products:

Load * Inline [

ProdID,ProductName,

10,'Samsung Galaxy',

11,'Samsung GTS-5253',

15,'Samsung GTS-55S3'

];

NoConcatenate

Excep:

Load * Inline [

InvID,ProductID,CustomerID,

1,10,10,

2,11,10,

3,15,10,

4,16,15,

]

WHERE NOT EXISTS(ProdID,ProductID) AND NOT Exists(CustID,CustomerID);

store Excep into a:\qvd.qvd

[#5]

there is a  typo in the Excep table

Excep:

Load * Inline [

InvID,ProductID,CustomerID,

1,10,10,

2,11,10,

3,16,10,

4,16,15,

]

WHERE NOT EXISTS(ProdID,ProductID) AND NOT Exists(CustID,CustomerID);

store Excep into a:\qvd.qvd

[Gysbert_Wassenaar]

Oh, you want OR instead of AND if any of the conditions is enough to put it in the Excep table.

NoConcatenate

Excep:

Load * Inline [

InvID,ProductID,CustomerID,

1,10,10,

2,11,10,

3,16,10,

4,16,15,

4,15,18,

4,19,10,

]

WHERE NOT EXISTS(ProdID,ProductID) OR NOT Exists(CustID,CustomerID);

See attached example.

[#7]

2 records to be picked up but none of them picked up

Excep:

Load * Inline [

InvID,ProductID,CustomerID

1,10,10

2,11,10

3,16,10  - Product id 16 is not there in the Products table

4,15,12  - Customer id 12  is not there in the customer table

]