Hi All,
Learn Referential Integrity by Referring your friend for a Job in your company.
What ever the domain is ? and what ever the data that you gonna load into Warehouse you will have the Reference Tables created and used for loading the data.
Why we need the Reference Tables?
Suppose if your company is conducting Interview for below mentioned skill set.
1) Software Testing Manual
2) Automation – QTP
3) Automation – Selenium
4) Automation – J-Unit
5) Data warehouse Testing
As usual you will have a on line application to enter all the Details related to the Employers. When it comes to the Skills , you will have the drop down list where you can see all the Skill sets in it.
Without using Reference Tables :
I am referring 5 candidates and loaded the data into warehouse for sending the Emails for the right candidate. Very next day company wants few more Skill Set, so they are updating the Front End drop down box to reflect the new skill sets. And peoples started applying for the added skill Set.
With Reference Tables :
Now I am going to create a Reference table which will have all the Skill Set that my company has like (Java, C, C++ and all types of testing). And in the Reference tables I mentioned some codes that related to each Skill Sets. And now I have all the Codes from the source system and will refer the codes to the skill sets in the reference tables and select the corresponding Skill Set.
Ask your self which one is more easier !!!! each time I cant go and modify the Front End application rather that just inserting a new record into the Reference Table and make them available for our data loads.
If the job that is not listed or not matched the we need to send a Default Message – ‘ Better Luck Next Time ‘ is the default value and error table check
Tips and Techniques
Reference tables are created to populate Product Hierarchy, Multiple statuses as per the source data, Multiple departments as per the source data, Interest calculation etc.,
Banking Example :
For example source will not send Product level hierarchy , so our data modellers creates MAP and TYPE tables and stores the Product hierarchy data. Source used to send the Product Number, and our code will search the PRODUCT Codes in the MAP tables and retrieves what all Products level data requires for the target tables. If the PRODUCT NUMBER not in the MAP table then we will write this into ERROR tables and assign Default value.
When verifying the Reference data the testers should concentrate on
a. What is the data type of the source column that is used for look up the MAAP or TYPE tables? If the source column is NULL then design the SQL with TRIM function and without TRIM function, If developer didn’t use the TRIM function when he designs the SQL, there are high possibility of getting space in the source column, then Look up couldn’t be successful.
{Left Join
MAP_TABLE where (date check)
On Trim(Srce_Column) = MAP_Column}
b. If the source column that is used for Look Up is time variant (Delta Column), then check Delta triggered by
looking up the current value from the source.
c. If the Look up is not successful then check the ERROR table for and entry related to the
column.
d. Testers doesn’t have any ownership of MAP or TYPE tables, they should not do any INSERTs or
UPDATES to them.
e. The testers should be very conscious about the data that inserted into MAP or TYPE tables.
Example:
I have an entry 000 in the MAP table and this value is used for look up. So the source can send 000 which
is valid. But Data Modeller designed the S2T; stop the record if the source value is 000. This alerted me
and raised the concern to the designed and changed the S2T.
Please refer the above image for better understanding of Reference Tables
Cheers - Asik
Filed under: Data Warehouse Testing - Learners Guide