Blank dimension after sync key

[Cassie_L]

Hello, I have 1 fact table and 2 dimension tables, link by below keys. And I am quite sure keys are created by common fields between fact table and dimension table.

But somehow the dimension will have blank value. After confirm certain documents, there should be no missing mapping. Could you please help to check why sync key is invalid at sometime? 

[Company Code]&'|'&Text("Accounting Document Number")&'|'&"Fiscal Year"&'|'&"Posting Date"as zkeyDOCUMENTHEADER,

"Fiscal Year"&'|'&[Company Code]&'|'&Text("Accounting Document Number")&'|'&"Profit Center"&'|'&Text("Functional Area ID")&'|'&Text("Posting Key")&'|'&Text("Line item")&'|'&Text("Account Number")&'|'&Num("Amount in LC") as zkeyDOCUMENTDETAIL

#synckey

[marcus_sommer]

There are a lot of fields included within the keys - so the check if every part of it has the appropriate values within the fact- and the dimension-tables could be quite tedious. Maybe some keys are really missing because there aren't appropriate records and/or it may relate to the data-quality.

At first I suggest to make sure that all fields from the keys are loaded in an identically way within all tables - including all kind of converting/formatting stuff like text() and num() and also some logic to check of any respectively a valid content, for example with if(len(trim(Field)), ... or coalesce() or alt() and similar stuff and replacing NULL with real values respectively any defaults - which in the end also means that this kind of measure isn't needed by creating the keys. The reason for it is that certain information within the fields like leading zeros or spaces and so on may already lost before you create the key.

If this is done and there are further missing keys you may use exists() within these loads and/or in extra loads to check the keys against each other. For this you will need to duplicate the keys with another field-name and/or to use the creation-expression to apply them as the second exists-parameter, for example with a logic like the following (simplified):

concatenate(DimTable)

load Key, 'no values available' as Fieldxyz resident FactTable
where not exists(DimTableDuplicateKey, Key);

which would add keys from facts which are missing in the dimension. Depending on your data you may need to do it for all keys and all directions.